SQL bulk insert does not insert values properly into table

.net excel sqlbulkcopy sql-server-2008-r2

Question

I have an Excel file(.xlsx) and I am trying to upload the content of the file into a Sql server table. Iam using the SQL bulk copy to bulk insert the data. The data gets inserted into the table but I find that the data is not inserted properly.

Here is the sample Excel data-

enter image description here

This is the code for Sql bulk copy:

string fname = Path.GetFileName(fup_addRoute.FileName);
fup_addRoute.SaveAs(Server.MapPath("/Admin/UserRoutes/" + fname));
string path = Server.MapPath("/Admin/UserRoutes/" + fname);

using (OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path)))
{
      OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
      connection.Open();
      System.Data.Common.DbDataReader dr = command.ExecuteReader();
      SqlBulkCopy bulkInsert = new SqlBulkCopy(con); 
      bulkInsert.DestinationTableName = "routesdata";
      bulkInsert.WriteToServer(dr);
      connection.Close();
      dr.Close();
      bulkInsert.Close();
}

Data after insert:

enter image description here

Only last row gets inserted and the first column value in excel sheet is missing. The xid column in table is an auto-increment column.

This procedure was easy in MySql with 'load data infile' but I just migrated to Sql server. What am I doing wrong in my code. Suggestions please.

Accepted Answer

I'm actually a bit surprised you are not reporting an exception.

At any rate, I suspect that (at least part of) your problem is that you need to specify the column mappings.

From http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopycolumnmapping.aspx

If mappings are not defined—that is, the ColumnMappings collection is empty—the columns are mapped implicitly based on ordinal position. For this to work, source and target schemas must match. If they do not, an InvalidOperationException will be thrown.

From what I can see your Excel file and your database tables do not match on all columns (e.g. the auto-increment). So I would try specifying your column mappings.


Popular Answer

As a follow-up to my earlier comment, since you're only missing the first row, it sounds like you need to add HDR=No to your connection string.

It should look something like this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No';

--EDIT

Please note the added apostrophes.

Good luck.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why