OleDbDataAdapter: How to read a column having data of different data types in a Excel seat


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))
                            dataSet = new 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.

0 answers