Creating a DataTable from a CSV File

Question

I am working on a project and I need to read the CSV file and then populate the DataSet with my data. I searched and I found interesting things in OleDB.

I have a CSVReader class:

class CSVReader { public DataTable GetDataTable(string filePath) { OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + Path.GetDirectoryName(filePath) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\""); conn.Open(); string strQuery = "SELECT * FROM [" + Path.GetFileName(filePath) + "]"; OleDbDataAdapter adapter = new OleDbDataAdapter(strQuery, conn); DataSet ds = new System.Data.DataSet("CSV File"); adapter.Fill(ds); return ds.Tables[0]; } } 

And I call it from here:

 CSVReader datareader = new CSVReader(); DataTable dt = datareader.GetDataTable(filepath); 

The problem is that it parses the first row (header row), such as JUST ONE for the column, I mean: This is the header of the CSV file:

 Name, Product Name, Server, Vendor, Start Time, End Time, Host Name, User Name, Project Name, Usage time (hours) 

And after it, all data is separated by commas.

When I read the file, fill in the dataset and type dt.Columns.Count, it shows that it has only 1 column.

Any help?

Thanks in advance.

Answer 1

I always use this CSV library to read CSV files through C #, it always worked well for me.

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

Here is an example of reading a CSF file using a library

 using System.IO; using LumenWorks.Framework.IO.Csv; void ReadCsv() { // open the file "data.csv" which is a CSV file with headers using (CsvReader csv = new CsvReader(new StreamReader("data.csv"), true)) { int fieldCount = csv.FieldCount; string[] headers = csv.GetFieldHeaders(); while (csv.ReadNextRecord()) { for (int i = 0; i < fieldCount; i++) Console.Write(string.Format("{0} = {1};", headers[i], csv[i])); Console.WriteLine(); } } } 

Answer 2

The best option I found, and it solves problems when you may have different versions of Office, as well as 32/64-bit problems, FileHelpers .

It can be added to your projects using NuGet and provides a one-line solution:

 CommonEngine.CsvToDataTable(path, "ImportRecord", ',', true); 

Answer 3

KBCsv has built-in read support in the DataSet :

 using (var reader = new CsvReader(@"C:\data.csv")) { reader.ReadHeaderRecord(); var dataSet = new DataSet(); reader.Fill(dataSet, "csv-data"); } 

Answer 4

if nothing special i use this kind of code

 TextReader tr1 = new StreamReader(@"c:\pathtofile\filename",true); var Data = tr1.ReadToEnd().Split('\n') .Where(l=>l.Length>0) //nonempty strings .Skip(1) // skip header .Select(s=>s.Trim()) // delete whitespace .Select(l=>l.Split(',')) // get arrays of values .Select(l=>new {Field1=l[0],Field2=l[1],Field3=l[2]}); 

Answer 5

Try including IMEX in advanced properties that will tell the driver that you have mixed mode data

 Text;HDR=YES;FMT=Delimited;IMEX=1