Problem on reading .csv files using OleDbConnection

[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

 

[Cor Ligthert [MVP]]

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

 

[Guest]

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);

Shouldn't be
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM
["+strFilenameWithoutPath+"]", conn);
?

 

[Paul Clement]

¤ 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)