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, Feb 03 '13 at 04:13

sgeddes · Answer 1 · 2013-02-03T04:37:08.697

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 03 '13 at 04:37

answered Feb 03 '13 at 04:30

sgeddes

62,311
6
61
83

Sorry that generates an exception after adding HDR=No; `Could not find installable ISAM.` – Shiridish Feb 03 '13 at 04:33
See http://stackoverflow.com/questions/512143/error-could-not-find-installable-isam/512187#512187 – Nathan Feb 03 '13 at 04:35
http://stackoverflow.com/a/644365/1073631 -- see if that doesn't help -- I just copied and pasted :) – sgeddes Feb 03 '13 at 04:35
+1 that solved the issue of missing the first row. However I still have the issue with auto increment. – Shiridish Feb 03 '13 at 04:48
Can you elaborate? Not sure I'm understanding your other issue. – sgeddes Feb 03 '13 at 04:50
You can see the screen shots in the question. The value `a1_sec-ecil` isn't inserted in the table and the radius column was inserted with null which should be 3 (as per the example) – Shiridish Feb 03 '13 at 04:56
Nathan's answer solved the issue with autoincrement filed. Confused who's answer to accept :) – Shiridish Feb 03 '13 at 05:07
No worries -- glad we could help. – sgeddes Feb 03 '13 at 05:27

score 2 · Accepted Answer · answered Feb 03 '13 at 04:21

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 03 '13 at 04:21

Nathan

10,593
10
63
87

just a guess that first roa i – Hiten004 Feb 03 '13 at 04:30
Sorry about top comment. It was a mistake. Sorry – Hiten004 Feb 03 '13 at 04:40
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. – Shiridish Feb 03 '13 at 05:06

SQL bulk insert does not insert values properly into table

2 Answers2