mysql外部表不是预期格式,错误：附加信息：外部表不是预期的格式

Need to import data from excel to Mysql. I am getting an error when fetching the excel sheet to database table.i got an error message like this

external table is not in the expected format

in c# windows form application.So any one find where exactly the error.

This is what I am 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.

解决方案

Beside the exception, make sure that you always close connections. In any case, the following might fix your issues:

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 have to make sure that the column names match and that you table name is correct as well.

This is based on an example found here.