OLEDB read excel cannot process data start with special character apostrophe ( ' )

Question

The following codes read an excel sheet and copy all the data into a C# DataTable

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
conn = new OleDbConnection(strConn);
conn.Open();

string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [" + Sheet1$+ "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
DataTable DT = ds.Tables[0];

When I review the data in DataTable DT, I found that the data in excel which start with apostrophe cannot be read (ex. '0010, '0026, ..., etc), i.e. become empty in DataTable DT.

Any suggested solution to solve it?

Answer 1

This is one of the fun things working with Excel vba.

The only way to see if there is an apostrophe is using this syntax

Dim s As String
Cells(1, 2).Value = "'abc"
s = Cells(1, 2).Formula
s = Cells(1, 2).PrefixCharacter

When you step through this vba code it will only show you the apostrophe if you add the prefixCharacter to your output.

So the only solution I know of is to loop through ALL THE CELLS.... replace the apostrophe with something silly like & #8217; (html apostrophe) and then fix it in your database..

And if you feel like you've been slapped in the face, yes VBA does that to you now and then. This is not OLEDB issue but an excel issue