SQL bulk insert does not insert values properly into table

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.

Are you only getting the last row or just not receiving the 1st row? — sgeddes
– sgeddes, Commented Feb 3, 2013 at 4:13

sgeddes · Accepted Answer · 2013-02-03 04:37:08Z

3

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.

edited Feb 3, 2013 at 4:37

answered Feb 3, 2013 at 4:30

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Shiridish Over a year ago

Sorry that generates an exception after adding HDR=No; Could not find installable ISAM.

Nathan Over a year ago

See stackoverflow.com/questions/512143/…

sgeddes Over a year ago

stackoverflow.com/a/644365/1073631 -- see if that doesn't help -- I just copied and pasted :)

Shiridish Over a year ago

+1 that solved the issue of missing the first row. However I still have the issue with auto increment.

sgeddes Over a year ago

Can you elaborate? Not sure I'm understanding your other issue.

|

Nathan · Accepted Answer · 2013-02-03 04:21:20Z

2

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.

answered Feb 3, 2013 at 4:21

Nathan

10.8k10 gold badges66 silver badges90 bronze badges

3 Comments

Hiten004 Over a year ago

just a guess that first roa i

Hiten004 Over a year ago

Sorry about top comment. It was a mistake. Sorry

Shiridish Over a year ago

Great, your solution solved the issue with auto increment field. Thanks. Oh now who's answer do I accept, sdegges solved one and you solved the other issue.

Collectives™ on Stack Overflow

SQL bulk insert does not insert values properly into table

2 Answers 2

8 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related