Link to home
Start Free TrialLog in
Avatar of Afzaal Khan
Afzaal Khan

asked on

Excel import error " External table is not in expected format"

Hi All,

Please help me here.

Below is my code where I have to upload excel with multiple worksheets to sql database.

The code is working for .xls file but when I am choosing .xlsx file it is throwing error "External table is not in expected format"

I looked in various forums and changed connection string with all possible ways but it didn't worked.

The worksheets name and columns are same as table name in sql db.

Need expert advise.

-------------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Data;
using System.Globalization;
using System.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Net;
using System.Text;
using Excel.Core;
using Excel;
using System.Threading;
using Resources;



namespace BusinessEventManager
{
    public partial class ValidateUpload : System.Web.UI.Page
    {
        String strConnection = "Data Source= <sql db server>;Initial Catalog= <sql db name> ;Integrated Security=True";
        String excelConnString;
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void ValidateButton_Click(object sender, EventArgs e)
        {


            excelConnString = Path.GetFullPath(BatchExcelFileUpload.PostedFile.FileName);

            SaveFileToDatabase(excelConnString);



        }


        #region SaveFile

        private void SaveFileToDatabase(string filePath)
        {

            if (Path.GetExtension(filePath) == ".xls")
            {
                excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=\"Excel 12.0\"", filePath);
            }
            else if (Path.GetExtension(filePath) == ".xlsx")
            {
                excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
            }


            // Create Connection to Excel work book
            OleDbConnection conn = new OleDbConnection(excelConnString);

            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);

            conn.Open();
            DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            conn.Close();

            // iterate each sheet
            foreach (System.Data.DataRow sheet in dtSheet.Rows)
            {

                conn.Open();
                DataTable dt = new DataTable();
                string sheetName = sheet["table_name"].ToString();

                ////Truncate Table
                string sclearsql = "Truncate Table " + sheetName.Replace("$", "");
                SqlConnection sqlconn = new SqlConnection(strConnection);
                SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
                sqlconn.Open();
                sqlcmd.ExecuteNonQuery();
                sqlconn.Close();

                ////End Truncate

                cmd.CommandText = "select * from [" + sheetName + "]";
                da.SelectCommand = cmd;
                da.Fill(dt);


                using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                {

                    // Destination table name.  Table name is sheet name minus any $
                    sqlBulk.DestinationTableName = sheetName.Replace("$", "");

                    foreach (var column in dt.Columns)
                    {

                        sqlBulk.ColumnMappings.Add(column.ToString(), column.ToString());
                    }

                    sqlBulk.WriteToServer(dt);

                }


                conn.Close();
            }


        }
    }
}
Avatar of Rgonzo1971
Rgonzo1971

Hi,

Normally xls is : Excel 12.0
and xls : Excel 8.0

Regards
Avatar of Afzaal Khan

ASKER

But xls is working xlsx is not.
Try this:
            else if (Path.GetExtension(filePath) == ".xlsx")
            {
                excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 12.0 Xml; HDR=YES'", filePath);
            }

Open in new window

same error after changing the above string.


External table is not in the expected format.
  Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

 Exception Details: System.Data.OleDb.OleDbException: External table is not in the expected format.

Source Error:


Line 83:             conn.Open();
Please help.
Hi,

Try this.. Change below line and try again.

OLD - cmd.CommandText = "select * from [" + sheetName + "]";

NEW - cmd.CommandText = "select * from [" + sheetName.Replace("$", "") + "]";

Hope it helps!
Thanks Pawan but it still failing, Actually it is not going to that line , failing at connection opening point (conn.open) .

Line 82:             OleDbDataAdapter da = new OleDbDataAdapter(cmd);
Line 83:
Line 84:             conn.Open();
Line 85:             DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
Line 86:             conn.Close();
Hi,
Pls try this.. for the connection string.

string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"", YourExcelFilePath);

Open in new window


Hope it helps !
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Experts , it seems like Windows 10 64 bit system issue , the same code is working on windows 7  machine.
Thanks Experts, it seems Windows10 64 bit issue
You have to be sure the correct drivers are installed on the target system. I normally point users to this:

https://www.microsoft.com/en-us/download/details.aspx?id=23734

I realize that is for 2007, but it will generally work for any ACE format file (2007+).