ERROR: Additional information: External table is not in the expected format

Question

You must import data from excel into Mysql. I am getting an error while retrieving an excel sheet in a database .i received an error message like this

external table is not in expected format

in a C # windows application form.So anyone will find exactly where the error is.

This is what I'm trying

using MySql.Data.MySqlClient; using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.Windows.Forms; namespace IMPORT { public partial class Form1 : Form { public Form1() { InitializeComponent(); } String MyConString = "SERVER=*******;" + "DATABASE=db;" + "UID=uid;" + "PASSWORD=pwd;" + "Convert Zero Datetime = True"; private void ButtonFile_Click(object sender, EventArgs e) { OpenFileDialog openfiledialog1 = new OpenFileDialog(); openfiledialog1.ShowDialog(); openfiledialog1.Filter = "allfiles|*.xls"; TextBox1.Text = openfiledialog1.FileName; } private void ButtonUpload_Click(object sender, EventArgs e) { String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + TextBox1.Text + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection xlConn = new OleDbConnection(connectionString); xlConn.Open(); DataTable data = new DataTable(); OleDbCommand selectCmd = new OleDbCommand("SELECT * FROM [Sheet1$]", xlConn); OleDbDataAdapter xlAdapter = new OleDbDataAdapter(); OleDbDataReader datare = selectCmd.ExecuteReader(); xlAdapter.Fill(data); DataSet xlDataset = new DataSet(); string sqlConnectionString = MyConString; using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) { bulkCopy.ColumnMappings.Add("id", "id"); bulkCopy.ColumnMappings.Add("password", "password"); bulkCopy.ColumnMappings.Add("name", "name"); bulkCopy.DestinationTableName = "TableName"; bulkCopy.WriteToServer(datare); MessageBox.Show("Upload Successfull!"); } } } } 

Thanks for your help in advance.

Answer 1

Aside from exceptions, make sure you always close connections. In any case, the following problems can be resolved:

 String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + TextBox1.Text + ";" + "Extended Properties=Excel 8.0;"; using (OleDbConnection excel_con = new OleDbConnection(connectionString )) { excel_con.Open(); DataTable dtExcelData = new DataTable(); //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default. dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)), new DataColumn("Name", typeof(string)), new DataColumn("Password",typeof(string)) }); using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excel_con)) { oda.Fill(dtExcelData); } excel_con.Close(); string consString = MyConString; using (SqlConnection con = new SqlConnection(consString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name sqlBulkCopy.DestinationTableName = "dbo.TableName"; sqlBulkCopy.ColumnMappings.Add("Id", "Id"); sqlBulkCopy.ColumnMappings.Add("Password", "Password"); sqlBulkCopy.ColumnMappings.Add("Name", "Name"); con.Open(); sqlBulkCopy.WriteToServer(dtExcelData); con.Close(); MessageBox.Show("Upload Successfull!"); } } } 

You must make sure that the column names match, and that you also correctly entered the table name.

This is based on an example found here .