Thursday, June 5, 2008

How to export data to CSV file in ASP.NET

Download the source code

First let's see what the CSV file is:

CSV file is a text based file in which data are separated by comma. It can be opened by excel so you can use excel functionality. Each row of data including the title is in separate line. Meanwhile, each row has data separated by comma.

How to create a CSV file?

We need to write data to Response object. Meanwhile the content type should be text/csv also a header type of attachment should be added to response. Afterwards, column names are written to response then to actual data is written (each row should be just in one line).Finally you need to call Response.End to finalize your work.

Important: if any part of data has comma, unfortunately the CSV file will be screwed up. What I did in the code is that I am replacing comma with space so I am sure that the only comma exists in each row is just for separating data rather than being part of data.

public class CSVExporter

{

public static void WriteToCSV(List<Person> personList)

{

string attachment = "attachment; filename=PerosnList.csv";

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.ClearHeaders();

HttpContext.Current.Response.ClearContent();

HttpContext.Current.Response.AddHeader("content-disposition", attachment);

HttpContext.Current.Response.ContentType = "text/csv";

HttpContext.Current.Response.AddHeader("Pragma", "public");

WriteColumnName();

foreach (Person item in personList)

{

WriteUserInfo(item);

}

HttpContext.Current.Response.End();

}

private static void WriteUserInfo(Person item)

{

StringBuilder strb = new StringBuilder();

AddComma(item.Name, strb);

AddComma(item.Family, strb);

AddComma(item.Age.ToString(), strb);

AddComma(string.Format("{0:C2}", item.Salary), strb);

HttpContext.Current.Response.Write(strb.ToString());

HttpContext.Current.Response.Write(Environment.NewLine);

}

private static void AddComma(string item, StringBuilder strb)

{

strb.Append(item.Replace(',', ' '));

strb.Append(" ,");

}

private static void WriteColumnName()

{

string str = "Name, Family, Age, Salary";

HttpContext.Current.Response.Write(str);

HttpContext.Current.Response.Write(Environment.NewLine);

}

}

Download the source code

11 comments:

Anonymous said...

Hi Emad,

this is great coding, and we've actually done the exactly same thing in vbscript for the company i work for and i was always thinking how it would look like doing it in C#. now i know :).

by far, You are the best persian teacher I have seen to this day.

maybe we bump into eachother at some persian gettogether in sydney. :)

Ehsan

Unknown said...

How would the code look like, if you had to query the data out of a table? It would be nice to have an example using Linq.

Thank you!
E

Unknown said...

How would the code look like if you had to query the data from a database table? How would it look using linq?

Unknown said...

You could have used double quoted comma delimited string format.

There is no need to call AddComma for each method, you could have just used

String.Format("\"{0}\",\"{1}\",\"{2}\"\n", employee.FirstName, employee.LastName, employee.Age)

Emad Yazdan said...

Hi Mojahid,

I appreciate your comment however I strongly believe it is not a good practice. let me give you an example if in a case you need to change the code you have to maintain that code in so many places however now there is a method that you change it. Please search for Coupling and Cohision in programming and you will see what I mean.

Unknown said...

hi this is very nice coding. what i espect from listview to csv file it work perfectly .

Thanks,
by,
SASIDHAR.C

Unknown said...

hi emad,

this is a exellent coding for convert data to csv file.
thanks,
sasidhar.c

Anonymous said...

I would have replaced the AddComma method with an AddQuotes method which would do something like this:

public string AddQuotes(string input)
{
return "\"" + input.Remove("\"") + "\"";
}

This would allow you to have commas in your datafields (although it would remove doublequotes from the data, so I guess it's a trade-off).

Jonathan Wood said...

CSV files allow for values with embedded comments if you wrap them in double quotes instead of stripping out commas as your code does.

I write a couple of simple classes for both reading and writing CSV files at http://www.blackbeltcoder.com/Articles/files/reading-and-writing-csv-files-in-c. Some of your readers may want to see those routines as well.

Andy said...

I don't usually comment on posts because I'm a bit of a lazy bugger. However, after weeks of searching something nice and simple from a list (not a nasty gridview), I found this and it's perfect.

Thank you Emad.

Anonymous said...

Yep, great job. Thank you for this. I am a complete novice with .net, and I was able to perfectly integrate this with my site. Would never have been able to figure it out on my own.