SQL bulk insert does not insert values properly into table

.net excel sqlbulkcopy sql-server-2008-r2

Question

I'm attempting to transfer the data from an Excel file (.xlsx) into a SQL server table. I'm inserting the data in bulk using SQL bulk copy. The data is entered into the table, but I discover that it was not entered correctly.

The Excel data sample is shown below.

enter image description here

The SQL bulk copy code is as follows:

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 following insert:

enter image description here

In an excel sheet, the initial column value is absent and just the final row is added. In the table, the xid column is an auto-increment column.

With 'load data infile,' this process was simple in MySql, but I just switched to Sql server. What exactly is wrong with my code? Please provide suggestions.

1
0
2/3/2013 4:28:03 AM

Accepted Answer

Actually, I'm a little shocked that you aren't mentioning an exception.

In any case, I believe that you need to define the column mappings, which may be the cause of your issue (at least in part).

zzz-13 to zzz

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.

Your Excel file and database tables match not on all columns, as far as I can see (e.g. the auto-increment). Therefore, I'd try mentioning your table mappings.

2
2/3/2013 4:21:20 AM

Popular Answer

As a follow-up to my previous remark, it seems that you need to add another row because you're just lacking the first row.HDR=No the connection string.

It should resemble the following:

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

--EDIT

Note the additional apostrophes.

Good fortune.



Related Questions





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