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
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
Thank you, but this doesn't work either.
Best Regards,
Markus
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
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
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
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
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
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 exFinally 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?
You could control delimiter using schema.ini file. Not sure if Jet supports it as a part of the Extended proprties
Can you be more specific about schema.ini? I didn't understand.
Thank you.
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.
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
Yes, I understand the structure of Schema.ini file, but where this file should be, because your example code never reffer to it. And what is "Run Query"? ODBC use this file by default or what?
If you are bored by my questions, it's no problem, don't answer.
I get the same error populating a dataset from an excel spreadsheet using the
OleDb.OleDbDataAdapter. But only on the web server. On my localhost there is no problem.this works great for me, thanks a lot;
one problem is that if the csv file uses UTF8 encoding and contains asian language chars, those chars are not properly imported;
not sure if the jet driver is capable of handling asian chars, because if i import it using Excel, i got the same scrambled chars.
Control Panel -> Regional and Language Options -> Customize -> List Separator
Probably not set as , but ; is used. That’s also why some have this problem only on there client and not on the web server or the other way around.
You may need to restart your computer..
To be sure that this is the problem use the Separator that is set in your Regional settings as separator in your csv file. Now you have all your columns.
Control Panel -> Regional and Language Options -> Customize -> List Separator
Probably not set as , but ; is used. That’s also why some have this problem only on there client and not on the web server or the other way around.
You may need to restart your computer..
To be sure that this is the problem use the Separator that is set in your Regional settings as separator in your csv file. Now you have all your columns.
