C # datatable to csv

Question

Can someone please tell me why the following code is not working. Data is saved to a csv file, but the data is not shared. All this exists in the first cell of each row.

StringBuilder sb = new StringBuilder(); foreach (DataColumn col in dt.Columns) { sb.Append(col.ColumnName + ','); } sb.Remove(sb.Length - 1, 1); sb.Append(Environment.NewLine); foreach (DataRow row in dt.Rows) { for (int i = 0; i < dt.Columns.Count; i++) { sb.Append(row[i].ToString() + ","); } sb.Append(Environment.NewLine); } File.WriteAllText("test.csv", sb.ToString()); 

Thank.

Answer 1

The next short version opens perfectly in Excel, maybe your problem was the final comma

.net = 3.5

 StringBuilder sb = new StringBuilder(); string[] columnNames = dt.Columns.Cast<DataColumn>(). Select(column => column.ColumnName). ToArray(); sb.AppendLine(string.Join(",", columnNames)); foreach (DataRow row in dt.Rows) { string[] fields = row.ItemArray.Select(field => field.ToString()). ToArray(); sb.AppendLine(string.Join(",", fields)); } File.WriteAllText("test.csv", sb.ToString()); 

.net> = 4.0

And as Tim remarked, if you are on .net> = 4, you can make it even shorter:

 StringBuilder sb = new StringBuilder(); IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>(). Select(column => column.ColumnName); sb.AppendLine(string.Join(",", columnNames)); foreach (DataRow row in dt.Rows) { IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString()); sb.AppendLine(string.Join(",", fields)); } File.WriteAllText("test.csv", sb.ToString()); 

As suggested by Christian, if you want to handle special characters escaping in fields, replace the loop block with:

 foreach (DataRow row in dt.Rows) { IEnumerable<string> fields = row.ItemArray.Select(field => string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\"")); sb.AppendLine(string.Join(",", fields)); } 

And the last sentence, you can write csv content line by line, and not as a whole document, so as not to have a large document in memory.

Answer 2

I wrapped this in an extension class that allows you to call:

 myDataTable.WriteToCsvFile("C:\\MyDataTable.csv"); 

on any DataTable.

 public static class DataTableExtensions { public static void WriteToCsvFile(this DataTable dataTable, string filePath) { StringBuilder fileContent = new StringBuilder(); foreach (var col in dataTable.Columns) { fileContent.Append(col.ToString() + ","); } fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1); foreach (DataRow dr in dataTable.Rows) { foreach (var column in dr.ItemArray) { fileContent.Append("\"" + column.ToString() + "\","); } fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1); } System.IO.File.WriteAllText(filePath, fileContent.ToString()); } } 

Answer 3

New expansion feature based on Paul Grimshaw's answer. I cleaned it up and added the ability to handle unexpected data. (Blank data, inline quotes and commas in the headers ...)

It also returns a string that is more flexible. It returns Null if the table object does not contain any structure.

  public static string ToCsv(this DataTable dataTable) { StringBuilder sbData = new StringBuilder(); // Only return Null if there is no structure. if (dataTable.Columns.Count == 0) return null; foreach (var col in dataTable.Columns) { if (col == null) sbData.Append(","); else sbData.Append("\"" + col.ToString().Replace("\"", "\"\"") + "\","); } sbData.Replace(",", System.Environment.NewLine, sbData.Length - 1, 1); foreach (DataRow dr in dataTable.Rows) { foreach (var column in dr.ItemArray) { if (column == null) sbData.Append(","); else sbData.Append("\"" + column.ToString().Replace("\"", "\"\"") + "\","); } sbData.Replace(",", System.Environment.NewLine, sbData.Length - 1, 1); } return sbData.ToString(); } 

You call it this way:

 var csvData = dataTableOject.ToCsv(); 

Answer 4

If your calling code refers to the System.Windows.Forms assembly, you might consider a radically different approach. My strategy is to use the functions already provided by the framework to do this in very few lines of code and without having to iterate over columns and rows. What the code below does is programmatically create a DataGridView on the fly and set the DataGridView.DataSource to a DataTable . Then I programmatically select all the cells (including the title) in the DataGridView and call DataGridView.GetClipboardContent() , putting the results into the Windows Clipboard . Then I paste the contents of the clipboard into the File.WriteAllText() call, so I’ll definitely indicate the “paste” formatting as TextDataFormat.CommaSeparatedValue .

Here is the code:

 public static void DataTableToCSV(DataTable Table, string Filename) { using(DataGridView dataGrid = new DataGridView()) { // Save the current state of the clipboard so we can restore it after we are done IDataObject objectSave = Clipboard.GetDataObject(); // Set the DataSource dataGrid.DataSource = Table; // Choose whether to write header. Use EnableWithoutHeaderText instead to omit header. dataGrid.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText; // Select all the cells dataGrid.SelectAll(); // Copy (set clipboard) Clipboard.SetDataObject(dataGrid.GetClipboardContent()); // Paste (get the clipboard and serialize it to a file) File.WriteAllText(Filename,Clipboard.GetText(TextDataFormat.CommaSeparatedValue)); // Restore the current state of the clipboard so the effect is seamless if(objectSave != null) // If we try to set the Clipboard to an object that is null, it will throw... { Clipboard.SetDataObject(objectSave); } } } 

Note that I also need to save the contents of the clipboard until the beginning and restore it as soon as I finish, so that the user does not receive a bunch of unexpected garbage the next time the user tries to paste. The main requirements for this approach: 1) your class must reference System.Windows.Forms , which may be wrong at the level of data abstraction; 2) your assembly should be aimed at the .NET 4.5 platform, since the DataGridView does not exist in 4.0 and 3) The method will fail if the clipboard is used by another process.

In any case, this approach may not be right for your situation, but it is nevertheless interesting and may be another tool in your tool.

Answer 5

Try changing sb.Append(Environment.NewLine); on sb.AppendLine(); .

 StringBuilder sb = new StringBuilder(); foreach (DataColumn col in dt.Columns) { sb.Append(col.ColumnName + ','); } sb.Remove(sb.Length - 1, 1); sb.AppendLine(); foreach (DataRow row in dt.Rows) { for (int i = 0; i < dt.Columns.Count; i++) { sb.Append(row[i].ToString() + ","); } sb.AppendLine(); } File.WriteAllText("test.csv", sb.ToString()); 

Answer 6

I did this recently, but included double quotes around my values.

For example, change these two lines:

 sb.Append("\"" + col.ColumnName + "\","); ... sb.Append("\"" + row[i].ToString() + "\","); 

Answer 7

Try to put ; instead

Hope this helps

Answer 8

Read this and this ?

---

Best implementation would be

 var result = new StringBuilder(); for (int i = 0; i < table.Columns.Count; i++) { result.Append(table.Columns[i].ColumnName); result.Append(i == table.Columns.Count - 1 ? "\n" : ","); } foreach (DataRow row in table.Rows) { for (int i = 0; i < table.Columns.Count; i++) { result.Append(row[i].ToString()); result.Append(i == table.Columns.Count - 1 ? "\n" : ","); } } File.WriteAllText("test.csv", result.ToString()); 

Answer 9

Error - list separator.

Instead of writing sb.Append(something... + ',') , you should put something like sb.Append(something... + System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator);

You must place the list separator character in your operating system (for example, in the example above) or the list separator on the client machine where the file will be viewed. Another option is to configure it in app.config or web.config as a parameter of your application.

Answer 10

Here is an enhancement to vc-74, which treats commas the same way as Excel. Excel puts data in quotation marks if there is a comma in the data, but not in quotation marks if there is no comma in the data.

  public static string ToCsv(this DataTable inDataTable, bool inIncludeHeaders = true) { var builder = new StringBuilder(); var columnNames = inDataTable.Columns.Cast<DataColumn>().Select(column => column.ColumnName); if (inIncludeHeaders) builder.AppendLine(string.Join(",", columnNames)); foreach (DataRow row in inDataTable.Rows) { var fields = row.ItemArray.Select(field => field.ToString().WrapInQuotesIfContains(",")); builder.AppendLine(string.Join(",", fields)); } return builder.ToString(); } public static string WrapInQuotesIfContains(this string inString, string inSearchString) { if (inString.Contains(inSearchString)) return "\"" + inString+ "\""; return inString; } 

Answer 11

4 lines of code:

 public static string ToCSV(DataTable tbl) { StringBuilder strb = new StringBuilder(); //column headers strb.AppendLine(string.Join(",", tbl.Columns.Cast<DataColumn>() .Select(s => "\"" + s.ColumnName + "\""))); //rows tbl.AsEnumerable().Select(s => strb.AppendLine( string.Join(",", s.ItemArray.Select( i => "\"" + i.ToString() + "\"")))).ToList(); return strb.ToString(); } 

Note that the value of ToList() at the end is important; I need something to make the expression appreciate. If I played golf, I could use Min() .

Also note that the result will have a new line at the end due to the last call to AppendLine() . You may not want this. You can simply call TrimEnd() to remove it.

Answer 12

To write a file, I believe the following method is the most efficient and clear: (you can add quotes if you want)

 public static void WriteCsv(DataTable dt, string path) { using (var writer = new StreamWriter(path)) { writer.WriteLine(string.Join(",", dt.Columns.Cast<DataColumn>().Select(dc => dc.ColumnName))); foreach (DataRow row in dt.Rows) { writer.WriteLine(string.Join(",", row.ItemArray)); } } } 

Answer 13

Perhaps the easiest way would be to use:

https://github.com/ukushu/DataExporter

especially in the case of your datatable data type containing the characters /r/n or the delimiter character inside your dataTable cells.

you only need to write the following code:

 Csv csv = new Csv("\t");//Needed delimiter var columnNames = dt.Columns.Cast<DataColumn>(). Select(column => column.ColumnName).ToArray(); csv.AddRow(columnNames); foreach (DataRow row in dt.Rows) { var fields = row.ItemArray.Select(field => field.ToString()).ToArray; csv.AddRow(fields); } csv.Save(); 

Answer 14

 StringBuilder sb = new StringBuilder(); SaveFileDialog fileSave = new SaveFileDialog(); IEnumerable<string> columnNames = tbCifSil.Columns.Cast<DataColumn>(). Select(column => column.ColumnName); sb.AppendLine(string.Join(",", columnNames)); foreach (DataRow row in tbCifSil.Rows) { IEnumerable<string> fields = row.ItemArray.Select(field =>string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\"")); sb.AppendLine(string.Join(",", fields)); } fileSave.ShowDialog(); File.WriteAllText(fileSave.FileName, sb.ToString()); 

Answer 15

 public void ExpoetToCSV(DataTable dtDataTable, string strFilePath) { StreamWriter sw = new StreamWriter(strFilePath, false); //headers for (int i = 0; i < dtDataTable.Columns.Count; i++) { sw.Write(dtDataTable.Columns[i].ToString().Trim()); if (i < dtDataTable.Columns.Count - 1) { sw.Write(","); } } sw.Write(sw.NewLine); foreach (DataRow dr in dtDataTable.Rows) { for (int i = 0; i < dtDataTable.Columns.Count; i++) { if (!Convert.IsDBNull(dr[i])) { string value = dr[i].ToString().Trim(); if (value.Contains(',')) { value = String.Format("\"{0}\"", value); sw.Write(value); } else { sw.Write(dr[i].ToString().Trim()); } } if (i < dtDataTable.Columns.Count - 1) { sw.Write(","); } } sw.Write(sw.NewLine); } sw.Close(); } 

Answer 16

To simulate Excel CSV:

 public static string Convert(DataTable dt) { StringBuilder sb = new StringBuilder(); IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>(). Select(column => column.ColumnName); sb.AppendLine(string.Join(",", columnNames)); foreach (DataRow row in dt.Rows) { IEnumerable<string> fields = row.ItemArray.Select(field => { string s = field.ToString().Replace("\"", "\"\""); if(s.Contains(',')) s = string.Concat("\"", s, "\""); return s; }); sb.AppendLine(string.Join(",", fields)); } return sb.ToString().Trim(); } 

Answer 17

In case someone else came across this, I used File.ReadAllText to get the CSV data, and then I modified it and wrote it using File.WriteAllText . CRLF \ r \ n were good, but the tabs \ t were not taken into account when opening Excel. (All solutions in this thread still use a comma separator, but that doesn't matter.) Notepad showed the same format in the resulting file as in the source. Diff even showed the files as identical. But I realized when I opened the file in Visual Studio using a binary editor. The source file was Unicode, but the target was ASCII . To fix this, I changed both ReadAllText and WriteAllText with the third argument set to System.Text.Encoding.Unicode , and from there Excel was able to open the updated file.

Answer 18

Fyr

 private string ExportDatatableToCSV(DataTable dtTable) { StringBuilder sbldr = new StringBuilder(); if (dtTable.Columns.Count != 0) { foreach (DataColumn col in dtTable.Columns) { sbldr.Append(col.ColumnName + ','); } sbldr.Append("\r\n"); foreach (DataRow row in dtTable.Rows) { foreach (DataColumn column in dtTable.Columns) { sbldr.Append(row[column].ToString() + ','); } sbldr.Append("\r\n"); } } return sbldr.ToString(); } 

Answer 19

Here is my decision based on previous answers by Paul Grimshaw and Anthony V.O. I introduced the code in a C # project on Github .

My main contribution is to eliminate the explicit creation and manipulation of StringBuilder and instead work only with IEnumerable . This avoids allocating a large buffer in memory.

 public static class Util { public static string EscapeQuotes(this string self) { return self?.Replace("\"", "\"\"") ?? ""; } public static string Surround(this string self, string before, string after) { return $"{before}{self}{after}"; } public static string Quoted(this string self, string quotes = "\"") { return self.Surround(quotes, quotes); } public static string QuotedCSVFieldIfNecessary(this string self) { return (self == null) ? "" : self.Contains('"') ? self.Quoted() : self; } public static string ToCsvField(this string self) { return self.EscapeQuotes().QuotedCSVFieldIfNecessary(); } public static string ToCsvRow(this IEnumerable<string> self){ return string.Join(",", self.Select(ToCsvField)); } public static IEnumerable<string> ToCsvRows(this DataTable self) { yield return self.Columns.OfType<object>().Select(c => c.ToString()).ToCsvRow(); foreach (var dr in self.Rows.OfType<DataRow>()) yield return dr.ItemArray.Select(item => item.ToString()).ToCsvRow(); } public static void ToCsvFile(this DataTable self, string path) { File.WriteAllLines(path, self.ToCsvRows()); } 

}

This approach goes well with converting IEnumerable to DataTable, as suggested here .

Answer 20

  DataTable dt = yourData(); StringBuilder csv = new StringBuilder(); int dcCounter = 0; foreach (DataColumn dc in dt.Columns) { csv.Append(dc); if (dcCounter != dt.Columns.Count - 1) { csv.Append(","); } dcCounter++; } csv.AppendLine(); int numOfDc = dt.Columns.Count; foreach (DataRow dr in dt.Rows) { int colIndex = 0; while (colIndex <= numOfDc - 1) { var colVal = dr[colIndex].ToString(); if (colVal != null && colVal != "") { DateTime isDateTime; if (DateTime.TryParse(colVal, out isDateTime)) { csv.Append(Convert.ToDateTime(colVal).ToShortDateString()); } else { csv.Append(dr[colIndex]); } } else { csv.Append("N/A"); } if (colIndex != numOfDc - 1) { csv.Append(","); } colIndex++; } csv.AppendLine(); 

I also needed to do some data overrides, so there are several if-more statements. I needed to make sure that if the field was empty to enter “N / A” instead, or if the “Date” field was formatted as “01/01/1900: 00”, it would be saved as “01/01/1900" instead.

Answer 21

if all the data is still in the first cell, this means that the application in which you opened the file is waiting for another separator. MSExcel can treat the comma as a delimiter, unless you specify otherwise.