Problem on reading .csv files using OleDbConnection

19 Jan 2006 4:18 AM

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

19 Jan 2006 6:02 AM

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

19 Jan 2006 11:10 AM

Milosz Skalecki

"Ray" wrote:

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

--
Milosz Skalecki
MCP, MCAD

19 Jan 2006 3:24 PM

Paul Clement

On Thu, 19 Jan 2006 12:18:37 +0800, "Ray" <r**@weiweiwei.com> wrote:

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