Add text when using SQLBulkCopy to SQL Server from excel

asp.net import-from-excel sqlbulkcopy

Question

I've created a page where our partners can upload excel files with statistics using SQLBulkCopy. The file has multiple sheets and I need to add the name of the sheet into a column in the DB for each row. The names of the sheets will not change so hardcoding the names is ok. How can I solve this?

Accepted Answer

As the name of the sheets doesn't change I solved it this way:

  1. Created a temporary table with the same layout as the final.

  2. Imported the data from the first sheet to temporary table.

  3. Filled the column which was now null with the correct value.

  4. Did nr 2->3 for the next sheet until all sheets were imported.

  5. Checked the temporary table for errors.

  6. Imported the data from the temporary table to the final table one row at the time to be able to spot if the records already exist and if so update it, else insert it.



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