Using Microsoft OLEDB JET driver to read Excel CSV file, how to keep the double quote in input file?

0
votes

I'm using the Microsoft OLEDB JET driver to read Excel CSV files (comma delimited)

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx; Extended Properties='text;HDR=Yes;IMEX=0'"

I use code like below to load the input file into a datatable, this works fine except one problem - if the input field has double quotes around it, the double quotes were gone when I load it into a DataTable after openning it using the oledb driver.

some of the input fileds has double quotes around the field because there are special characters like a comma, I need to output the same file format (basically splitting the same file into multiple files based on value of a specific field) after processing but keep those double quotes. how do I keep the double quotes in the input file??

            OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM {0}", configSection.InputFile), cn);
            OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
            DataTable dt = new DataTable();
            adp.Fill(dt);
1
exceloledbjet
The quotes are not part of the data (only being added during save to meet the requirements of the CSV format), so they will not be read by the OLEDB provider. You can either parse the file "manually" or add the quotes back based on the field contents when you re-save the data. – Tim Williams
Is there any extended properties that I can use to keep the double quotes? Only some of the fields are enclosed in double quotes, but when I read the file into a data table, all the quotes are gone – user2494400
You could try the "schema.ini" approach from here: social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/… – Tim Williams

1 Answers

0
votes

Try putting square brackets around {0} from your select command.

Otherwise, just add the quotes back in when pulling from the datatable.