Share via

External table is not in the expected format.

Question

Thursday, May 25, 2006 4:28 AM

Hi all!

The following code snippet work fine under VS 2005, but it throws an exception: System.Data.OleDb.OleDbException: External table is not in the expected format.
under VS 2003, ASP 1.1 when trying to open the connection. This occurs only for specific excel files. Does anybody know what's wrong? And where should I look for answers? Am I doing something wrong from ASP, or my excel file is the one to blame? Help me out, pls.

System.Data.OleDb.OleDbConnection cn;
System.Data.OleDb.OleDbDataAdapter cmd;
System.Data.DataSet ds = new DataSet();
cn = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "data source=E:\a.xls;Extended Properties=Excel 8.0;");
            
cn.Open();

Thanks!!

Eva.

All replies (36)

Monday, September 11, 2006 7:58 PM

Hi Eva,

 I am having the same problem.

Have you found a solution yet?

Help very much appreciated.

regards

Mark

Monday, September 11, 2006 8:13 PM

Excel is not a database it is a flat file so tell me what you are doing and I may find some code from Microsoft for you.  Hope this helps.

Monday, November 6, 2006 7:10 AM

donot you know that you can query the Excel sheet and use it like a DB then..

 

sorry

 

http://www.Net4developers.com

 

Monday, November 6, 2006 9:11 AM

Yep I know even when it was implemented, Excel XP and above, and Access is a database but not RDBMS.

Tuesday, December 19, 2006 11:18 PM

this error generally occurs if the excel u r trying to connect is not in a valid format...for ex..the excel might be in HTML table format or SYLK file format...U can happily connect excel excel read it and fill it into a datatable..fast and smooth too...here is sample vb code..below IMEX property is used to avoid datatypes of cells..

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                            "Data Source=E:\new1.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
        oledbConn.ConnectionString = strConn
        ' Open oledb connection
        If oledbConn.State = Data.ConnectionState.Closed Then
            oledbConn.Open()
        End If
        Dim oledbdatatbl As New System.Data.DataTable
        ' Select excel sheet.
        oledbCmd.CommandText = "SELECT * FROM [sheet1$]"
        oledbCmd.CommandType = Data.CommandType.Text
        oledbCmd.Connection = oledbConn
        oledbadap.SelectCommand = oledbCmd
        oledbadap.Fill(oledbdatatbl)
        GridView1.DataSource = oledbdatatbl.DefaultView
        GridView1.DataBind()
        If oledbConn.State = Data.ConnectionState.Open Then
            oledbConn.Close()
        End If

Wednesday, September 12, 2007 3:41 AM

The File that you are accessing using OLEDB is not in correct format i-e information missing.

i was also getting the same error. i was using my application to extract contact information from yellowpages and then save that information to a excel file generated using code. now when i wanted to open that file and insert / read data using oledb it gave me the error "External table is not in the expected format."

 

Resolution

I created an excel file using MS Excel and then i defined my columns over there and when accessed using OLEDB there was no error and data insertion was successful using code. Now this doesnot seems to be a good solution but it worked for me in that typical scenario

 

The excel file that i was creating from code and the file created by MS Excel Application had different file sizes so this means that some of the information in file created by me using code was missing.

 

Wednesday, September 19, 2007 9:40 AM

Hi i am new in this group but i always use to take help from this forum. So thanks for all that.

Now i am also facing the same problem. I tried to export excel in dataset by using below code but getting error "External table is not in the expected format.<!-- / icon and title --><!-- message -->" 

SO can you please help me on this.

string conSalesRpt = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Scheduler_Automation_Extraction\Scheduler_db_SalesReport\SalesReport_Cap.xls;Extended Properties=""Excel 8.0;""";

OleDbDataAdapter MyOwner=new OleDbDataAdapter("select distinct [Opportunity Owner] from [SalesReport_Cap$]",conSalesRpt);

DataSet downer=new DataSet();

 

Thanks,

MyOwner.Fill(downer,"SalesReport");

Thursday, September 20, 2007 11:28 PM

 

Hi i am new in this group but i always use to take help from this forum. So thanks for all that.

Now i am also facing the same problem. I tried to export excel in dataset by using below code but getting error "External table is not in the expected format." 

SO can you please help me on this.

string conSalesRpt = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Scheduler_Automation_Extraction\Scheduler_db_SalesReport\SalesReport_Cap.xls;Extended Properties=""Excel 8.0;""";

OleDbDataAdapter MyOwner=new OleDbDataAdapter("select distinct [Opportunity Owner] from [SalesReport_Cap$]",conSalesRpt);

DataSet downer=new DataSet();

 

Thanks,

MyOwner.Fill(downer,"SalesReport")

Even now i am also facing when i delete old excel file and copy same file again and than try to excess it gives me same error.

  1. When you download file from ABC site to let say c drive and run the scheduler, Scheduler doesn’t read that file.
  2. When you open that excel, Copy the excel data, create new excel by and past all data to new excel file. Scheduler reads that new file easily and process.  

Its really important and urgent for me and i am also struggling on this from last 2 days. so please help me out.  lots of thanks...

Tuesday, October 16, 2007 1:55 PM

Hi!

I can solve this problem. It happens to me because the xls file I have was a exported file from my system in HTML format (my system creates a HTML file named with XLS extension). So I could not use the connection string with the Extended Property = Excel 8.0, but with "HTML Import".

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=arquivo.xls;Extended Properties=HTML Import;

I hope to help someone. bye

 // 

 Olá!

Consegui solucionar este problema. Isto aconteceu comigo porque o arquivo xls que eu tenho era um arquivo que foi exportado do meu sistema no formato HTML (meu sistema 
cria um arquivo HTML nomeado com a extensão XLS). Assim eu não pude usar a connection string com a Extended Property = Excel 8.0, mas com "HTML Import"

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=arquivo.xls;Extended Properties=HTML Import;

Espero ter ajudado alguem.

Wednesday, October 24, 2007 11:41 AM

I had the same problem and i found out that i was reading the wrong excel file.. and if the columns in the excel file dont match the column in the sql server u get that error..

 

Regards

Karen

Tuesday, November 13, 2007 6:35 AM

Hi,
 I have the same problem.The solution you have given is working to open the excel file in html format.but i did not query to access data.
 
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=arquivo.xls;Extended Properties=HTML Import;

Monday, November 26, 2007 9:48 PM

 Nice thread ... was having issues with the XL auto formating of the columns - found the IMEX=1 property

 Works just great - thanks

 I had a situation where the xl file I was getting from the vendor had columns in random order

 and can have more columns than needed and it varied from file to file

I use the below query to overcome the problem of non-standard format xl file:

        SELECT
            convert (varchar,
            GetDate(), 112)                 BatchNumber,
            convert(varchar,
            cast([Transaction_Date]
            as smalldatetime), 101) WTranDate,
            Left([Shed], 50)             WShedName,
            left([Grower_Code], 15)  WTrace,
            left([Grower], 50)             WGrowerName,
            left([Tag_Number], 50)     WTagNumber,
            left([Item_No], 15)         WItemCode,
            left([Item], 50)                 WItemDescripton,
            left([Case_Count], 10)     WCaseCount,
            [MU Shipped]                         WShippedQty
        FROM
        (
                SELECT
                    *       
                FROM OpenDataSource
                (
                    'Microsoft.Jet.OLEDB.4.0',
                    'Data Source="\domainc\STARPROJECT\LoadArea\WEYCODATA.xls";
                    User ID=Admin;Password=;
                    Extended properties="Excel 8.0;HDR=YES;IMEX=1;"'
                )...sheet1$ c
        ) DATA

 Now I dont care what order the columns are and they can give any number of columns

 I pick what I need - luckily column names were fix :-)   some relief!

 

Thanks 

 

 

Thursday, April 3, 2008 10:26 AM

 I have faced similar OledbException. Even my file is not exported from any other systems rater my excel file is created and saved from Excel itself, I still get this error.

 I've even have IMEX=1 property set.
 

any solution?
 

Thursday, April 3, 2008 10:39 AM

Are the columns in ur excel file and the database are the same??

Friday, April 18, 2008 3:48 PM

instead of

MyOwner.Fill(downer,"SalesReport");

use

MyOwner.Fill(downer); or

MyOwner.Fill(downer,"ExcelInfo");

Friday, June 27, 2008 11:34 PM

I was getting the error "External table is not in the expected format." when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0,

Instead try using a connection string like: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(fileNamePath);Extended Properties=Excel 12.0;";

I believe this new connection string should work for both .xlsx and .xls Excel files.

Excel 2007 files are typically saved with the extension .xlsx and a Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Excel 2003 or older files use extension .xls and a Type: application/vnd.ms-excel

//string fileNamePath = "~/admin/article/Excel/ExcelImport.xls";

string fileNamePath = "~/ExcelImport.xls";/*

// Connect to the Excel Spreadsheet old Excel 2003 binary format

string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(fileNamePath);Extended Properties=Excel 8.0;";

*/

// Connect to the Excel Spreadsheet 2007 // SEE: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=852945&SiteID=1

string xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(fileNamePath);Extended Properties=Excel 12.0;";

 

Monday, July 28, 2008 6:05 AM

Great post developerJamiro.

Many thanks

Friday, September 5, 2008 10:13 AM

 Thanks for that developerJamiro, that solved my problem too.

Wednesday, September 24, 2008 8:24 AM

Hi, please make sure that the above file "a.xls" was really in the same format. To check the file open it and do save as the file as a1.xls. This may solve your problem.

else please follow the following code. 

If Not Me.FileUpload1.PostedFile Is Nothing And FileUpload1.PostedFile.ContentLength > 0 Then

            Dim fn As String = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim SaveLocation As String = Server.MapPath("..\finance\" & fn) 'folder path

            Try

                FileUpload1.PostedFile.SaveAs(SaveLocation)

                Dim excelConnectionString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("..\finance\" & fn) & ";" & "Extended Properties=Excel 8.0;")
                Using connection As OleDbConnection = New OleDbConnection(excelConnectionString)

                    Dim command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection) 'This will query on excel Sheet1 change name if name is different if name mismact it will not work
                    Try

                        connection.Open()

                    Catch ex As Exception
                        Response.Write("Error: " & ex.Message)

                    End Try

                    ' Create DbDataReader to Data Worksheet

                    Using dr As System.Data.OleDb.OleDbDataReader = Command.ExecuteReader()
                        ' SQL Server Connection String

                        Dim sqlConnectionString As String = ConfigurationManager.AppSettings("WBConnectionString")

                        ' Bulk Copy to SQL Server

                        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)
                            bulkCopy.DestinationTableName = "database table name where you want to copy"

                            bulkCopy.WriteToServer(dr)

                            connection.Close()

                            File.Delete(SaveLocation)


                        End Using

                    End Using

                End Using

            Catch Exc As Exception
                Response.Write("Error: " & Exc.Message)
            Finally
                File.Delete(SaveLocation)
            End Try

        Else

            Response.Write("Please select a file to upload.")
        End If

 

Wednesday, September 24, 2008 8:26 AM

Hi, please make sure that the above file "a.xls" was really in the same format. To check the file open it and do save as the file as a1.xls. This may solve your problem.

else please follow the following code. 

If Not Me.FileUpload1.PostedFile Is Nothing And FileUpload1.PostedFile.ContentLength > 0 Then

            Dim fn As String = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim SaveLocation As String = Server.MapPath("..\finance\" & fn) 'folder path

            Try

                FileUpload1.PostedFile.SaveAs(SaveLocation)

                Dim excelConnectionString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("..\finance\" & fn) & ";" & "Extended Properties=Excel 8.0;")
                Using connection As OleDbConnection = New OleDbConnection(excelConnectionString)

                    Dim command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection) 'This will query on excel Sheet1 change name if name is different if name mismact it will not work
                    Try

                        connection.Open()

                    Catch ex As Exception
                        Response.Write("Error: " & ex.Message)

                    End Try

                    ' Create DbDataReader to Data Worksheet

                    Using dr As System.Data.OleDb.OleDbDataReader = Command.ExecuteReader()
                        ' SQL Server Connection String

                        Dim sqlConnectionString As String = ConfigurationManager.AppSettings("WBConnectionString")

                        ' Bulk Copy to SQL Server

                        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)
                            bulkCopy.DestinationTableName = "database table name where you want to copy"

                            bulkCopy.WriteToServer(dr)

                            connection.Close()

                            File.Delete(SaveLocation)


                        End Using

                    End Using

                End Using

            Catch Exc As Exception
                Response.Write("Error: " & Exc.Message)
            Finally
                File.Delete(SaveLocation)
            End Try

        Else

            Response.Write("Please select a file to upload.")
        End If

 

Happy programming [:)]

Amit Kumar Mishra

appshark Software Pvt Ltd

 

Monday, October 27, 2008 6:41 PM

Instead try using a connection string like: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(fileNamePath);Extended Properties=Excel 12.0;";

 

 

"I love you dude" (in a freindly manner).. Spent soo much time trying to figure this out. Your solution fixed it for me right away. Geez, was pulling my hair out. YAY! Now I can finish our intranet lol..

 

Cheers dude..

Wednesday, December 3, 2008 6:30 AM

Hi there, I too have the same issue error -  ["External table is not in expected format"].. I use Excel 2003 , Asp.net 3.5

I export from Gridview to Excel and do changes in the Excel and after that I try to upload i get the same error.

 I fill data to the empty cells.I dont add or remove columns/rows.

I create Excel from Gridview using

Response.Clear();

Response.AddHeader("content-disposition", "attachment;filename=test.xls");

Response.Charset = "";

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd.xls";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

GridViewIndicatorData.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();

And Read Excel to load in to GridView using

string strConnection;

 

char[] ext = { '.', 'x', 'l', 's' };

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePathUpdated + ";"

+ "Extended Properties='Excel 8.0;IMEX=1'";

OleDbDataAdapter indicatorCommand = new OleDbDataAdapter("SELECT * FROM [" + "SheetName" + "$]", strConnection);

 

DataSet sourceDataSet = new DataSet();

srcCommand.Fill(sourceDataSet , "ExcelInfo");

GridViewIndicatorData.DataSource = indicatorDataSet.Tables[0].DefaultView;

GridViewIndicatorData.DataBind();

Kindly help me to rectify this errorI have spend lot of time in this already .

 thanks a lot for your help

 Raj

Wednesday, January 14, 2009 11:49 PM

 hi all 

 i am also getting the same error. i am loading the file dynamically(**i used the same code in WPF and its working fine**)

               string absPath = Server.MapPath(username + "\Projects\" + projects + "\" + folder + "\" + fileName);
                string  connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;";
                connectionString += "Data Source=";
                connectionString += absPath + ";";
                connectionString += @"Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""";

                string strSQL = "SELECT * FROM [Sheet1$]";
                OleDbConnection excelConnection = new OleDbConnection(connectionString);
                excelConnection.Open(); // This code will open excel file. ###### getting error here...

can anyone help me out.

thanks balu.

Tuesday, January 27, 2009 5:13 PM

 

@Raj 

Instead of "Extended Properties='Excel 8.0;IMEX=1'"

use

@"Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""" ( Notice the double quotes instead of single and user HDR=YES if your excel file has column headings)

HTH

 

Wednesday, January 28, 2009 10:55 AM

@Balu

 Take a look at this post.I have posted the code that is working.Also the post has a very good link explaining the issue.

http://forums.asp.net/t/1375170.aspx 

HTH

Wednesday, May 27, 2009 12:13 AM

Hi, i hope this code will help you :

 

  protected void btnSubmit_Click(object sender, EventArgs e)
    {
        if (flpStores.HasFile)
        {
            string sFileName = Path.GetFileNameWithoutExtension(flpStores.FileName) + "_" + DateTime.Now.ToFileTime().ToString()+ Path.GetExtension(flpStores.FileName);
            flpStores.SaveAs(Server.MapPath("~/ClientBin/" + sFileName));
            CreateXMLFile(sFileName, Path.GetExtension(flpStores.FileName));
        }
    }

    void CreateXMLFile(string sFileName,string ext)
    {
        try
        {
            string sConnectionString = string.Empty;
            string applicationPath = Request.ApplicationPath;
            applicationPath = Server.MapPath(applicationPath);
         if (ext == ".xls")
         {
             sConnectionString = string.Format(System.Globalization.CultureInfo.InvariantCulture, "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source={0}\ClientBin\{1}; Extended Properties=Excel 8.0;", applicationPath, sFileName);
         }
         else
             sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/ClientBin/" + sFileName) + ";Extended Properties=Excel 12.0";

            // Change code here  Date: 05/15/09
            //string sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/ClientBin/" + sFileName) + ";Extended Properties=Excel 12.0;";
           
            OleDbConnection con = new OleDbConnection(sConnectionString);

             OleDbCommand cmd = new OleDbCommand("SELECT * from [Sheet1$]", con);
            if (con.State == ConnectionState.Closed)
                con.Open();
                         OleDbDataReader drSheet = cmd.ExecuteReader();
                
                StringBuilder sXML = new StringBuilder();
                if (drSheet.HasRows)
                {
                    sXML.Append("<?xml version=\1.0\ encoding=\utf-16\ ?>");
                    sXML.Append("<StoreLocators>");
                    while (drSheet.Read())
                    {
                        sXML.Append("<sData Storename=\" + Server.HtmlEncode(drSheet["Storename"].ToString().Replace("'", "''").Replace("&", "&")) + "\");
                        sXML.Append(" Address=\" + Server.HtmlEncode(drSheet["Address"].ToString().Replace("'", "''").Replace("&", "&") )+ "\");
                        sXML.Append(" Cityname=\" + Server.HtmlEncode(drSheet["Cityname"].ToString().Replace("'", "''").Replace("&", "&")) + "\");
                        sXML.Append(" Statecode=\" + Server.HtmlEncode(drSheet["Statecode"].ToString().Replace("'", "''").Replace("&", "&")) + "\");
                        sXML.Append(" Zip=\" + Server.HtmlEncode(drSheet["Zip"].ToString().Replace("'", "''").Replace("&", "&")) + "\");
                        sXML.Append(" Email=\" + Server.HtmlEncode(drSheet["Email"].ToString().Replace("'", "''").Replace("&", "&")) + "\");
                        sXML.Append(" Phone=\" +                    sXML.Append("</StoreLocators>");
                    drSheet.Close();
                   
                }
                //cmd.Dispose();
              con.Close();
               
                string ss = sXML.ToString();
                StoreLocationDetails oStoreLocationDetails = new StoreLocationDetails();
                int iRow = oStoreLocationDetails.BulkInsert_StoreLocation(ss);
               
            //}
            lblMessage.Text = "File Uploaded Successfully";
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
           // throw ex;
        }
    }

 

Wednesday, July 15, 2009 2:06 AM

THNX DEAR 

I WAS HAVING SAME PROBLEM WHEN I DOWNLOAD ANY EXCLE THROUGH C# CODE SO ITS FORMAT IS DIFFERENT FROM OTHERE EXCEL FILE  

WHEN I UPLOADED THIS FILE I WAS GETTING THE SAME ERROR 

WHEN I CHANGED PROVIDER ITS WORKING FINE 

provider=Microsoft.Jet.OLEDB.4.0; data source='C:\ABC.XLS';Extended Properties='HTML Import

Wednesday, August 12, 2009 4:28 AM

Hi everybody!

This is such a great method to read excle file, thanks to everybody for all the suggestions  .

I'm experiencing just a "little big" problem, when I read a sheet without columns header, the data adapter uses the cells from the first data row as the columns headers   and so I miss the first data cells.

...any help  ?

tnx in advance

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +

                "Data Source=" + inputPath + ";" +

                "Extended Properties=\Excel 12.0 Xml;HDR=YES\;";

                OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);

                DataSet myDataSet = new DataSet();

                

                dataAdapter.Fill(myDataSet, "Data")

Wednesday, August 12, 2009 4:37 AM

add HDR in your connection string for reading excell sheet, if your excel file has column headings

**HDR=YES **

hope this will help you

Wednesday, August 12, 2009 4:48 AM

add HDR in your connection string for reading excell sheet, if your excel file has column headings

HDR=YES 

hope this will help you

I tried specifying HDR=NO and it worked perfectly  ... thank you!!

Wednesday, August 12, 2009 4:54 AM

Please remember to click “Mark as Answer” on the post that helps you, it will help other(s) to get there answer.

Wednesday, August 12, 2009 2:25 PM

I am having a similar problem to what is in this thread. The excel file I need to connect to is being generated by a classic ASP page so it is in HTML format. I will be uploaded this file and connecting to it with .NET in C#. I am able to create an OleDbConnection with a dataset using ExternalProperties="HTML Import;HDR=YES;IMEX=1", however I am not able to connect  through my code:

const string connStringTemplate =
    "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source={0};" +
    "Extended Properties=\"HTML Import;HDR=YES;IMEX=1\"";

string sheetName = "Sheet1";
string select = String.Format("SELECT * FROM [{0}$];", sheetName);
string connString = String.Format(connStringTemplate, saveFile);

DataSet workbook = new DataSet();
using (OleDbConnection oConn = new OleDbConnection(connString))
{
        oConn.Open(); //this line gives Unspecified error
        DataAdapter oda = new OleDbDataAdapter(select, oConn);
        oda.Fill(workbook);
}

 

Thursday, August 13, 2009 1:01 PM

 I think I'm having some kind of dev environment issue. I ran the same code on another box in my office and it connects. I don't know what the specific issue is though.

Tuesday, November 24, 2009 2:49 AM

I recently ran into the same error message and found out that the root cause was that I was using a connection string format taht works
only for Excel 97/2003 with a file created as Excel 2007. You can see below that there is quite a difference. Worked fine after using the
right conn string (that of 2007).

//2007
connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + inputExcelFileName + @";Extended Properties=""Excel 12.0 Xml;HDR=YES;""";

//97/2003
connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + inputExcelFileName + @";Extended Properties=""Excel 9.0;HDR=YES;IMEX=1;""";

 

Friday, May 14, 2010 9:15 AM

I have tried all of the suggested items above without success.

I have an application that allows customers to upload Excel files into our system.  The only time I get the error: "External table is not in the expected format." is when the user has renamed the default sheet (Sheet1) to another name say (Test Sheet).

Here is a snippet of my code:

         If bolXLSX = False Then
                cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended Properties='Excel 8.0;IMEX=1;HDR=NO;'"
                cn.Open()
            Else
                                cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test.xls;Extended Properties='Excel 12.0 Xml;IMEX=1;HDR=NO;'"
                cn.Open()
            End If

My issue is that I have no way of knowing if the user has renamed the default sheet name when I am uploading the Excel file.

Any advice?

Tuesday, June 29, 2010 6:50 AM

You can find the sheet names using the below code

String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
        "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
    // Create connection object by using the preceding connection string.

    objConn = new OleDbConnection(connString);
    // Open connection with the database.

    objConn.Open();
    // Get the data table containg the schema guid.

    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
 
    if(dt == null)
    {
      return null;
    }

    String[] excelSheets = new String[dt.Rows.Count];
    int i = 0;

    // Add the sheet name to the string array.

    foreach(DataRow row in dt.Rows)
    {
      excelSheets[i] = row["TABLE_NAME"].ToString();
      i++;
    }

From this u can select the users sheet number
eg:

string sheetName = excelSheets[0];
        OleDbCommand cmd = new OleDbCommand("Select * from [" + sheetName + "]", oledbConn);
  • Last updated on