Using Oledb to read a .csv with double quotes in it

[EDN Admin]

Hello all!

Im trying to read from a .csv file that has the following content:

<font face="Courier New field 1;field 2;field 3;field 4;field 5
field 1;field "2";field 3;field 4;field 5
field 1;field 2;field 3;field 4;field 5
</font>
Everything goes well except for the second line where the connection is unnable to continue beyond the first double quote, so my table ends up like this:

<font face="Courier New +---------+---------+---------+---------+---------+
| field 1 | field 2 | field 3 | field 4 | field 5 |
+---------+---------+---------+---------+---------+
| field 1 | field   |         |         |         |
+---------+---------+---------+---------+---------+
| field 1 | field 2 | field 3 | field 4 | field 5 |
+---------+---------+---------+---------+---------+</font>

The code is the following:

<font color="#2b91af <font color="#2b91af
<font size=2>OleDbConnection</font></font></font><font size=2><font color="#080808 connection = </font><font color="#0000ff <font color="#0000ff new</font></font> <font color="#2b91af <font color="#2b91af OleDbConnection</font></font><font color="#080808 (</font><font color="#a31515 <font color="#a31515 "Provider=Microsoft.Jet.OleDb.4.0; Data Source = " </font></font><font color="#080808 + folder + </font><font color="#a31515 <font color="#a31515 "; Extended Properties = "Text;HDR=YES;FMT=CSVDelimited""</font></font><font color="#080808 );

connection.Open();
</font></font><font color="#2b91af <font color="#2b91af
<font size=2>OleDbDataAdapter</font></font></font><font size=2><font color="#080808 adapter = </font><font color="#0000ff <font color="#0000ff new</font></font></font><font size=2> <font color="#2b91af <font color="#2b91af OleDbDataAdapter</font></font></font><font size=2><font color="#080808 (</font><font color="#a31515 <font color="#a31515 "SELECT * FROM "</font></font></font><font size=2><font color="#080808 + file, connection);
</font><font color="#2b91af <font color="#2b91af DataSet</font></font></font><font size=2><font color="#080808 data_set = </font><font color="#0000ff <font color="#0000ff new</font></font></font><font size=2> <font color="#2b91af <font color="#2b91af DataSet</font></font><font color="#080808 (</font><font color="#a31515 <font color="#a31515 "Temp"</font></font><font color="#080808 );
adapter.Fill(data_set);
</font><font color="#2b91af <font color="#2b91af DataTable</font></font><font color="#080808 data_table = data_set.Tables[0];


Is there any Extended Property that allows us to tell the connection to ignore the double quotes or to treat them as simple text.

Thank you for your time.

Best regards,

Jorge</font>
</font>

View the full article