CODEWITHSUNDEEP
c#excel
Cocoa Dev
Cocoa Dev

Reputation: 9541

Reading an Excel file with C# causes OleDbException to be thrown

The message says

The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.

The name of the sheet in the Worksheet is "Sheet1"

        string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"";", fileName);
        string query = String.Format("SELECT [columnName1],[columnName2],[columnName3] from [{0}]", "Sheet1$");
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);
        DataTable YourTable = dataSet.Tables[0];
        listBox1.DataSource = YourTable.Columns["ColumnName1"];

Upvotes: 1

Views: 8884

Answers (2)

Ash
Ash

Reputation: 1

Please put square brackets around Sheet1. So your query is: select * from [Sheet1$]

Upvotes: -1

user2140261
user2140261

Reputation: 7993

This works for me: 

string filename = @"C:\Book1.xlsm";

        string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";", filename);
        string query = String.Format("SELECT * from [{0}$]", "Sheet1");
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);
        DataTable YourTable = dataSet.Tables[0];

*NOTE: * If your data does not have headers to make HDR=NO

Also noticed that in your question you used 

[columnName1],[columnName2],[columnName3]

for your columns to select. Please remember these should be the value(s) of the first cell in the column(s) that you would like to grab.

To get column E Use: 

        string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO\";", filename);
        string query = String.Format("SELECT [F5] from [{0}$]", "Sheet1");

Replace 5 With any other column number you need so 

F1 = A
F2 = B
F3 = C

and so on.

The Error you are getting could be because you have the file open and active.

OR you are pointing at the wrong file (Remember you have to include full file path in the filename string. and make sure the sheet is correct. Alos take notice to the fact the i include the $ in my string not in my parameter so rememer to only put just the name of the sheet you are trying to get. If you are still having trouble supply me with the FULL file name for the worksheet you are using i.e. C:\Book1.xlsm and the sheet you are trying to get data from.

Upvotes: 5

Related Questions