5

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.
Fred
  • 3,365
  • 4
  • 36
  • 57
Muflix
  • 6,192
  • 17
  • 77
  • 153
  • 1
    Have your application show the result of `String.Format("{0}-bit", IntPtr.Size * 8)`. What does it say? – Gord Thompson Jun 14 '20 at 22:19
  • it says 32-b, I will try to run program in 64-b mode – Muflix Jun 15 '20 at 08:29
  • I switched to 64-b, but the error message is the same. – Muflix Jun 15 '20 at 08:38
  • Make sure you have the right driver - http://help.loftware.com/pages/viewpage.action?pageId=27099554 – MikeJ Jun 15 '20 at 13:34
  • Try using [AceOdbcCheck](https://github.com/gordthompson/AceOdbcCheck) to check your ODBC setup. – Gord Thompson Jun 15 '20 at 13:52
  • I don't get it, because in `odbc data source administrator window` the driver for `.xlsb` is already listed ? – Muflix Jun 16 '20 at 09:58
  • Have you tried to use ACE.OLEDB driver? See: [Xlsb files using Microsoft.ACE.OLEDB.12.0](https://www.connectionstrings.com/ace-oledb-12-0/xlsb-files/) – Maciej Los Jun 17 '20 at 10:06
  • @Muflix - The driver information may have been written to the Windows registry but the driver itself may be installed in such a way that it is only accessible to Microsoft Office and isn't directly available to third-party applications. That's why I suggested trying [AceOdbcCheck](https://github.com/gordthompson/AceOdbcCheck). – Gord Thompson Jun 21 '20 at 17:17

1 Answers1

0

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.

anakic
  • 2,746
  • 1
  • 30
  • 32