DataType problem whilst importing Excel spreadsheet using OleDb
Posted on 2007-10-17
I know that this is a well-known problem. Unfortunately, I have not found an answer despite several searches.
I have an excel spreadsheet with several columns. Each columns DataType is supposed to be String. Unfortunately, I have a problem when the first entry in a column is a number but the succeeding entries are strings. OleDb then assumes that the column is of type Double and not String. This means that it ingnores everything which is not a number.
I am using this connection string (C#):
connection_string = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1" + (char)34;
If I set the HDR to no I do not have the problem. However, I do not obtain any column names, which are crucial for my validation.
Any pointers to overcome this problem would be very much appreciated. Many thanks in advance.