asked on

import csv file to a dataTable

What is the easiest way to import a CSV file into a .NET DataTable object?

I am using Visual Studio 2005.

1) Does OLEDB have a driver that could parse a CSV into a datatable?

2) Is some other class inside the System.Data namespace able to parse a CSV file?

3) What about VSTO, does that have a tool that could allow me to tap into the functionality of Excel and read a CSV file? (I'm not very familiar with VSTO)

thanks!

brokeMyLegBiking

ASKER CERTIFIED SOLUTION

iboutchkine

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brokeMyLegBiking

ASKER

ok, that worked great! bloody brilliant.

Do you know how it decides the datatype? Does it ever use a dateTime datatype? (I have a date field, but it cast it as a string in the database)

I see that sometimes it uses Int32 datatype.

I guess I could do a conversion on my dataTable after the data is imported.

thanks again,

iboutchkine

By default CSV is a text file and all the data are strings. You have to do the conversion when you read data from the dataset

eliuha

is there a way to point the sConnectionString to a remote file , instead of a local one ?
i mean , if the file is not on the local machine but somewhere in the internet .

Wardy_01

eliuha :

declare a new System.Web.WebClient object and call download string on the url then dump that to a local location before running this code.

psyche6

Using Jet OLEDB has been a great solution for accessing CSV files in 32 bit applications. However, Microsoft has not and will not release a 64 bit version of Jet OLEDB, so .Net apps running in 64 bit that use this technique will fail. One solution is to compile all .Net applications to force 32 bit, but that's the tail wagging the dog. A better solution that's platform independent is to just read and parse the CSV file and manually populate the DataTable as:

string CSVFilePathName = @"C:\test.cvs";
string[] Lines = File.ReadAllLines(CSVFilePathName);
string[] Fields;
Fields = Lines[0].Split(new char[] { ',' });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
dt.Columns.Add(Fields[i].ToLower(), typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0); i++)
{
Fields = Lines[i].Split(new char[] { ',' });
Row = dt.NewRow();
for (int f = 0; f < Cols; f++)
Row[f] = Fields[f];
dt.Rows.Add(Row);
}

Access rows using DataTable functions as DataTable.Select()