Reputation: 11
I want to import excel file data in to SQL Server but this gives an error as shown below:
external table is not in the expected format xls
I am working on windows 8.1 OS and excel 2013. I am using the following code.
try
{
if (FlUploadcsv.HasFile)
{
string FileName = FlUploadcsv.FileName;
string filePath = "C:\\Users\\admin\\Desktop\\Sheet1.xlsx";
string path = filePath;// string.Concat(Server.MapPath("~/Document/" + FlUploadcsv.FileName));
FlUploadcsv.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
ds = new DataSet();
objAdapter1.Fill(ds);
Dt = ds.Tables[0];
}
}
catch (Exception ex)
{
}
Upvotes: 0
Views: 7984
Reputation: 112
Here is a similar way to do it:
string filename = System.IO.Path.GetFileName(FileUpload1.FileName);
if (FileUpload1.HasFile == true) {
string fp = System.IO.Path.GetDirectoryName(FileUpload1.FileName);
string full = "C:\\Users\\user\\Documents\\" + filename;
TextBox1.Text = full;
//FileUpload1.SaveAs(Server.MapPath("Files/" + filename))
try {
string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=full;Extended Properties=Excel 8.0;HDR=YES;";
string cmdStr = "Select * from [Sheet1$]";
using (OleDbConnection oledbconn = new OleDbConnection(connStr)) {
using (OleDbCommand oledbcmd = new OleDbCommand(cmdStr, oledbconn)) {
oledbconn.Open();
OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);
DataSet ds = new DataSet();
oledbda.Fill(ds);
//save to an SQL Database
oledbconn.Close();
}
}
} catch (Exception ex) {
TextBox2.Text = ex.ToString();
}
}
Upvotes: 0
Reputation: 1655
First you need to Replace Connection string :
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
With this :
If you are use import for .xls
then use below one :
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "; Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
If you are use import for .xlsx
then use below one :
OleDbConnection OleDbcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
Try this blow one :
try
{
if (FlUploadcsv.HasFile)
{
OleDbConnection OleDbcon;
OleDbCommand cmd = new OleDbCommand(); ;
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
DataSet ds = new DataSet();
DataTable dtExcelData = new DataTable();
string FileName = FlUploadcsv.FileName;
string filePath = "C:\\Users\\admin\\Desktop\\Sheet1.xlsx";
string path = filePath;// string.Concat(Server.MapPath("~/Document/" + FlUploadcsv.FileName));
FlUploadcsv.PostedFile.SaveAs(path);
if (Path.GetExtension(path) == ".xls")
{
OleDbcon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "; Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
}
else if (Path.GetExtension(path) == ".xlsx")
{
OleDbcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
}
OleDbcon.Open();
cmd.Connection = OleDbcon;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [Sheet1$]";
objAdapter1 = new OleDbDataAdapter(cmd);
objAdapter1.Fill(ds);
dtExcelData = ds.Tables[0];
string consString = "Your Sql Connection string";
/* You want insert into your sql database table using SqlBulkCopy. */
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "SqlDatabase Table name where you want insert data";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add(".xls/.xlsx Header column name(Id)", "Your database table column(IndexId)");
.
.
.
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
/* You want insert into your sql database table using SqlBulkCopy. */
}
}
catch (Exception ex)
{ }
Upvotes: 0