Excel to SQL. Alternative to BulkCopy?

c# sql sqlbulkcopy

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);

                    }
                }                 
            }

Accepted Answer

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");

Popular Answer

Creating a primary key, suggests you are enforcing a domain constraint (a good thing).

Therefore, your actual problem is not that you need another way to perform the bulk insert, but that you need to find out why you have duplicate keys (the precise reason for enforcing the PK).



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow