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

0 answers