Record CSV file in .net

Question

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

I spent some time looking for a set of rules and realized that there are many 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 this is not a simple process of separating lines with commas, I was looking for an existing CSV writer, either third-party, or (hopefully!) Included in the .net structure.

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

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

I was wondering if anyone knows any convenient .Net (2.0 → 3.5 and 4.0) libraries that can be used to create a properly formatted CSV file.

In addition, if there are any rule sets for generating CSV files.

There are many details of reading CSV and parsing CSV files, but not so much about writing (well, I know this is just the opposite: P).

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

Any help would be greatly appreciated :)

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

The optimal third-party library is Linq-to-CSV (rather than a frame library): http://www.codeproject.com/KB/linq/LINQtoCSV.aspx

Thanks for all your help. I decided that the best solution would be to create a simple static class that will perform a special character replacement (as Chris said).

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

Thanks again:)

Answer 1

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

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

And if you need a literal double quote, make two of them, for example:

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

As for dates, stick with the ISO format and you should be fine (e.g. yyyy-mm-dd hh: mm: ss)

Answer 2

CsvHelper (the library I support) is also available through NuGet.

CsvHelper can automatically write your class objects to a file for you.

 var myObj = new MyCustomClass { Prop1 = "one", Prop2 = 2 }; var streamWriter = // Create a writer to somewhere... var csvWriter = new CsvWriter( streamWriter ); // You can write a single record. csvWriter.WriteRecord( myObj ); // You can also write a collection of records. var myRecords = new List<MyCustomClass>{ myObj }; csvWriter.WriteRecords( myRecords ); 

Answer 3

I would just like to add an RFC there, which indicates the CSV format, which I would consider as a canonical source.

Answer 4

I used filehelpers , and this is pretty surprising for creating CSV.

Answer 5

Here is a function that you can use to create a CSV file string from a list of strings (IEnumerable (Of String) or a string array can also be used):

 Function CreateCSVRow(strArray As List(Of String)) As String Dim csvCols As New List(Of String) Dim csvValue As String Dim needQuotes As Boolean For i As Integer = 0 To strArray.Count() - 1 csvValue = strArray(i) needQuotes = (csvValue.IndexOf(",", StringComparison.InvariantCulture) >= 0 _ OrElse csvValue.IndexOf("""", StringComparison.InvariantCulture) >= 0 _ OrElse csvValue.IndexOf(vbCrLf, StringComparison.InvariantCulture) >= 0) csvValue = csvValue.Replace("""", """""") csvCols.Add(If(needQuotes, """" & csvValue & """", csvValue)) Next Return String.Join(",", csvCols.ToArray()) End Function 

As I think, converting from VB.NET to C # is easy

Answer 6

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

Answer 7

I know that you said you found your answer, but I just wanted to give a vote for the LINQtoCSV library that you mentioned. I used it in several projects, and it works great so that your business code is clean and does not concern the details / features of the file format.

It may not be that difficult in your particular case to write an exporter, but the nice thing about this library is that it is bidirectional. If you have to consume CSV along the way, this is not very big code, and / or it gives you a consistent library for use in future projects.

Answer 8

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

Answer 9

Another rule to add to others: use commas as field separators, not field terminators. The reason for this is that the trailing comma at the end of the line can be ambiguous: does it matter or does it mean NULL after it?

Answer 10

I found this important link, which is pretty neat. I have not tried it yet, will tell you how this happens!

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

More carefully, this implementation essentially uses only the basic rules:

special characters = \ n \ "and char delimiter.

if special characters are found, then surround with quotation marks. Replace the quote with a double quote.

Essentially the rules Chris was talking about. I think the easiest way to do this is to create my helper method based on simple rules and revise based on user needs.

Answer 11

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);