Excel to SQL. Alternative to BulkCopy?

Question

I have a piece of code which copies data from an excel spreadsheet to a MSSQL table using DataReader and SqlBulkCopy. It worked fine until I created a primary key on the table and now it fails. I am first deleting the contents of the SQL table before filling it again with the data from excel.

As it is only a small amount of data I am moving, I wondered if there was a better way to do this than using BulkCopy?

Update: below is the relative code and the error I receive is: "The given value of type String from the data source cannot be converted to type float of the specified target column."

using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                connection.Open();
                OleDbCommand cmd = new OleDbCommand
                ("SELECT Name, Date, Amount FROM ExcelNamedRange", connection);

                using (OleDbDataReader dr = cmd.ExecuteReader())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                    {
                        bulkCopy.DestinationTableName = "SqlTable";
                        bulkCopy.WriteToServer(dr);

                    }
                }                 
            }

What is the problem with having the primary key there, what errors are you getting? — Lasse V. Karlsen
@Lasse I have updated my post with the error and the code which causes it. — Brian

Pleun Pleun · Accepted Answer · 2011-03-25T12:26:37

SqlBulkCopy automatically maps the fields. But since you added a primary key that default mapping is no longer valid.

You will have to set ColumnMapping to tell your SqlBulkCopy object explicityly how to map the fields.

Do this for all your fields, except the primary key (assuming you use an identity on the PK).

For example:

_bulkCopyEngine.ColumnMappings.Add("fieldname_from", "fieldname_to");

Excel to SQL. Alternative to BulkCopy?

5 Answers