You must Sign In to post a response.
  • Category: ASP.NET

    External table is not in expected format in

    Dear Friends,

    In my Application, i have one screen which is contains of Excel Upload(.xls and .xlsx). After Uploading it will convert as Xml format. While that time i got Error as "External Table is not in Expexted Format" but same thing works in another server. what could be the problem. Kindly guide me to do
  • #764434
    I think the error come when you run 32bit code to 64bit operating system.
    change your code as below
    connection string for XLSX file (64 BIT)
    public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

    for XLS (32 BIT)
    Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + path + ";Extended Properties=""Excel 8.0;HDR=YES;"""

    see below link for more help
    hope it helps

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #764436

    Check this [I hope you can convert it to VB]:
    string szExcelFilePath = @"C:\Users\ShashikantG\Downloads\PropertyReportOperatingMonthandStatus02_02_2016.xlsx";
    string szSelectCommand = "SELECT * FROM [Sheet1$]";//... Sheet1 because here I am reading from Sheet1
    string szExcelConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + szExcelFilePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";//... [Works even if excel file is closed]
    string szExcelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + szExcelFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";//... [Works only when excel file is open]

    System.Data.OleDb.OleDbDataAdapter dtAdaptor = new System.Data.OleDb.OleDbDataAdapter(szSelectCommand, szExcelConnection);
    DataTable dtTable = new DataTable();
    Hope it helps.
    Shashikant Gurav

  • #764439
    Hai Karunanidhi,
    The error "External Table is not in Expected Format" could be from various reasons like:
    1. Database is not connecting properly with the Excel file.
    Below link can give you some workaround to fix this issue:
    2. If you are using the same provideer for both type of Excels(xls, xlsx). You need to use different provider name for each.
    For this, you can follow the below link:
    Hope it will be helpful to you.

    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)

  • #764477
    Here is the code snippet to guideline for your problem "External table is not in expected format in", you can edit snippet according to your program

    Dim myDataset As New DataSet()
    Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\ACTIVE - ROSTER.xlsx;" & _"Extended Properties=""Excel 12.0;HDR=YES"""
    Dim myData As New OleDbDataAdapter("SELECT * FROM [ACTIVE$]", strConn)
    myData.Fill(myDataset, "ACTIVE")

    Or To connect
    dim path as string = yourpath
    dim connStr as string = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;"

Sign In to post your comments