1

I want to export data from an excel file to mysql database table. I use the following code to get the data from the excel file;

        string path = label4.Text;

        String connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
             "Data Source=" + path + ";" +
             "Extended Properties=Excel 12.0;";
        OleDbConnection xlConn = new OleDbConnection(connectionString);
        xlConn.Open();

        OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", xlConn);
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
        dataAdapter.SelectCommand = selectCommand;
        DataTable dataSet = new DataTable();
        dataAdapter.Fill(dataSet);
        xlConn.Close();

This code works fine when 'Enable Editing' is ON on the excel file. But, it throws the following exception when 'Enable Editing' is OFF:

System.Data.OleDb.OleDbException: 'External table is not in the expected format'

How can I access the excel file even when 'Enable Editing' is OFF?The design of excel file is not on my hands. Thanks.

Jayesh Babu
  • 1,389
  • 2
  • 20
  • 34
  • You might need to switch to using `Interop` to extract the data. Try going through the solutions here: https://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format – Jeremy Thompson Jun 04 '18 at 05:58
  • it is mentioned not to use `Interop` on the link given by you @JeremyThompson – Jayesh Babu Jun 04 '18 at 06:16
  • To clarify, my comment was actually 2 solutions a) Interop b) try solutions in very similar QA. Interop has much richer/full support of Excel using OleDb has its limitations as you're seeing. In these situations where something is unsupported you will need to use a technology that does support it. And given you have no control over the spreadsheet looks like you don't have much choice. Good luck! – Jeremy Thompson Jun 04 '18 at 07:50

0 Answers0