Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> .NET Development >> .NET Framework Data Access and Storage

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

[1323 byte] By [MarkusFritz] at [2008-2-15]

«« MSBuild equivalent of Nant Solution task ...
»» Jump To Code from a DotNet Add-in

# 1

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

PaulDomag at 2007-9-8 >

top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...

# 2

Thank you, but this doesn't work either.

Best Regards,
Markus

MarkusFritz at 2007-9-8 >

# 3

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

PaulDomag at 2007-9-8 >

# 4

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

MarkusFritz at 2007-9-8 >

# 5

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

PaulDomag at 2007-9-8 >

# 6

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

DavidSceppa at 2007-9-8 >

# 7

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

MarkusFritz at 2007-9-8 >

# 8

See this Entry

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

MVP_mayank at 2007-9-8 >

# 9

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?

Danel at 2007-9-8 >

# 10

You could control delimiter using schema.ini file. Not sure if Jet supports it as a part of the Extended proprties

VMazur at 2007-9-8 >

# 11

Can you be more specific about schema.ini? I didn't understand.

Thank you.

Danel at 2007-9-8 >

# 12

check out http://spaces.msn.com/mayank/

MVP_mayank at 2007-9-8 >

# 13

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.

Danel at 2007-9-8 >

# 14

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

MVP_mayank at 2007-9-8 >