Dumping SQL table to .csv C#

Question

I am trying to implement a script in my application that will dump the entire contents (for now, but I am trying to write the code so that I can easily customize it to only grab certain columns) of a sql db (running ms sql server express 2014) to a .csv file.

Here is the code I have written currently:

        public void doCsvWrite(string timeStamp){       
        try {
            //specify file name of log file (csv).
            string newFileName = "C:/TestDirectory/DataExport-" + timeStamp + ".csv";
            //check to see if file exists, if not create an empty file with the specified file name.
            if (!File.Exists(newFileName)) {
                FileStream fs = new FileStream(newFileName, FileMode.CreateNew);
                fs.Close();
                //define header of new file, and write header to file.
                string csvHeader = "ITEM1,ITEM2,ITEM3,ITEM4,ITEM5";
                using (FileStream fsWHT = new FileStream(newFileName, FileMode.Append, FileAccess.Write))
                using(StreamWriter swT = new StreamWriter(fsWHT))
                {
                    swT.WriteLine(csvHeader.ToString());
                }
            }
            //set up connection to database.
            SqlConnection myDEConnection;   
            String cDEString = "Data Source=localhost\\NAMEDPIPE;Initial Catalog=db;User Id=user;Password=pwd";
            String strDEStatement = "SELECT * FROM table"; 

            try
            {
                myDEConnection = new SqlConnection(cDEString);
            }
            catch (Exception ex)
            {  
                //error handling here.
                return;
            }

            try
            {
                myDEConnection.Open();
            }
            catch (Exception ex)
            {
                //error handling here.
                return;
            }
            SqlDataReader reader = null;
            SqlCommand myDECommand = new SqlCommand(strDEStatement, myDEConnection);
            try
            {
                reader = myDECommand.ExecuteReader();
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        if(reader["Column1"].ToString() == "") {
                            //does nothing if the current line is "bugged" (containing no values at all, typically happens after reboot of 3rd party equipment).
                        }
                        else {
                            //grab relevant tag data and set the csv line for the current row.
                            string csvDetails = reader["Column1"] + "," + reader["Column2"] + "," + String.Format("{0:0.0}", reader["Column3"]) + "," + String.Format("{0:0.000}", reader["Column4"]) + "," + reader["Column5"];

                            using (FileStream fsWDT = new FileStream(newFileName, FileMode.Append, FileAccess.Write))
                            using(StreamWriter swDT = new StreamWriter(fsWDT))
                            {
                                //write csv line to file.
                                swDT.WriteLine(csvDetails.ToString());
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //error handling here.
                myDEConnection.Close();
                return;
            }
            myDEConnection.Close();
        }
        catch (Exception ex)
        {
            //error handling here.
            MessageBox.Show(ex.Message);
        }
    }

Now, this was working fine when I was using it with a 3rd party SQLite-based database, but the output I'm getting after modifing this to my MSSQL db looks something like this (ITEM1 is the primary key, a standard auto-incrementing ID-field):

ITEM1,ITEM2,ITEM3,ITEM4,ITEM5
1,row1_item2,row1_item3,row1_item4,row1_item5
1,row1_item2,row1_item3,row1_item4,row1_item5
1,row1_item2,row1_item3,row1_item4,row1_item5
1,row1_item2,row1_item3,row1_item4,row1_item5
1,row1_item2,row1_item3,row1_item4,row1_item5
1,row1_item2,row1_item3,row1_item4,row1_item5
2,row2_item2,row2_item3,row2_item4,row2_item5
2,row2_item2,row2_item3,row2_item4,row2_item5
2,row2_item2,row2_item3,row2_item4,row2_item5
2,row2_item2,row2_item3,row2_item4,row2_item5
2,row2_item2,row2_item3,row2_item4,row2_item5
3,row3_item2,row3_item3,row3_item4,row3_item5
3,row3_item2,row3_item3,row3_item4,row3_item5
3,row3_item2,row3_item3,row3_item4,row3_item5
3,row3_item2,row3_item3,row3_item4,row3_item5
....

So it seems that it writes several entries of the same row, where I would just like one single line each row. Any suggestions?

Thanks in advance.

edit: Thanks everyone for your answers!

Answer 1

Complete example. Verified working .NET 4.8, May 22. Code simplified for demo.

Why the DataTable ? Under circumstances it is useful. If you converting hundreds of files at once and multi threading - it works as large buffer + you can do pretty complex data mangling at the same time - should you need it.

UNFORTUNATELY - Microsoft trying to detect the column types and if your data not comply with the mechanism it ends with hard to correct errors. In that case use the second solution.

// Get the data from SQLite
SqliteConnection SQLiDataCon = new SqliteConnection(@"Data Source=c:\sqlite.db3");
SQLiDataCon.Open();     
SqliteDataReader SQLiDtaReader = new SqliteCommand(@"SELECT * FROM stats;", SQLiDataCon).ExecuteReader();

// Load data to DataTable
DataTable csvTable = new DataTable();
csvTable.Load(SQLiDtaReader);

// Get "one" string with column names
string csvFields = @"""" + String.Join(@""",""",csvTable.Columns.Cast<DataColumn>().Select(dc => dc.ColumnName).ToArray()) + @"""";

// Prep "in memory the entire content of the CSV"  
StringBuilder csvString = new StringBuilder();

// Write the header in 
csvString.AppendLine(csvFields);

// Write the rows in 
foreach (DataRow dr in csvTable.Rows)
{
    csvString.AppendLine(@"""" + String.Join(@""",""", dr.ItemArray) + @"""");
}

// Save to file 
StreamWriter csvFile = new StreamWriter(@"c:\stats.csv");
csvFile.Write(csvString);

Without DataTable.

// SQLITE
SqliteConnection SQLiDataCon = new SqliteConnection(@"Data Source=c:\sqlite.db3");
SQLiDataCon.Open();   

StringBuilder csvString = new StringBuilder();
StreamWriter csvFile;
Object[] csvRow;
SqliteDataReader  SQLiDtaReader = new SqliteCommand(@"SELECT * FROM sometable;", SQLiDataCon).ExecuteReader();

// CSV HEADER
csvString.AppendLine(@"""" + String.Join(@""",""", SQLiDtaReader.GetSchemaTable().AsEnumerable().Select(dr => dr.Field<string>("ColumnName")).ToArray<string>()) + @"""");

// CSV BODY  
while (SQLiDtaReader.Read())
{
    SQLiDtaReader.GetValues(csvRow = new Object[SQLiDtaReader.FieldCount]);
    csvString.AppendLine(@"""" + String.Join(@""",""",csvRow ) + @"""");
}

// WRITE IT 
csvFile = new StreamWriter(@"C:\somecsvfile.csv");
csvFile.Write(csvString);

Answer 2

Usually, we use specialy designed export/import utilites for dumping data. However, if you have to implement you own routine I suggest decomposing.

private static IEnumerable<IDataRecord> SourceData(String sql) {
  using (SqlConnection con = new SqlConnection(ConnectionStringHere)) {
    con.Open();

    using (SqlCommand q = new SqlCommand(sql, con)) {
      using (var reader = q.ExecuteReader()) {
        while (reader.Read()) {
          //TODO: you may want to add additional conditions here

          yield return reader; 
        }
      }
    }
  }
}

private static IEnumerable<String> ToCsv(IEnumerable<IDataRecord> data) {
  foreach (IDataRecord record in data) {
    StringBuilder sb = new StringBuilder();

    for (int i = 0; i < record .FieldCount; ++i) {
      String chunk = Convert.ToString(record .GetValue(0));

      if (i > 0)
        sb.Append(','); 

      if (chunk.Contains(',') || chunk.Contains(';'))
        chunk = "\"" + chunk.Replace("\"", "\"\"") +  "\"";

      sb.Append(chunk);
    }

    yield return sb.ToString(); 
  } 
}

Having SourceData and ToCsv you can easily implement

private static void WriteMyCsv(String fileName) {
  var source = SourceData("SELECT * FROM table");

  File.WriteAllLines(fileName, ToCsv(source));
}

Answer 3

it happens because output placed inside for-loop

for (int i = 0; i < reader.FieldCount; i++)

and every record repeats FieldCount-times

Answer 4

The for loop isn't needed in the section below. Because it loops from 0 to FieldCount I assume the loop was originally meant to append the text from each column together but inside the loop there's a single line that concatenates the text and assigns it to csvDetails.

        try
        {
            reader = myDECommand.ExecuteReader();
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    if(reader["Column1"].ToString() == "") {
                        //does nothing if the current line is "bugged" (containing no values at all, typically happens after reboot of 3rd party equipment).
                    }
                    else {
                        //grab relevant tag data and set the csv line for the current row.
                        string csvDetails = reader["Column1"] + "," + reader["Column2"] + "," + String.Format("{0:0.0}", reader["Column3"]) + "," + String.Format("{0:0.000}", reader["Column4"]) + "," + reader["Column5"];

                        using (FileStream fsWDT = new FileStream(newFileName, FileMode.Append, FileAccess.Write))
                        using(StreamWriter swDT = new StreamWriter(fsWDT))
                        {
                            //write csv line to file.
                            swDT.WriteLine(csvDetails.ToString());
                        }
                    }
                }
            }
        }

Answer 5

You have a for loop which is looping over the fieldcount.

for (int i = 0; i < reader.FieldCount; i++)

I think it will work if you remove the loop as you don't need to iterate through the columns.