This is a follow-up on my post on how to read a CSV file using Josh Close’s CsvHelper. CsvHelper is a fast and flexible .NET library for reading and writing CSV files.
Below I will now show how to write CSV files using CsvHelper. The example will actually be reading a CSV file and then writing the contents of that CSV into another but doing it three times. Each time we do this we will use another method (WriteRecords for all records at once, WriteRecord for writing one record, and WriteField for writing one field).
Our sample input CSV file will look like the following.
CommonName,FormalName,TelephoneCode,CountryCode
Argentina,Argentine Republic,54,ARG
Armenia,Republic of Armenia,374,ARM
Australia,Commonwealth of Australia,61,AUS
Austria,Republic of Austria,43,AUT
“Bahamas, The”,Commonwealth of The Bahamas,-241,BHS
Bangladesh,People’s Republic of Bangladesh,880,BGD
Note this CSV file is a simpler file based on the file which is located here.
The first thing to do is to Install CsvHelper. To do this run the following command in the Package Manager Console:
|
1 |
Install-Package CsvHelper |
Now add CsvHelper to the program by adding:
|
1 |
using CsvHelper; |
The next step is to create a class which has properties with the same name of the column headings found in the csv file. Below you will find an example of a class which does this:
|
1 2 3 4 5 6 7 8 9 |
class DataRecord { //Should have properties which correspond to the Column Names in the file //i.e. CommonName,FormalName,TelephoneCode,CountryCode public String CommonName { get; set; } public String FormalName { get; set; } public String TelephoneCode { get; set; } public String CountryCode { get; set; } } |
Finally create an instance of CSVReader and CSVWriter and invoke the GetRecords method using the DataRecord class to read the CSV file.
|
1 2 3 4 |
var reader = new CsvReader(sr); var writer = new CsvWriter(sw); IEnumerable records = reader.GetRecords<DataRecord>().ToList(); |
Once we have the completed we are now ready to write the file out. We first will write the entire file out by using the WriteRecords method. Note this method will also normally write the header out automatically.
|
1 |
writer.WriteRecords(records); |
We then write the records out again but this time we will do so by iterating thru the records collection and writing the entire record using the WriteRecord method and also by writing the fields of the record using the WriteField method.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
foreach (DataRecord record in records) { //Write entire current record writer.WriteRecord(record); //write record field by field writer.WriteField(record.CommonName); writer.WriteField(record.FormalName); writer.WriteField(record.TelephoneCode); writer.WriteField(record.CountryCode); //ensure you write end of record when you are using WriteField method writer.NextRecord(); } |
One point to note that if you were writing a file using the WriteRecord or WriteField methods, you will not get a header record in the file. If you wish to have a header record us the WriteHeader method (example is commented out in final code).
A completed example can be found below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
using System; using System.Collections; using System.Collections.Generic; using System.Diagnostics; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using CsvHelper; namespace CSVHelperWriteSample { class Program { static void Main(string[] args) { using (var sr = new StreamReader(@"countrylist.csv")) { using (var sw = new StreamWriter(@"countrylistoutput.csv")) { var reader = new CsvReader(sr); var writer = new CsvWriter(sw); //CSVReader will now read the whole file into an enumerable IEnumerable records = reader.GetRecords<DataRecord>().ToList(); //Write the entire contents of the CSV file into another writer.WriteRecords(records); //Now we will write the data into the same output file but will do it //Using two methods. The first is writing the entire record. The second //method writes individual fields. Note you must call NextRecord method after //using Writefield to terminate the record. //Note that WriteRecords will write a header record for you automatically. If you //are not using the WriteRecords method and you want to a header, you must call the //Writeheader method like the following: // //writer.WriteHeader<DataRecord>(); // //Do not use WriteHeader as WriteRecords will have done that already. foreach (DataRecord record in records) { //Write entire current record writer.WriteRecord(record); //write record field by field writer.WriteField(record.CommonName); writer.WriteField(record.FormalName); writer.WriteField(record.TelephoneCode); writer.WriteField(record.CountryCode); //ensure you write end of record when you are using WriteField method writer.NextRecord(); } } } } } } |
You can download this sample code with CsvHelper and CSV file here:
About Carlos Ferreira
Can we write the dynamic types into csv format with headers?
Never tried it to be honest. 🙁 I would be an interesting experiment. Will try it out this week.
Carlos,
I hope you can help me. I have a website using CSVHelper. I create a CSV very much like your example. My problem is trying to overwrite an existing CSV. It fails on the creation of the TextWriter because IIS has a lock on the file and I know this because if I simply try to delete the file, I get a message that says I can’t because it’s in use by IISExpress.
Is there another process which is writing to this file or it is your code that is just creating it and writing it over. Make sure you are wrapping the creation of textwriter with a using block to ensure it gets disposed in case of an error. There are a number of sysinternals utils that can help with finding the process that has the file locked (handle, process explorer), etc.
Where should i write my file path?
There’s no specific requirement for path location of the file other than ensuring that the credentials have the appropriate rights to the file location.
It’s nigh on impossible to copy/paste from the code areas on this page.
Your right. I use the Crayon Syntax Highlighter for formatting the code. If you move the mouse to the top of the code area a menu bar will appear. There are two options there that might be of interest – copy which will copy the entire code base for you in the clipboard and ‘Open Code in New Window’ which will open a webpage which make is easy to copy the sections of code that you need. Not sure what this is like on a mobile device.
I used to use SyntaxHighlighter Evolved but found that pages were loading incredibly slow (not sure why) so ended up switching to Crayon Syntax Highligher. If anyone has any suggestions please leave a comment.
Oh one other thing – you can download the sources. I keep on forgetting to put it on Github – will need to do that someday.
this is very very good blog . it is very help full for reading and writing data in csv File . Thank you so much Carlos Ferreira
Great tutorial. I have a question. How to also export lists?
I adjusted your example and added a list of address to your DataRecord class:
public List Adresses { get; set; }
After reading the records from the CSV I loop trough each record and add 1 dummy address:
// Fill list of addresses
foreach (var record in records)
{
record.Adresses = new List {address};
}
The I call writer.WriteRecords(records);
A csv is made but without the addresses.
Please advice.
Thanks,
Paul
Hi Carlos,
Thank you so much for the step by step tutorial. I tried to follow Josh Close and failed miserably (for which I’m duly ashamed). You on the other hand got me going straight away. If it weren’t for you, I’d still be dodging my boss. You are a life saver.
Thanks again!!
Paras.
Hi – thx for the kind words – glad it helped!
Hello Carlos,
This was a huge help for me in building an interface from a Point-of-Sale system into our Financial system!
I have successfully pulled in a CSV and sorted it and now want to subtotal based on the value of a specific field and only write out the total records to a new CSV.
Where I am stuck at the moment is attempting to pull the record count on the CSV file getting processed so that I make sure that I process the last record properly.
I get the error: “The reader has already exhausted all records. If you would like to iterate the records more than once, store the records in memory. i.e. Use CsvReader.GetRecords().ToList() ”
//CSVReader will now read the whole file into an enumerable
IEnumerable records = reader.GetRecords().ToList();
int intLastRow = 0;
Here—> intLastRow = reader.GetRecords().Count();
SOLVED!
I moved my new lines of code up to just after “var reader = new CsvReader(sr);” and now have the count I needed!
Thanks again for your tutorial!!
Thank you Carlos, you’ve a great soul 🙂
Hi Carols,
Great article. Took me longer to find a great source like this than coding it myself…haha I originally was working with StringBuilder, but needed to navigate away from it. Thanks!
Hi Carols
How can I use your approach to add columns. I see writer.NextRecord() writes the record to the next row. I need a way to write to the next column.
Hi just add another writer.WriteField(record.NewFieldName) before the NextRecord(). You will need to modify the DataRecord Class as well to accommodate the additional field. I hope this helps.