Populate DataSet from CSV

Hi there!

I'm trying to populate a DataSet from a CSV-File. The CSV-File looks like:

"Name";"Given name";"Department";"E-Mail";"Telephone"
"Test";"Test";"Dep 1";"Test@Test.com";"+49 555 555 555"

And my Code looks like this:

string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + System.IO.Path.GetDirectoryName(strFileName) +";" +"Extended Properties=\"Text;HDR=YES;FMT=Delimited\"";

System.Data.OleDb.OleDbConnection conText = new System.Data.OleDb.OleDbConnection(strConnectionString);new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName(strFileName).Replace(".", "#"), conText).Fill(dsImport);

This works fine, so far. But after executing this code the DataTable only contains the first column.

I'm using Visual Studio 2005 Beta 2

What's wrong

Thanks in advance...
Markus

Answer this question

Populate DataSet from CSV

Rahul Patil
You could control delimiter using schema.ini file. Not sure if Jet supports it as a part of the Extended proprties
James Manning
Can you be more specific about schema.ini I didn't understand.

Thank you.
Krystof
Hi David,

if I try this with a comma delimited file I get the following error populating the dataset:

new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName(strFileName).Replace(".","#"), conText).Fill(dsImport);
System.Data.OleDb.OleDbException was unhandled by user code
Message="Unspecified error"
Source="Microsoft JET Database Engine"
ErrorCode=-2147467259
Dmac721
Thank you, but this doesn't work either.

Best Regards,
Markus
AaronW62630
in Second Example Schema.ini file Conatins Details of Columns and Delimiter , it also contains file name from which data should be fetched

in my example see ..first line

[TempFile.csv]

so you need to copy your data first to file name TempFile.csv

which you can done by File.Copy Method and after that if you Run Query TempFile.csv

you will get proper data ...

if your data is containing comma ..then above method will not work correctly...

for soluntion http://spaces.msn.com/mayank/blog/

this will work fine
Eren
Hi!

yes, I've tried this with several files and I'm able to open these files in excel without errors.

The DataTable really contains only the first column. If I try to open a file without seperating each column with " then the first column contains the whole row.

This code has also worked with the Beta 1 of VS.

Best Regards,
Markus
Tom0674
check out http://spaces.msn.com/mayank/
Renzska
I've already check this link, but I don't understand in the second code example when this schema.ini is used. Maybe I'm blind but I really need more details.

Sorry, it's my first time when I try to use import from csv file type using OLEDB or ODBC. I can use, of course, file streaming, but I think it's more elegant to do in first way.
Protium
Hi,

I really can't reproduce your problem... Im also using Beta 2 and it works fine here... Could you post the CSV File (If its not that confidential ) so that I could tinker with it

cheers,

Paul June A. Domag
Strider69
Hi,

Have you tried using other csv's coz the code works fine here....
Or maybe all of your record is being placed on a single row. Try counting how many fields your row contains...

Or try opening your csv in excel. See if there's any error on the csv...

cheers,

Paul June A. Domag
Protrack
I have the same problem. I have a CSV File with this kind of data:

"0213176101","01.03","19:17:00","0727037595","00:01:37","0,2801","Connex"
"0213176101","01.03","20:08:37","0744173844","00:00:19","0,1399","Orange"
"0213176101","01.03","20:25:19","0726220127","00:00:36","0,1399","Connex"
"0213176101","01.03","20:44:05","0720777443","00:00:43","0,1399","Connex"

and using the same code (but in VB) I can import first Column only.

cn = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _

"data source=" & Path.GetDirectoryName(DataFile) & _

";Extended Properties=""text;HDR=NO;FMT=Delimited(;)""")

cmd = New OleDb.OleDbDataAdapter("select * from " + Path.GetFileName(DataFile), cn)

cn.Open()

Try

cmd.Fill(ds)

Catch ex As Exception

Throw ex

Finally

cn.Close()

End Try

Indeed, fields are delimited by "" and also by comma. But I think this is normal because I can have numeric fields like "0,2801", so it must somehow make the difference between comma as a field delimiter and comma as a decimal delimiter.

The question is the same: It's there a solution to use OleDB to import these data into a dataset correctly
Edusie
Hi,

Try this...

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) +"; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName(strFileName), conn);
DataSet ds = gcnew DataSet("Temp");
adapter.Fill(ds);
DataTable tb = ds.Tables[0];

cheers,

Paul June A. Domag
Inamori
Markus,

It looks like this behavior is controlled by the Jet OLE DB provider. I saw the same behavior, but found that all columns were retrieved if the file was delimited with commas (",") rather than semi-colons (";").

Do you have control over the structure of the text files

David Sceppa
Microsoft
Jen Smith
See this Entry

http://spaces.msn.com/members/mayank/ partqs=ayear%3D2006%26amonth%3D1&_c11_blogpart_blogpart=blogview&_c=blogpart
Populate DataSet from CSV

Answer this question