Problem on reading .csv files using OleDbConnection

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

Hi All

I've tried to read a .csv file and bind the data to a datagrid.
I am doing sth like this:

conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+
"Data Source=" + csvPath + ";" +
"Extended Properties=\"text;HDR=YES;FMT=Delimited\"");
conn.Open();
//Read all data into a data table
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM ["+csvPath+"]", conn);

But I discover that some of data can not be retrieved (while I can retrieve most of them).
Then when I tried to add a link break (simply pressing Enter) @ the line above the line containing unretrieved value in the text editor, I will get things all right.

I guess it is sth to do with the carriage return encoding in the CSV (CSV provided by the others), so that the conn can't recognize the string, how can I fix that?

Thanks

Ray
 
Ray,

Are you sure that the CSV is confirm your local settings of your system.

There is not one standard CSV format on the world.

Cor
 
Ray said:
Hi All

I've tried to read a .csv file and bind the data to a datagrid.
I am doing sth like this:

conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+
"Data Source=" + csvPath + ";" +
"Extended Properties=\"text;HDR=YES;FMT=Delimited\"");
conn.Open();
//Read all data into a data table
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM ["+csvPath+"]", conn);
Click to expand...

Shouldn't be
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM
["+strFilenameWithoutPath+"]", conn);
?
 
¤ Hi All
¤
¤ I've tried to read a .csv file and bind the data to a datagrid.
¤ I am doing sth like this:
¤
¤ conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+
¤ "Data Source=" + csvPath + ";" +
¤ "Extended Properties=\"text;HDR=YES;FMT=Delimited\"");
¤ conn.Open();
¤ //Read all data into a data table
¤ OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM ["+csvPath+"]", conn);
¤
¤ But I discover that some of data can not be retrieved (while I can retrieve most of them).
¤ Then when I tried to add a link break (simply pressing Enter) @ the line above the line containing unretrieved value in the text editor, I will get things all right.
¤
¤ I guess it is sth to do with the carriage return encoding in the CSV (CSV provided by the others), so that the conn can't recognize the string, how can I fix that?

If the file is missing end-of-line characters then I'm afraid the only way to handle this is to fix
the file. I don't know of any way to compensate for this in the connection string or a schema.ini
file.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top