I'm almost thinking for Excel to take this in correctly, the column names and the first field should be surrounded by quotation marks, no? Where it looks like this:
I actually was able to work it out on my own. Sharing the code so that it might help someone else.
// take data set returned and drop it into StringBuilder sb
Response.ClearContent();
Response.Clear();
// prepare to output the data to a .csv file
Response.ContentType = "text/plain";
Response.AddHeader("Content-Disposition", "attachment; filename=DownloadedData.csv;");
// Begin formatting the StringBuilder sb
StringBuilder sb = new StringBuilder();
sb.Append("\"");
// Create the column names
string[] columnNames = dt.Columns.Cast<DataColumn>().
Select(column => column.ColumnName).
ToArray();
sb.AppendLine(string.Join("\",\"", columnNames));
// Parse through each row in StringBuilder sb and place in Array
foreach (DataRow row in dt.Rows)
{
string[] fields = row.ItemArray.Select(field => field.ToString()).ToArray();
string separator = @""", """;
sb.AppendLine(string.Join(separator, fields));
}
// send StringBuilder sb to string object "output"
string output = sb.ToString();
// ensure each field has a correct structure - adds extra quotation mark at end
output = output.Replace("\r\n", "\"\r\n\"");
// after outputting strip extra quotation mark at end
output = output.Remove(output.Length - 1, 1);
Response.Write(output.ToString());
Response.Flush();
Response.End();
All-Star
35218 Points
9955 Posts
Moderator
Exporting names to CSV, take two columns instead of one
Dec 18, 2017 04:13 PM|bbcompent1|LINK
How do I get my name column to be one, stead of splitting as two? The output looks like this:
ID,saMAccountName,DisplayName
0,jdoe,Doe,John
I would like to surround the column separators with quotation marks like
"0","jdoe","Doe, John"
Here's the code:
Response.ClearContent(); Response.Clear(); Response.ContentType = "text/plain"; Response.AddHeader("Content-Disposition", "attachment; filename=DownloadedData.csv;"); 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)); } // the most easy way as you have type it Response.Write(sb.ToString()); Response.Flush(); Response.End();All-Star
53121 Points
23672 Posts
Re: Exporting names to CSV, take two columns instead of one
Dec 18, 2017 04:35 PM|mgebhard|LINK
Concatenation should work.
StringBuilder sb = new StringBuilder(); string[] fields = { "Hello, World", "foo", "Bar" }; string separator = @""", """; sb.AppendLine("\"" + string.Join(separator, fields) + "\""); Console.WriteLine(sb.ToString());Results
All-Star
35218 Points
9955 Posts
Moderator
Re: Exporting names to CSV, take two columns instead of one
Dec 18, 2017 05:00 PM|bbcompent1|LINK
The output looks like this now:
I'm almost thinking for Excel to take this in correctly, the column names and the first field should be surrounded by quotation marks, no? Where it looks like this:
All-Star
53121 Points
23672 Posts
Re: Exporting names to CSV, take two columns instead of one
Dec 18, 2017 07:28 PM|mgebhard|LINK
I'm not sure what the problem is the code works fine on my side. Can you post your code so we can see what you have written?
StringBuilder sb = new StringBuilder(); object[] fields = {0, "Hello, World", "foo", "Bar" }; string separator = @""", """; sb.AppendLine("\"" + string.Join(separator, fields) + "\""); Console.WriteLine(sb.ToString());All-Star
35218 Points
9955 Posts
Moderator
Re: Exporting names to CSV, take two columns instead of one
Dec 18, 2017 08:26 PM|bbcompent1|LINK
I actually was able to work it out on my own. Sharing the code so that it might help someone else.
// take data set returned and drop it into StringBuilder sb Response.ClearContent(); Response.Clear(); // prepare to output the data to a .csv file Response.ContentType = "text/plain"; Response.AddHeader("Content-Disposition", "attachment; filename=DownloadedData.csv;"); // Begin formatting the StringBuilder sb StringBuilder sb = new StringBuilder(); sb.Append("\""); // Create the column names string[] columnNames = dt.Columns.Cast<DataColumn>(). Select(column => column.ColumnName). ToArray(); sb.AppendLine(string.Join("\",\"", columnNames)); // Parse through each row in StringBuilder sb and place in Array foreach (DataRow row in dt.Rows) { string[] fields = row.ItemArray.Select(field => field.ToString()).ToArray(); string separator = @""", """; sb.AppendLine(string.Join(separator, fields)); } // send StringBuilder sb to string object "output" string output = sb.ToString(); // ensure each field has a correct structure - adds extra quotation mark at end output = output.Replace("\r\n", "\"\r\n\""); // after outputting strip extra quotation mark at end output = output.Remove(output.Length - 1, 1); Response.Write(output.ToString()); Response.Flush(); Response.End();Participant
1630 Points
931 Posts
Re: Exporting names to CSV, take two columns instead of one
Dec 18, 2017 10:52 PM|PaulTheSmith|LINK
You could use LinqToCsv for a much more robust solution. (What happens to your code if someone's name is
William "Bill" Gates
What happens with dates? Should numbers really be enclosed in quotation marks, would that make them text in Excel instead of numbers)?
The LinqToCsv nuget package does a great job, handles all the corner cases that you might miss and is easy to use.