Saturday, September 06, 2008

HOW TO: Create a CSV File using System.IO classes

CSV (Comma Separated Values) 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. Each row has data separated by comma.

If its an ASP.NET application, a CSV file can be easily created by setting the
HttpContext.Current.Response.ContentType = "text/csv";

For other Windows applications or a Windows based service, the above method cannot be used. The other possible way is to use the Interop Excel object to create. However, this may require an extra dll to be added to your app. As this is an Interop dll, its not managed and maintaining this dll may become difficult in future.

The following method gives an easier way of handling this - using the classes in System.IO namespace. The below is a code extract that shows how this can be done. We have used a simple example of getting a list of employees

using System.IO;
using System.Text;

private string[] empDetailsHeader = {
"Employee ID",
"Name",
"Salary"
};
private const string separator = ",";

private void CreateCSVFile(string filePath){
try {
StringBuilder csvString = new StringBuilder();

List empList = new List();
//Writing the Header

for (int i = 0; i < empDetailsHeader.Count(); i++){
csvString.Append(empDetailsHeader[i]);
csvString.Append(separator);
}
//Get the list containing the Employee details
empList = GetEmployeeList();

csvString.AppendLine();
foreach (Employee employee in EmpDetails){
csvString.Append(employee.ID.ToString());
csvString.Append(separator);
csvString.Append(employee.Name.ToString());
csvString.Append(separator);
csvString.Append(employee.Salary.ToString());
csvString.Append(separator);
//Next Line
csvString.AppendLine();
}
//save the file
SaveFile(filePath, csvString);
}


private void SaveFile(string filePath, StringBuilder stringBuidler) {
if (File.Exists(filePath)){
File.Delete(filePath);
}else{
StreamWriter streamWriter = new StreamWriter(filePath);
streamWriter.Write(stringBuidler.ToString());
streamWriter.Close();
}
}

Now how do we take care if there is a comma in the data that we need to get into the CSV? Any comma in data is taken as a delimiter for taking the data to the next column. As a work around for this - to still maintain the same data, we can wrap the text using double quotes.

For example, suppose the Salary column in the above data has a value 40,000. Some developers use the method of replacing this comma with space. By doing this we sometimes lose the authenticity of data.
To make sure that we still maintain the correct data, we need to wrap the string with double quotes.

char doubleQuote = '"';
if(employee.Salary.ToString().Contains(",")) {
csvString.Append(doubleQuote);
csvString.Append(employee.Salary.ToString());
csvString.Append(doubleQuote);
}
This will ensure that we have correct data in the CSV File that is created.

1 comment:

Anonymous said...

Hi,

Need to get in touch with the blog author. Any email id where she can be reached?

Regards
Shilpi