Click here to Skip to main content
13,706,648 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I'm trying to read an Excel (.xlsx) file using the code shown below.Error is.... External table is not in the expected format.

What I have tried:

DataTable dtData = new DataTable();
        OleDbConnection oOleDbConnection;
        OleDbDataAdapter oOleDbDataAdapter;
        try
        {
            oOleDbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0';");
            oOleDbDataAdapter = new OleDbDataAdapter("select * from [sheet1$]", oOleDbConnection);
            oOleDbDataAdapter.Fill(dtData);
            oOleDbConnection.Close();
            oOleDbConnection.Dispose();
            return dtData;
        }
        catch (Exception ex)
        {
            return null;
        }




Can you please tell me where I went wrong..
Posted 13-Jun-18 2:01am
Updated 13-Jun-18 2:30am
v2
Comments
Maciej Los 13-Jun-18 8:15am
   
1. Seems, you forgot to open connection.
2. How do you create and initiate path variable?
anuj___chauhan 13-Jun-18 13:08pm
   
same code working with .xls format but not working with .xlsx
Gerry Schmitz 13-Jun-18 14:37pm
   
Then it's the format or the provider / driver; and not the code.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Please, read my comment first.

1. You forgot to open connection:
oOleDbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0';");
oOleDbConnection.Open(); //you missed that!
oOleDbDataAdapter = new OleDbDataAdapter("select * from [sheet1$]", oOleDbConnection);


2. A path variable should be initiated this way:
string path = @"C:\Folder1\Foder2\FileName.xlsx";
//or - without @
//note: single [\] is an escape character, so you need to pass two [\\] to change its meaning
string path = "C:\\Folder1\\Foder2\\FileName.xlsx";


3. Check if sheet1 exists in a collection of sheets!

4. Use specific connection string for each Excel file (depending on extension):
See: Microsoft ACE OLEDB 12.0 Connection Strings - ConnectionStrings.com[^]
For xlsx files:
string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml';", path);
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180920.1 | Last Updated 13 Jun 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100