Results 1 to 8 of 8

Thread: External table is not in the expected format error ?

  1. #1

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  3. #3

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    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

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  5. #5

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: External table is not in the expected format error ?

    Code:
    MyCommand.Fill(DtSet, "tempTable")

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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.
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    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

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: External table is not in the expected format error ?

    Quote Originally Posted by meho2020 View Post
    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
  •  



Click Here to Expand Forum to Full Width