External table is not in the expected format.

Question

1.44/5 (2 votes)

See more:

I am having the code as follows in vb.net

VB

Dim connectionString As String =[String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", filepath)
            Using oleDbConn As New OleDbConnection(connectionString)
                oleDbConn.Open()

when i try to open *.xls file it works fine but if I try to open *.xlsx page than it throws me an error External table is not in the expected format. I searched in net but could not sort out with it. Please someone help me to sort it out its really urgent

Posted 30-Jan-13 22:31pm

sagarjainhr

Updated 29-Jun-20 6:49am

Patrice T

v2

Add a Solution

7 solutions

Solution 10

You may have more than one excel/database sheet open in the background.... so make sure you have the right sheet open/closed. I closed all sheet the reconnected to database.

Posted 29-Jun-20 6:49am

Member 14876352

Comments

Dave Kreskowiak 29-Jun-20 13:13pm

Asked and answered seven years ago. Next time, read all of the answers before answering yourself.

Solution 8

I tried all suggested here but none worked..and finally i found out that a particular cell was having more than 12k characters. so look out for length as well.

Posted 2-Oct-19 22:30pm

Yusuf.br

Updated 2-Oct-19 22:40pm

Solution 9

i was also facing an same issue in which while reading data from an excel i was getting external table error.

basically the excel file i was trying to read was code generated. code generated excel files do not have a [Program Name] property under Details tab. Due to which the driver mis behaves.

if u open and save as the file the issue will get resolved or else you can save the file before reading using the below code will solve ur issue

// Initialize Excel Application
Excel.Application excelApp = new Excel.Application();
// Open Existing Excel
Excel.Workbook excelWorkBook = excelApp.Workbooks.Open("/Ur path..");

// Save Excel
excelWorkBook.Save();
// Close Excel
excelWorkBook.Close();
// Close Excel Application
excelApp.Quit();

Posted 14-Oct-19 0:31am

Member 14622077

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mantu Singh · Accepted Answer · 2013-01-30T22:39:00

Solution 2

Use the following code as the Excel 2007 vcersion files are Excel 12.0

Dim connectionString As String =[String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""", filepath)

Best of luck!!

Posted 30-Jan-13 22:39pm

Mantu Singh

Comments

sagarjainhr 31-Jan-13 4:56am

It didnt work

bbirajdar · Accepted Answer · 2013-01-30T22:39:00

Solution 3

You need OLEDB 12 drivers installed on your machine.
Install from this link

http://www.microsoft.com/en-us/download/details.aspx?id=13255[^]
And the code will be like this .

C#

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFileName + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
            // if you don't want to show the header row (first row) use 'HDR=NO' in the string
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();
            // Get the data table containg the schema guid.
            DataTable dtWorksheetTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dtWorksheetTables == null || dtWorksheetTables.Rows.Count == 0) return null;
            string worksheetName = GetWorksheetName(dtWorksheetTables);
            string strExcelSQL = "SELECT * FROM [" + worksheetName + "]";
            OleDbCommand oleDbCommand = new OleDbCommand(strExcelSQL, excelConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(oleDbCommand);
            DataTable excelDataTable = new DataTable();
            dataAdapter.Fill(excelDataTable);
            //Dispose 
            dataAdapter.Dispose();
            oleDbCommand.Dispose();
            excelConnection.Close();
            excelConnection.Dispose();
            GC.Collect();

Posted 30-Jan-13 22:39pm

bbirajdar

Updated 30-Jan-13 23:36pm

v3

Comments

sagarjainhr 31-Jan-13 4:56am

I have installed access database 10 installed in my machine still its throwing an error

bbirajdar 31-Jan-13 5:03am

Change the conenction string to

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFileName + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";

sagarjainhr 31-Jan-13 5:06am

I am using the vb.net platform and not C# so cant use "\" slashes as its throws an error.
If I convert it to vb.net and use also its throwing the same error

bbirajdar 31-Jan-13 5:11am

Dont look at the \ . Look at the contents of the connection string.... You are using Excel 8.0.. Instead use 12.0.. Did you get my point or should I explain it in more detail?

sagarjainhr 31-Jan-13 5:19am

I tried using Excel 12.0 still its giving the same error. I am struck up over here and I am really not getting what to do I tried with 8 & 12 both but still not able I am getting the same error

bbirajdar 31-Jan-13 5:20am

Post your complete code.. Your reply is not enough to guess what is wrong...

sagarjainhr 31-Jan-13 5:24am

Private Sub importFromExcel(ByVal filepath As String)
Try
Dim tablesNames As DataTable
Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties=""Excel 12.0;HDR=YES;""")
Using oleDbConn As New OleDbConnection(connectionString)
oleDbConn.Open()
tablesNames = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If tablesNames Is Nothing Then
cmbNames.Items.Add("-Select-")
Else

cmbNames.Items.Clear()
cmbNames.Items.Add("-Select-")
For i As Integer = 0 To tablesNames.Rows.Count - 1 Step +2
Dim tableName As String = tablesNames.Rows(i)("TABLE_NAME").ToString().Replace("$", "")
tableName = tableName.Replace("'", "")
If tableName.StartsWith("_") Then
Else
cmbNames.Items.Add(tableName.Trim())
End If
Next
End If

End Using
Catch generatedExceptionName As Exception
MessageBox.Show(generatedExceptionName.ToString)

Finally
OleDbConnection.ReleaseObjectPool()

End Try
End Sub

sagarjainhr 31-Jan-13 5:26am

this is my code I want to fetch the *.xlsx file and access worksheet of it but i am not able to access *.xlsx file but I can access *.xls file. As all the datas are there in *.xlsx Its very much necessary to access *.xlsx file than *.xls file
When oleDbConn.Open() tries to execute it throws an error External table is not in the expected format.

bbirajdar 31-Jan-13 5:30am

The code seems to be correct.. Can you tell me the line number where this error is thrown ?

sagarjainhr 31-Jan-13 5:33am

it says line 59 the same line where oleDbConn.Open() code try to executes

sagarjainhr 31-Jan-13 5:34am

Private Sub btn_FileBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_FileBrowse.Click

Using ofd As New OpenFileDialog()
cmbNames.Items.Clear()
chklboxName.Items.Clear()
ofd.Title = "Select file"
'If tbBrowse.Text = "" Then
ofd.InitialDirectory = "My Documents:\"
'Else
' ofd.InitialDirectory = tbBrowse.Text
'End If
ofd.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
If ofd.ShowDialog() = DialogResult.OK Then
filename = ofd.FileName
tbBrowse.Text = filename
Try
System.IO.File.Open(filename, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read)
Catch
Dim Response As MsgBoxResult = MsgBox("Please ensure that the file is closed before proceeding", MsgBoxStyle.Information, "Alert")
tbBrowse.Text = ""
End Try
Else
tbBrowse.Text = ""
Dim Response As MsgBoxResult = MsgBox("No file selected, Please select a file to proceed", MsgBoxStyle.Information, "Alert")
MessageBox.Show("No file selected, Please select a file and proceed")
End If
Call importFromExcel(filename)
End Using
End Sub

Private Sub importFromExcel(ByVal filepath As String)
Try
Dim tablesNames As DataTable
' Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""", filepath)
'Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;""", filepath)
' Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""", filepath)
Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties=""Excel 12.0;HDR=YES;""")
Using oleDbConn As New OleDbConnection(connectionString)
oleDbConn.Open()
tablesNames = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If tablesNames Is Nothing Then
cmbNames.Items.Add("-Select-")
Else

cmbNames.Items.Clear()
cmbNames.Items.Add("-Select-")
For i As Integer = 0 To tablesNames.Rows.Count - 1 Step +2
Dim tableName As String = tablesNames.Rows(i)("TABLE_NAME").ToString().Replace("$", "")
tableName = tableName.Replace("'", "")
If tableName.StartsWith("_") Then
Else
cmbNames.Items.Add(tableName.Trim())
End If
Next
End If

End Using
Catch generatedExceptionName As Exception
MessageBox.Show(generatedExceptionName.ToString)

Finally
OleDbConnection.ReleaseObjectPool()

End Try
End Sub

bbirajdar 31-Jan-13 5:36am

Okay.. Install these OLEDB drivers http://www.microsoft.com/en-us/download/details.aspx?id=13255

sagarjainhr 31-Jan-13 5:36am

Where btn_FileBrowse_Click() fnc browse the file and calls the importFromExcel() function
and this is how the function work

bbirajdar 31-Jan-13 5:37am

This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.

sagarjainhr 31-Jan-13 5:42am

Its also not working....

sagarjainhr 1-Feb-13 0:22am

Thanks for your help
Its working fine now. thr was no problem with connection string it was problem with browsing the file function I just commented 1 block its working fine

Member 10684591 26-Mar-14 10:12am

i am also facing same problem can you just share the connection string

Member 12887310 12-Dec-16 2:25am

hi could youp please tellme how did you solve this issue

sagarjainhr · Accepted Answer · 2013-01-31T18:26:00

Solution 4

I solved it by my self there was no problem with the connection string and everything was fine over there. The problem was with I was checking whether the file is open or not and I have not closed that file after that process. so it was used by other process and connection string was failing to access the excel sheet and it was throwing an error.

Please ensure that the file is not open or used by any other process while trying to read with *.xlsx file, If file is open also it works fine with *.xls and not in *.xlsx file keep it in mind while accessing *.xlsx file

Posted 31-Jan-13 18:26pm

sagarjainhr

Updated 3-Feb-13 23:24pm

v2

Comments

vikramaditya9 26-Nov-13 3:20am

I have also facing same problem "External table is not in the expected format".I am using Connectionstring like > ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelPath + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
Please suggest anyone any problem with connectionstring?

Anshuwa 23-Dec-14 6:14am

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Files\file.xlsx; Extended Properties='Excel 8.0;HDR=Yes

WEI LI JASMINE ZEE 30-May-18 23:40pm

YES. The selected excel file must be opened and it will have no problem with the connection string.
If the selected excel file is closed, I will get an error of 'External table is not in the expected format'.
Is there any way to solve it? TQ.

Raimis9 · Accepted Answer · 2013-01-30T22:38:00

Solution 1

Change connection string:
http://www.connectionstrings.com/excel-2007[^]

Posted 30-Jan-13 22:38pm

Raimis9

Comments

sagarjainhr 31-Jan-13 4:57am

I have tried with this link still not able to sort it out