Facing error while loading data from Excel Sheet to DataTable

Question

using (OleDbConnection connection = new 
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + ";Extended Properties=\"Text;HDR=" + header + ";IMEX=1;Readonly=1;Extended Properties=Excel 8.0;\""))
{
    using (OleDbCommand command = new OleDbCommand(sql, connection))
    {
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
        {
            dataTable = new DataTable();
            adapter.Fill(dataTable);
        }
    }
}

The above is the code I am using and getting the below error:

Cannot update. Database or object is read-only.

Have someone faced the same issue?

Refer to this link: https://social.msdn.microsoft.com/Forums/en-US/822c9afb-3302-4810-86aa-1eb1ac46d000/odbc-and-excel-database-or-object-is-readonly?forum=adodotnetdataproviders and https://stackoverflow.com/questions/14261655/best-fastest-way-to-read-an-excel-sheet-into-a-datatable — Rahul Sharma, Mar 20 '19 at 08:23
What excel version are you using and which on what bit environment are you running your Visual Studio? Is it 32 or 64 bit? — Rahul Sharma, Mar 20 '19 at 15:15

score 0 · Accepted Answer · answered Mar 20 '19 at 07:46

your connection string contains "Readonly=1;". try changing "Readonly=0;". And try to remove "imex=1". So your code should be someting like that:

    using (OleDbConnection connection = new 
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + ";Extended Properties=\"Text;HDR=" + header + ";Readonly=0;Extended Properties=Excel 8.0;\""))
{
    using (OleDbCommand command = new OleDbCommand(sql, connection))
        {
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                {
                    dataTable = new DataTable();
                    adapter.Fill(dataTable);
                 }
         }
}

Is there any other solution? – Jitesh Dammani Mar 20 '19 at 11:53 — Jitesh Dammani, Mar 20 '19 at 11:53

score 0 · Answer 2 · answered Mar 20 '19 at 18:40

Try using the following syntax (remove Text from Extended properties since it is used to import csv files) :

using (OleDbConnection connection = new 
OleDbConnection(String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"HDR={1};IMEX=1;Readonly=1;Extended Properties=Excel 8.0;\"",pathOnly ,header )))
{
    using (OleDbCommand command = new OleDbCommand(sql, connection))
    {
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
        {
            dataTable = new DataTable();
            adapter.Fill(dataTable);
        }
    }
}

If you have office 2007 or higher provider installed try using Microsoft.ACE.OLEDB.12.0 provider since the it also support reading old excel formats.

If you are trying to import text files (csv) then it is better to use text parsing libraries such as :

Facing error while loading data from Excel Sheet to DataTable

2 Answers2