c# exporting sql data to csv

Question

When exporting my data from sql to excel, it starts with the second line and not the first. I think I've narrowed down the problem to the streamwriter section of code but can't seem to work out where it's going wrong! This is the code;

public static void ToCsv3(IDataReader myReader, string fileName, bool includeHeaderAsFirstRow)

    {
        const string Separator = ",";
        Stream s = File.Create(fileName + ".txt");
        StreamWriter streamWriter = new StreamWriter(s, Encoding.Unicode);


        StringBuilder sb = null;

        if (includeHeaderAsFirstRow)
        {
            sb = new StringBuilder();
            for (int index = 0; index < myReader.FieldCount; index++)
            {
                if (myReader.GetName(index) != null)
                    sb.Append(myReader.GetName(index));

                if (index < myReader.FieldCount - 1)
                    sb.Append(Separator);
            }
            streamWriter.WriteLine(sb.ToString());
        }
        int j = 0;
        while (myReader.Read())
        {
            sb = new StringBuilder();


            for (int index = 0; index < myReader.FieldCount - 1; index++)
            {


                if (!myReader.IsDBNull(index))
                {
                    string value = myReader.GetValue(index).ToString();
                    if (myReader.GetFieldType(index) == typeof(String))
                    {
                        if (value.IndexOf("\"") >= 0)
                            value = value.Replace("\"", "\"\"");
                        if (value.IndexOf(Separator) >= 0)
                            value = "\"" + value + "\"";
                    }
                    if (j != 0)
                    {
                        if (index == 0)
                        {
                            sb.Append(Environment.NewLine);
                        }
                    }
                    sb.Append(value);

                    j = j + 1;
                }


                if (index < myReader.FieldCount - 1)
                    sb.Append(Separator);

            }


           if (!myReader.IsDBNull(myReader.FieldCount - 1))
                sb.Append(myReader.GetValue(myReader.FieldCount).ToString().Replace(Separator, " "));


            streamWriter.Write(sb.ToString());
        }



        myReader.Close();
        streamWriter.Close();


    }

Is the issue that the header you are generating is not being written to the file when includeHeaderAsFirstRow is true or is it that data is being written to the file starting at line 2 and not line 1 even if it's the header? — Stephen Ross
– Stephen Ross, Commented Feb 16, 2016 at 15:03
It writes the header, then skips the next line (the first line of data) and writes from the second line of data in the table/database — Rebecca Jones
– Rebecca Jones, Commented Feb 16, 2016 at 15:05
Could it be, that the DataReader moved one row before you pass it in? — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 16, 2016 at 15:12
Is it possible that the call to streamWriter.Write(sb.ToString()) has written an empty string? It certainly looks as though it may be possible without knowing your DB fields and data returned. — Stephen Ross
– Stephen Ross, Commented Feb 16, 2016 at 15:13
@RebeccaJones, Is there no line at all or an empty line, or a line with separators but not data? Can you find out, what data should appear in the first row? — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 16, 2016 at 15:26

Dmitrii Bychenko · Accepted Answer · 2016-02-16 15:43:55Z

2

I'd rather decompose the solution into creating CSV and saving it to the file:

public static IEnumerable<String> ToCsv(IDataReader reader, 
  Boolean includeHeaderAsFirstRow, 
  Char separator = ',',  
  Char quotation = '"') {

  if (null == reader)
    throw new ArgumentNullException("reader");

  String qt = quotation.ToString(); 

  StringBuilder Sb = new StringBuilder();

  if (includeHeaderAsFirstRow) {
    for (int i = 0; i < reader.FieldCount; ++i) {
      if (i > 0)
        Sb.Append(separator);

      String name = reader.GetName(i);

      if (name.Contains(separator) || name.Contains(quotation))
        name = qt + name.Replace(qt, qt + qt) + qt;

      Sb.Append(name);
    }

    yield return Sb.ToString();
  }

  while (reader.Read()) {
    Sb.Clear();

    for (int i = 0; i < reader.FieldCount; ++i) {
      if (i > 0)
        Sb.Append(separator);

      if (!reader.IsDBNull(i)) {
        String item = Convert.ToString(reader[i]);

        if (item.Contains(separator) || item.Contains(quotation))
          item = qt + item.Replace(qt, qt + qt) + qt;

        Sb.Append(item);
      }
    }

    yield return Sb.ToString();
  }
}

public static void CsvToFile(String fileName, 
  IDataReader reader, 
  Char separator = ',',  
  Char quotation = '"') {

  if (String.IsNullOrEmpty(Path.GetExtension(fileName)))
    fileName += ".txt"; // ".csv" looks better here

  File.WriteAllLines(fileName, ToCsv(reader, separator, quotation));
}

edited Feb 16, 2016 at 15:43

answered Feb 16, 2016 at 15:29

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rebecca Jones Over a year ago

Actually this works when changing how I called the includeHeaderAsFirstRow to bool! Still puzzled to why my original isn't working but this does the job (after removing the blank last line created afterwards!). thank you

Collectives™ on Stack Overflow

c# exporting sql data to csv

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related