Not Reading from Excel with mixed types
Posted on 2010-08-24
I'm t trying to import some data from Excel by an OLEDB command string to a dataTable in c#.
I have one column with mixed types, most of the fields are integers and some are strings.
I set the ImportMixedTypes = Text and TypeGuessRows = 0
If one string data is on the first fields of the column, the type is text and no data is missing.
So, if the string data is at the end of the column, the type is float and all the strings data are missing.
Per example, my column contains about 10,000 integers and 200 strings at the end.
I can't change the format of the Excel sheet.
My connection string is:
string cnx = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\doc.xls;
Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;
My OledbCommand is:
"SELECT [C1] ,[C2] FROM [Sheet1$2:11313] ";
I need to find a way to set the ImportMixedTypes = Text for any Excel sheet.