-
Sep 8th, 2021, 03:00 AM
#1
Thread Starter
Banned
External table is not in the expected format error ?
Dear All
I am working on vb.net application and I have a code which does a certain job and it is working but sometimes it gives me this error with some excel files and it is working with other files, and sometimes it works once without error and next time it gives this error
This line of code gives me this error
Code:
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & File.FullName & ";Extended Properties=Excel 12.0;")
it gives me this error
EXTERNAL TABLE IS NOT IN THE EXPECTED FORMAT
I installed Access Database Engine and it did not solve the problem
Thanks, Regards
Moheb Labib
-
Sep 8th, 2021, 03:46 AM
#2
Re: External table is not in the expected format error ?
The exception clearly isn't thrown on that particular line of code so how about you provide the rest of the relevant code, at least. I suspect that it's actually happening when you execute a query, in which case the connection string is irrelevant. In that case, the actual query and the file itself are what matter. The error message is referring to an external table so we need to determine what table that is and your connection string only tells us that you're connecting to an Excel file and nothing more.
-
Sep 8th, 2021, 03:48 AM
#3
Thread Starter
Banned
Re: External table is not in the expected format error ?
The Full Code
Code:
Private Sub GetBBSDataOLEDB()
If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
DataGridView1.Rows.Clear()
Dim Directory = FolderBrowserDialog1.SelectedPath
Dim DtSet As DataSet
Dim firstTable As Boolean = True
Dim Files() As System.IO.FileInfo
Dim DirInfo As New System.IO.DirectoryInfo(Directory)
Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
Dim MyConnection As New System.Data.OleDb.OleDbConnection
Dim MyCommand As New System.Data.OleDb.OleDbDataAdapter
For Each File In Files
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & File.FullName & ";Extended Properties=Excel 12.0;")
MyCommand = New OleDbDataAdapter("select BBSName, BarMark, Diameter, Length, Quantity from [SCHEDULE$]", MyConnection)
MyCommand.TableMappings.Add("Table", "Test")
If firstTable Then
DtSet = New DataSet
MyCommand.Fill(DtSet, "MergedTable")
firstTable = False
Else
MyCommand.Fill(DtSet, "tempTable")
DtSet.Tables("MergedTable").Merge(DtSet.Tables("tempTable"))
DtSet.Tables("tempTable").Clear()
End If
Next
DataGridView1.DataSource = DtSet.Tables("MergedTable")
'DataGridView1.Sort(DataGridView1.Columns(2), ListSortDirection.Ascending)
MyConnection.Close()
End If
End Sub
Thanks
-
Sep 8th, 2021, 03:55 AM
#4
Re: External table is not in the expected format error ?
And what line does the exception get thrown on, pray tell? Please provide ALL the relevant information. Now that you have sown that code, I suspect that the issue is in the merge because the two tables have different schema but I shouldn't have to guess this stuff.
-
Sep 8th, 2021, 03:56 AM
#5
Thread Starter
Banned
Re: External table is not in the expected format error ?
Code:
MyCommand.Fill(DtSet, "tempTable")
-
Sep 8th, 2021, 08:51 AM
#6
Re: External table is not in the expected format error ?
Two things jump out at me... First is that your get file info call.... get EVERY file... you probably want to limit that to just excel files, since that's what you're reading in. I suspect thats where your problem lies too... it's picking up some random file in that folder and trying to read it like an Excel file, and it isn't one.
Second... you're opening connections in the loop, but not closing them in the loop... Look into the "Using" keyword.
It should end up looking something like this (shooting from the hip here)
Code:
'Dim MyConnection As New System.Data.OleDb.OleDbConnection
'Dim MyCommand As New System.Data.OleDb.OleDbDataAdapter
For Each File In Files
Using MyConnection as New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & File.FullName & ";Extended Properties=Excel 12.0;")
Using MyCommand as New OleDbDataAdapter("select BBSName, BarMark, Diameter, Length, Quantity from [SCHEDULE$]", MyConnection)
MyCommand.TableMappings.Add("Table", "Test")
If firstTable Then
DtSet = New DataSet
MyCommand.Fill(DtSet, "MergedTable")
firstTable = False
Else
MyCommand.Fill(DtSet, "tempTable")
DtSet.Tables("MergedTable").Merge(DtSet.Tables("tempTable"))
DtSet.Tables("tempTable").Clear()
End If
End Using
End Using
Next
DataGridView1.DataSource = DtSet.Tables("MergedTable")
'DataGridView1.Sort(DataGridView1.Columns(2), ListSortDirection.Ascending)
'MyConnection.Close()
There's a way to combine the two Using statements into one line, but I'm not sure what the syntax is...
-tg
EDIT - Also ... go ahead and add 'Import System.Data.OleDb' at the top of your file... then you can use OleDbConnection and OleDbAdaptor and the other objects directly w/o having to type the FQN every time.
-
Sep 9th, 2021, 08:49 AM
#7
Thread Starter
Banned
Re: External table is not in the expected format error ?
Thanks your effort, However I found where is the problem, The code should run when the folder contains only excel files without any other files or folders, that's why it gave this error , when it loops inside the folder it finds folders and files not excel, that's why it gave this error, when I removed all other folders and files it ran ok without error
Thanks your time and effort anyway
Moheb Labib
-
Sep 9th, 2021, 01:58 PM
#8
Re: External table is not in the expected format error ?
Originally Posted by meho2020
Thanks your effort, However I found where is the problem, The code should run when the folder contains only excel files without any other files or folders, that's why it gave this error , when it loops inside the folder it finds folders and files not excel, that's why it gave this error, when I removed all other folders and files it ran ok without error
Thanks your time and effort anyway
Moheb Labib
the "GetFiles" method allows you to filter what files it returns. So if there are other file types besides Excel it's not a problem.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|