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(objec t sender, EventArgs e)
{
excelConnString = Path.GetFullPath(BatchExce lFileUploa d.PostedFi le.FileNam e);
SaveFileToDatabase(excelCo nnString);
}
#region SaveFile
private void SaveFileToDatabase(string filePath)
{
if (Path.GetExtension(filePat h) == ".xls")
{
excelConnString = String.Format("Provider=Mi crosoft.AC E.OLEDB.12 .0;Data Source={0}; Extended Properties=\"Excel 12.0\"", filePath);
}
else if (Path.GetExtension(filePat h) == ".xlsx")
{
excelConnString = String.Format("Provider=Mi crosoft.AC E.OLEDB.12 .0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
}
// Create Connection to Excel work book
OleDbConnection conn = new OleDbConnection(excelConnS tring);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
conn.Open();
DataTable dtSheet = conn.GetOleDbSchemaTable(O leDbSchema Guid.Table s, 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"].ToStri ng();
////Truncate Table
string sclearsql = "Truncate Table " + sheetName.Replace("$", "");
SqlConnection sqlconn = new SqlConnection(strConnectio n);
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.DestinationTableNa me = sheetName.Replace("$", "");
foreach (var column in dt.Columns)
{
sqlBulk.ColumnMappings.Add (column.To String(), column.ToString());
}
sqlBulk.WriteToServer(dt);
}
conn.Close();
}
}
}
}
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(objec
{
excelConnString = Path.GetFullPath(BatchExce
SaveFileToDatabase(excelCo
}
#region SaveFile
private void SaveFileToDatabase(string filePath)
{
if (Path.GetExtension(filePat
{
excelConnString = String.Format("Provider=Mi
}
else if (Path.GetExtension(filePat
{
excelConnString = String.Format("Provider=Mi
}
// Create Connection to Excel work book
OleDbConnection conn = new OleDbConnection(excelConnS
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
conn.Open();
DataTable dtSheet = conn.GetOleDbSchemaTable(O
conn.Close();
// iterate each sheet
foreach (System.Data.DataRow sheet in dtSheet.Rows)
{
conn.Open();
DataTable dt = new DataTable();
string sheetName = sheet["table_name"].ToStri
////Truncate Table
string sclearsql = "Truncate Table " + sheetName.Replace("$", "");
SqlConnection sqlconn = new SqlConnection(strConnectio
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.DestinationTableNa
foreach (var column in dt.Columns)
{
sqlBulk.ColumnMappings.Add
}
sqlBulk.WriteToServer(dt);
}
conn.Close();
}
}
}
}
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);
}
ASKER
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.OleDbExc eption: External table is not in the expected format.
Source Error:
Line 83: conn.Open();
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.OleDbExc
Source Error:
Line 83: conn.Open();
ASKER
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!
Try this.. Change below line and try again.
OLD - cmd.CommandText = "select * from [" + sheetName + "]";
NEW - cmd.CommandText = "select * from [" + sheetName.Replace("$", "") + "]";
Hope it helps!
ASKER
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(O leDbSchema Guid.Table s, null);
Line 86: conn.Close();
Line 82: OleDbDataAdapter da = new OleDbDataAdapter(cmd);
Line 83:
Line 84: conn.Open();
Line 85: DataTable dtSheet = conn.GetOleDbSchemaTable(O
Line 86: conn.Close();
Hi,
Pls try this.. for the connection string.
Hope it helps !
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);
Hope it helps !
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Experts , it seems like Windows 10 64 bit system issue , the same code is working on windows 7 machine.
ASKER
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+).
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+).
Normally xls is : Excel 12.0
and xls : Excel 8.0
Regards