CODEWITHSUNDEEP
c#.netcsv
Russell
Russell

Reputation: 17739

Writing a CSV file in .net

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

Upvotes: 44

Views: 83855

Answers (11)

Josh Close
Josh Close

Reputation: 23393

CsvHelper (a library I maintain) also available via 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 );

Upvotes: 51

Evgeny Gorb
Evgeny Gorb

Reputation: 1531

Here is the function you can use to generate a row of CSV file from string list (IEnumerable(Of String) or string array can be used as well):

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, it won't be difficult to convert from VB.NET to C#)

Upvotes: 4

dan-gph
dan-gph

Reputation: 16909

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 could be ambiguous: Does it have no significance or does it signify a NULL value following it?

Upvotes: 2

Chris
Chris

Reputation: 40651

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)

Upvotes: 21

user291066
user291066

Reputation: 55

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

Upvotes: 0

Richard Nienaber
Richard Nienaber

Reputation: 10564

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.

Upvotes: 18

super_seabass
super_seabass

Reputation: 1116

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.

Upvotes: 3

TrueWill
TrueWill

Reputation: 25553

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

Upvotes: 3

Russell
Russell

Reputation: 17739

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.

Upvotes: 0

itowlson
itowlson

Reputation: 74832

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.

Upvotes: 2

lomaxx
lomaxx

Reputation: 115843

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

Upvotes: 6

Related Questions