CODEWITHSUNDEEP
c#sql-serverexcel
Amr Kamal
Amr Kamal

Reputation: 137

"The given ColumnMapping does not match up with any column in the source or destination" while importing Excel into SQL Server using C#

I am getting an error

The given ColumnMapping does not match up with any column in the source or destination

with the following code

private void button1_Click_1(object sender, EventArgs e)
{
    of1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";

    if ((of1.ShowDialog()) == System.Windows.Forms.DialogResult.OK)
    {
        imagepath = of1.FileName; //file path
        textBox1.Text = imagepath.ToString();
    }
}

private void loadbtn_Click(object sender, EventArgs e)
{
    string ssqltable = comboBox1.GetItemText(comboBox1.SelectedItem);
    string myexceldataquery = "select * from ["+ ssqltable + "$]";

    try
    {
        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+imagepath+";Extended Properties='Excel 12.0 Xml; HDR=YES;IMEX=1;';");
        string ssqlconnectionstring = "Data Source=.;Initial Catalog=Bioxcell;Integrated Security=true";

        OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oconn);
        oconn.Open();

        OleDbDataReader dr = oledbcmd.ExecuteReader();

        SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
        bulkcopy.DestinationTableName = ssqltable;

        while (dr.Read())
        {
            bulkcopy.WriteToServer(dr);
        }

        oconn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }

    /* DisplayingData DD = new DisplayingData();
    DD.Show();*/
}

I think SQL Server is case sensitive and I copied the same column names but the same error ..

Excel File

Database

Is there any way around this?

Upvotes: 2

Views: 14944

Answers (1)

waka
waka

Reputation: 3407

Use SqlBulkCopy.ColumnMapping like this

for (int i = 0; i < dr.FieldCount; i++) {
    bulkcopy.ColumnMappings.Add(i, i);
}

I just created a test table and a test file, according to your images. It worked fine for me, but would only add the first row of data.

Maybe you should make use of a DataTable and try again:

DataTable dt = new DataTable();
dt.Load(oledbcmd.ExecuteReader());
SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
bulkcopy.DestinationTableName = ssqltable;
for(int i = 0; i < dt.Columns.Count; i++){
    bulkcopy.ColumnMappings.Add(i,i);
}
bulkcopy.WriteToServer(dt);

When I tried it like that all my test rows got added to the database.

Upvotes: 2

Related Questions