I have the following code below to import from an excel file into a datatable. The problem is one of the columns in my excel file contains mixed content. The first few rows contain numbers and then the majority contain text. When i tried to import in .net it entered all numbers but entered all rows that contained text as null.
I have read about the downfalls of establishing datatypes when importing from excel. I have also seen microsoft recommendations of adding IMEX setting as seen below. Which seemed to work for a time.
However, I then started getting other problems with "Table in unexpected format". This was solved by saving as a CSV file and then saving back to XLS. However, after doing that it seemed to revert to the old problem of entering null for text values in the particular column.
As you can see in my code below I have set the IMEX setting on the connection string. I have been unable to implement the registry changes as the site is hosted on a shared server and the hosts won't make these change. I believe the problem is that only the first 8 rows are being evaulated to establish the datatype to use and in the case of the excel sheet the first 14 row are numbers and the majority of the rest text.
Is there any other way round this? Could I use a loop with a datareader to fill a table or would the same problem occur.?
Any help would be much appreciated.
public DataTable getXlsSheet(string file, string sql)
string cString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;IMEX=1;HDR=No';";
OleDbConnection myConnection = new OleDbConnection(cString);
OleDbCommand myCommand = new OleDbCommand(sql, myConnection);
OleDbDataAdapter myAdapter = new OleDbDataAdapter(myCommand);
DataTable myTable = new DataTable();