Pfthb

My experience is that there are many different csv formats. Specially how they handle escaping of quotes and delimiters within a field.

These are the variants I have ran into:

• quotes are quoted and doubled (excel) i.e. 15" -> field1,"15""",field3


• quotes are not changed unless the field is quoted for some other reason. i.e. 15" -> field1,15",fields3


• quotes are escaped with . i.e. 15" -> field1,"15"",field3


• quotes are not changed at all (this is not always possible to parse correctly)


• delimiter is quoted (excel). i.e. a,b -> field1,"a,b",field3


• delimiter is escaped with . i.e. a,b -> field1,a,b,field3

I have tried many of the existing csv parsers but there is not a single one that can handle the variants I have ran into. It is also difficult to find out from the documentation which escaping variants the parsers support.

In my projects I now use either the VB TextFieldParser or a custom splitter.

I recommend CsvHelper from Nuget.

(Adding a reference to Microsoft.VisualBasic just doesn't feel right, it's not only ugly, it's probably not even cross-platform.)

Sometimes using libraries are cool when you do not want to reinvent the wheel, but in this case one can do the same job with fewer lines of code and easier to read compared to using libraries.
Here is a different approach which I find very easy to use.

1. In this example, I use StreamReader to read the file


2. Regex to detect the delimiter from each line(s).


3. An array to collect the columns from index 0 to n

using (StreamReader reader = new StreamReader(fileName))
 
 string line; 

 while ((line = reader.ReadLine()) != null)
 
 //Define pattern
 Regex CSVParser = new Regex(",(?=(?:[^"]*"[^"]*")*(?![^"]*"))");

 //Separating columns to array
 string X = CSVParser.Split(line);

 /* Do something with X */
 
 

CSV can get complicated real fast.


Use something robust and well-tested:

FileHelpers:
www.filehelpers.net

The FileHelpers are a free and easy to use .NET library to import/export data from fixed length or delimited records in files, strings or streams.

I use this here:

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

Last time I was looking for something like this I found it as an answer to this question.

Another one to this list, Cinchoo ETL - an open source library to read and write CSV files

For a sample CSV file below

Id, Name
1, Tom
2, Mark

Quickly you can load them using library as below

using (var reader = new ChoCSVReader("test.csv").WithFirstLineHeader())

 foreach (dynamic item in reader)
 
 Console.WriteLine(item.Id);
 Console.WriteLine(item.Name);
 

If you have POCO class matching the CSV file

public class Employee

 public int Id get; set; 
 public string Name get; set; 

You can use it to load the CSV file as below

using (var reader = new ChoCSVReader<Employee>("test.csv").WithFirstLineHeader())

 foreach (var item in reader)
 
 Console.WriteLine(item.Id);
 Console.WriteLine(item.Name);
 

Please check out articles at CodeProject on how to use it.

Disclaimer: I'm the author of this library

private static DataTable ConvertCSVtoDataTable(string strFilePath)
 
 DataTable dt = new DataTable();
 using (StreamReader sr = new StreamReader(strFilePath))
 
 string headers = sr.ReadLine().Split(',');
 foreach (string header in headers)
 
 dt.Columns.Add(header);
 
 while (!sr.EndOfStream)
 
 string rows = sr.ReadLine().Split(',');
 DataRow dr = dt.NewRow();
 for (int i = 0; i < headers.Length; i++)
 
 dr[i] = rows[i];
 
 dt.Rows.Add(dr);
 

 

 return dt;
 

 private static void WriteToDb(DataTable dt)
 
 string connectionString =
 "Data Source=localhost;" +
 "Initial Catalog=Northwind;" +
 "Integrated Security=SSPI;";

 using (SqlConnection con = new SqlConnection(connectionString))
 
 using (SqlCommand cmd = new SqlCommand("spInsertTest", con))
 
 cmd.CommandType = CommandType.StoredProcedure;

 cmd.Parameters.Add("@policyID", SqlDbType.Int).Value = 12;
 cmd.Parameters.Add("@statecode", SqlDbType.VarChar).Value = "blagh2";
 cmd.Parameters.Add("@county", SqlDbType.VarChar).Value = "blagh3";

 con.Open();
 cmd.ExecuteNonQuery();
 
 

 

First of all need to understand what is CSV and how to write it.

1. Every next string ( /r/n ) is next "table" row.


2. "Table" cells is separated by some delimiter symbol. Most often used symbols is t or ,
   


3. Every cell possibly can contain this delimiter symbol (cell must to start with quotes symbol and ends with this symbol in this case)


4. Every cell possibly can contains /r/n sybols (cell must to start with quotes symbol and ends with this symbol in this case)

The easiest way for C#/Visual Basic to work with CSV files is to use standard Microsoft.VisualBasic library. You just need to add needed reference, and the following string to your class:

using Microsoft.VisualBasic.FileIO;

Yes, you can use it in C#, don't worry. This library can read relatively big files and supports all of needed rules, so you will be able to work with all of CSV files.

Some time ago I had wrote simple class for CSV read/write based on this library. Using this simple class you will be able to work with CSV like with 2 dimensions array.
You can find my class by the following link:
https://github.com/ukushu/DataExporter

Simple example of using:

Csv csv = new Csv("t");//delimiter symbol

csv.FileOpen("c:\file1.csv");

var row1Cell6Value = csv.Rows[0][5];

csv.AddRow("asdf","asdffffff","5")

csv.FileSave("c:\file2.csv");

To complete the previous answers, one may need a collection of objects from his CSV File, either parsed by the TextFieldParser or the string.Split method, and then each line converted to an object via Reflection. You obviously first need to define a class that matches the lines of the CSV file.

I used the simple CSV Serializer from Michael Kropat found here: Generic class to CSV (all properties)
and reused his methods to get the fields and properties of the wished class.

I deserialize my CSV file with the following method:

public static IEnumerable<T> ReadCsvFileTextFieldParser<T>(string fileFullPath, string delimiter = ";") where T : new()

 if (!File.Exists(fileFullPath))
 
 return null;
 

 var list = new List<T>();
 var csvFields = GetAllFieldOfClass<T>();
 var fieldDict = new Dictionary<int, MemberInfo>();

 using (TextFieldParser parser = new TextFieldParser(fileFullPath))
 
 parser.SetDelimiters(delimiter);

 bool headerParsed = false;

 while (!parser.EndOfData)
 
 //Processing row
 string rowFields = parser.ReadFields();
 if (!headerParsed)
 
 for (int i = 0; i < rowFields.Length; i++)
 
 // First row shall be the header!
 var csvField = csvFields.Where(f => f.Name == rowFields[i]).FirstOrDefault();
 if (csvField != null)
 
 fieldDict.Add(i, csvField);
 
 
 headerParsed = true;
 
 else
 
 T newObj = new T();
 for (int i = 0; i < rowFields.Length; i++)
 
 var csvFied = fieldDict[i];
 var record = rowFields[i];

 if (csvFied is FieldInfo)
 
 ((FieldInfo)csvFied).SetValue(newObj, record);
 
 else if (csvFied is PropertyInfo)
 
 var pi = (PropertyInfo)csvFied;
 pi.SetValue(newObj, Convert.ChangeType(record, pi.PropertyType), null);
 
 else
 
 throw new Exception("Unhandled case.");
 
 
 if (newObj != null)
 
 list.Add(newObj);
 
 
 
 
 return list;


public static IEnumerable<MemberInfo> GetAllFieldOfClass<T>()

 return
 from mi in typeof(T).GetMembers(BindingFlags.Public