6

I'm working on an ASP.NET MVC4 projet and I'm trying to export data from an xlsx file (Excel 2010 file) to my database by using SQL Bulk Copy. My Excel file contains only 2 columns : the first contains numbers (from 1 to 25) and the second contains characters (successive series of "a, b, c")

This is how I try to do in order to export data but I got the error "The given value of type String from the data source cannot be converted to type int of the specified target column" : 

public ActionResult Bulk()
{    
    string xConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\maarab\Documents\BulkCopy.xlsx;Extended Properties=Excel 12.0;";
    using (OleDbConnection connection = new OleDbConnection(xConnStr))
    {
        OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
        connection.Open();    
        string dbConnection = ((EntityConnection)db.Connection).StoreConnection.ConnectionString;

        // Create DbDataReader to Data Worksheet
        using (DbDataReader dr = command.ExecuteReader())
        {
            using (var bulkCopy = new SqlBulkCopy(dbConnection))
            {    
                bulkCopy.DestinationTableName = "bm_test" 
                bulkCopy.WriteToServer(dr); //here I got the error    
            }
        }

        return RedirectToAction("Index");
}

Any idea about what's causing this error?

Traffy
  • 2,803
  • 15
  • 52
  • 78
  • what are the column types of bm_test? Looks like you're populating a date column with a string. – mcalex Jul 19 '13 at 08:02
  • there is a conversion problem ,u r trying to put string value into datetime datatype column.check your dr`. – Rahul Jul 19 '13 at 08:03
  • What is the structure of bm_test ? – Riv Jul 19 '13 at 08:04
  • @mcalex 3 columns : Id_Test (bigint, isIdentity), Int (int) and Str (varchar(50)) – Traffy Jul 19 '13 at 08:04
  • Sorry, was "int" instead of "datetime" in the error. – Traffy Jul 19 '13 at 08:07
  • Its because your trying to import the string into an int column. You will either need to convert it before insert or change the column to srting and convert after (i would probably go with before) – Dev N00B Jul 19 '13 at 08:13
  • @DevN00B Thanks for your advice. However, how can I proceed to do that? – Traffy Jul 19 '13 at 08:35
  • In your query instead of "Select * from [Sheet1$]" select the individual columns and wrap them in the convert...i Believe. not done it this way for a while – Dev N00B Jul 19 '13 at 08:46

4 Answers4

13

SqlBulkCopy.WriteToServer(DataTable) fails with confusing messages if the column order of the DataTable differs from the column order of the table definition in your database (when this causes a type or length incompatibility). Apparently the WriteToServer method does not map column names.

subsci
  • 1,740
  • 17
  • 36
  • Pro tip: this happened to me because I used an Entity Framework migration to add a new column. The column was the third property listed in the object, but the last column in the DB. – RJB Jul 23 '16 at 20:59
9

Mine was whining about this until I set the orders manually like so:

SqlBulkCopy sbc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
sbc.DestinationTableName = "agSoilShapes";
sbc.ColumnMappings.Add("Mukey", "Mukey");
sbc.ColumnMappings.Add("Musym", "Musym");
sbc.ColumnMappings.Add("Shapes", "Shapes");

DataTable dt = new DataTable();
dt.Columns.Add("Mukey", typeof(SqlInt32));
dt.Columns.Add("Musym", typeof(SqlString));
dt.Columns.Add("Shapes", typeof(SqlGeometry));

Thanks to others (@subsci) for comments leading me in this direction :)

jocull
  • 20,008
  • 22
  • 105
  • 149
  • 4
    THANK YOU. I spent way too much time trying to figure out where my String -> bool parse was going wrong (everything looks fine) until I found your "ColumnMappings.Add" solution, and it worked. OH BILLLLLLLLLLYYYYY – clamum Apr 20 '18 at 21:13
2

I have received a similar error when using EntityFramework.BulkInsert package. In this case the underlying cause was an order mismatch between database table columns and generated model metadata columns (database-first).

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
  • How did you get the column orders to match? – user3071284 Jun 01 '20 at 16:25
  • @user3071284 After three years I can hardly remember, but I think the POCO must have the properties defined in the exact same order as the columns in the table. This is rather strange since one expects the mapping to be done based on name, not on the order. – Alexei - check Codidact Jun 01 '20 at 16:58
  • Thanks, I did try that, but it may not work any longer. It looks like Entity Framework Core may be using alphabetical order while the database is in the same order as the POCO/model. – user3071284 Jun 01 '20 at 17:44
1

DataTable Columns order and Database tables' columns order should be the same. It worked after updating order of tables columns.

oyenigun
  • 587
  • 6
  • 15