Writing a CSV file in .net

Question

I have a requirement to export a dataset as a CSV file.

I have spent a while searching for a set of rules to go by and realised there are quite a few rules and exceptions when writing a CSV file.

http://knab.ws/blog/index.php?/archives/3-CSV-file-parser-and-writer-in-C-Part-1.html http://bytes.com/topic/c-sharp/answers/236875-problems-streamwriter-output-csv http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/0073fcbb-adab-40f0-b768-4bba803d3ccd

So now it is not a simple process of separating strings with commas, I have searched for an existing CSV writer either 3rd party or (hopefully!) included in the .net framework.

Edit: New link: http://www.thinqlinq.com/Post.aspx/Title/LINQ-to-CSV-using-DynamicObject-and-TextFieldParser

The TextFieldParser is a VB object (can be referenced from C#) that will automatically parse CSV files. :)

I was wondering if anybody knows any handy .Net (2.0 -> 3.5 and 4.0) libraries that can be used to generate a correctly formatted CSV file.

Also, if there are any sets of rules for generating CSV files.

There are a lot of details of CSV readers and parsing CSV files, however not as many about writing (ok, I know it is just the opposite :P ).

http://www.codeproject.com/KB/database/CsvReader.aspx

Any help would be much appreciated :)

I found another article with some more detailed CSV rules: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

A neat 3rd party library is Linq-to-CSV (not framework library): http://www.codeproject.com/KB/linq/LINQtoCSV.aspx

Thanks for your help everyone. I have decided that the best solution will be to create a simple static class that will do the special character replacement (that Chris mentioned).

If I had a need for Linq querying my CSV files, I would look at the CodeProjects implementation of Linq-to-CSV.

Thanks again :)

Answer 1

I've used filehelpers extensively and it's pretty awesome for generating CSVs.

Answer 2

If there are any commas in your cell, surround the entire cell with double quotes, eg:

cell 1,cell 2,"This is one cell, even with a comma",cell4,etc

And if you want a literal double quote, do two of them, eg:

cell 1,cell 2,"This is my cell and it has ""quotes"" in it",cell 4,etc

As for dates, stick to ISO format, and you should be fine (eg yyyy-mm-dd hh:mm:ss)

Answer 3

You can use ODBC to read and write CSV files (via OdbcConnection and a suitable connection string). This should be reasonably good for generating CSV files, and will handle things like quoting for you; however I have run into some issues when using it to read CSV files generated by other programs.

Answer 4

I found this important link which is quite neat. Haven't tried it yet, will let you know how it goes!

http://www.codeproject.com/KB/linq/LINQtoCSV.aspx

Looking more closely, this implementation essentially only uses basic rules too:

special chars = \n \" and the separator char.

if found special characters, then surround with quotes. Replace quote with double quote.

Essentially the rules Chris mentioned. I think the easiest way to do this is to create my helper method based on the simple rules and revise on a user-needs basis.

Answer 5

Another rule to add to the others: Use the commas as field separators rather than as field terminators. The reason for this is that a trailing comma at the end of a line is ambiguous: Does it have no significance or does it signify a NULL value following it?

Answer 6

For the specifications, see http://en.wikipedia.org/wiki/Comma-separated_values

Answer 7

I know you said you found your answer, but I just wanted to give a vote for the LINQtoCSV library you mentioned. I've used it in a couple projects and it works really well for keeping your business code clean and not concerned with details / peculiarities of the file format.

Maybe in your specific case it is not too difficult to write the exporter, but the nice thing about this library is that it is bidirectional. If you find yourself having to consume the CSV down the road it's not much extra code, and/or it gives you a consistent library to use on future projects.

Answer 8

I would just like to add there's an RFC that specifies the CSV format which is what I would regard as the canonical source.

Answer 9

Can you use a string array and then concatenate using:

string out = "";
string[] elements = { "1", "2" };
foreach(string s in elements) { out += s + "," };
out = out.substring(0, out.Length-1);