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

Question

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.

Try this answer: stackoverflow.com/a/1363480/4273349 Hope this helps you!. — Naveen - நவீன்
– Naveen - நவீன், Commented Dec 15, 2015 at 12:29

Chief Wiggum · Accepted Answer · 2015-12-15 12:40:09Z

1

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.

edited Dec 15, 2015 at 12:40

answered Dec 15, 2015 at 11:38

Chief Wiggum

2,9642 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user5475686 Over a year ago

thank you JAMESBLOND. sir now got an error Additional information: There is no row at position 0. ...

user5475686 Over a year ago

string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString(); error in this line why they shows no row at 0 position??? please help me sir.

Chief Wiggum Over a year ago

I've updated the sheetName logic a bit. Are you sure your excel sheet is called Sheet1?

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related