OleDbDataAdapter: How to read a column having data of different data types in a Excel seat
Hi,
I am using OleDbDataAdapter to read data from a excel sheet, which have a column having data of 'INT' type in staring some rows after that 'VARCHAR' type.
When i am opening it, and trying read it, it reads all numeric value correctly but returns DBNull Value for 'VARCHAR' type data. My code snippet is:
static DataTable ReadExcel(string workSheetName, string filePath) { DataSet dataSet = null; if (File.Exists(filePath)) { string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\""; connectionString = String.Format(connectionString, filePath); using (OleDbConnection oledbConnection = new OleDbConnection(connectionString)) { using (OleDbDataAdapter dataAdapterCommand = new OleDbDataAdapter("select * from [" + workSheetName + "$]", oledbConnection)) { oledbConnection.Open(); dataSet = new DataSet(); dataAdapterCommand.Fill(dataSet); } } } if (dataSet != null && dataSet.Tables.Count > 0) return dataSet.Tables[0]; return null; } is there any way to fix the same.
Thanks in advance.
Nirbhay