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

Question

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 ..

Is there any way around this?

Solution

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.

Comments

[Amr Kamal]

inside the while statment ?

[waka]

@AmrKamal: No, before the while-loop. You only need to do the column mapping once.

[Amr Kamal]

when i put this loop inside for worked but added only one column .. then i put it outside before while .. shows the same error

[Amr Kamal]

still added first column only

[waka]

@AmrKamal Clutching at straws here, but are you sure you have all the columns from the excel file in the DataReader and not just one column that contains all the data?

[waka]

@AmrKamal also check if the table headings from the file are not in your data reader. The problem might be that you are trying to insert a string into an int column (ie adding the word "TerritoryCode" to the "TerritoryCode" column which might be defined as Integer).

[Amr Kamal]

all sql tables data types are nvarchar 50 and the header not added as u said

[waka]

@AmrKamal: I edited the answer to show how to add Data using a DataTable. Could you try out this solution and see if it works?

[Amr Kamal]

sorry but i don't understand i should use data reader and data table ? and then can u edit if i will remove while loop or not ? cause u make new sqlbulk and remove data reader(dr) .. sorry :(

[Amr Kamal]

i mean mapping doesn't work cause for loop contain dr.field count

[waka]

@AmrKamal I edited the usage for the ColumnMapping with a DataTable. You don't need a while-loop if you use DataTable since the whole table is inserted into the database at once (that's the whole point of using bulk copy in the first place ;) ).

[Amr Kamal]

i'm sorry for bother u :( it's worked but when i tried with only old file but when i tried with this file mentioned in the post didn't work and show me the same error .. the given columnmapping .. i don't know what i should do in this problem :(

[Amr Kamal]

bytheway i accepted ur answer :) but can u help me ?

[waka]

@AmrKamal I'm really out of ideas, sorry. I tried it with a test file myself and everything worked fine. :(