Search code examples
c#odbcxlsb

How to read XLSB file with ODBC

I have this code, which I would like to convert .xlsb file to DataTable

public static class Utils
    {
        public static DataTable ImportExceltoDatatable(string filepath)
        {

            string connectionString = "Driver ={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ = " + filepath;

            string query = "Select * From [SheetName$]";

            using (var connection = new OdbcConnection(connectionString))
            using (var adapter = new OdbcDataAdapter(query, connection))
            {
                DataSet dataset = new DataSet();
                adapter.Fill(dataset); // <---------------- exception thrown here

                DataTable datatable = dataset.Tables[0];
                return datatable;
            };
        }
    }

adapter.Fill(datasaet) throws the following exception

System.Data.Odbc.OdbcException: 'ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'

In computer ODBC settings, Excel driver seems to be installed enter image description here

what is the correct way of how to use this driver, do I have a mistake in the connection string?

Unfortunately, I found no other way than to use ODBC.

  • NPOI, EPPLUS does not read XLSB.
  • LinqToExcel needs Microsoft.ACE.OLEDB.12.0 registered on the machine.
  • Microsoft.Office.Interop.Excel needs excel to be installed on the machine.
Solution

  • Had the same problem just now. In your pic you can see the "Platform" column says the driver is 64-bit. Switching the project to x64 (instead of Any CPU) fixes the problem.