At first glance, I have the same problem as many people, and I found many questions and answers about my problem, but none of them helped me.
I am importing from an MS excel file (.xls file) to NET using ADO NET. The file contains mixed types in one column: numbers and text, as well as a known problem - the text format is not recognized and data is lost.
I use the following connection string with recommended parameters:
string strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\";", pathname);
This is my code:
OleDbConnection sqlConn = new OleDbConnection(this.strConnectionString);
sqlConn.Open();
OleDbDataAdapter sqlAdapter = new OleDbDataAdapter();
string sql = "SELECT * FROM [" + sheetName + "]";
OleDbCommand selectCMD = new OleDbCommand(sql, sqlConn);
sqlAdapter.SelectCommand = selectCMD;
DataTable dt = new DataTable(sheetName);
sqlAdapter.Fill(dt);
I tested it under NET 3.5 (x86) and NET 4.0 (x86) (also tested as windows exe and asp net versions), and the problem remains.
I do not know that I am doing something wrong, but I spent many hours and the problem still remains.