Easy way to update Excel data to SQL Server

Question

I have a database with some tables "Products, Cores, Clients, ProdCliPrice, etc"

I am developing a software and one of it's functions is to update (or insert) the data in an Excel file to my database.

The Excel file contain a ProductID, a Core Name and a Product Price.

I wrote a code that stores all Core Names and Ids to a array, and then, line by line insert (or update if already exists) the product to the database. Since I have more than 100k rows, it is taking too long.

Could you help me with a better way to insert or update these data?

• blobbles: If you have access to SQL Server Management Studio, you can use the Import/Export wizard.
• G. Knoxx: I have but others users won't, there's anyway to "call" the wizard using C#?
• blobbles: What version of SQL Server do you have installed?
• McNets: excel-sql-server.com/…
• McNets: msdn.microsoft.com/en-us/library/ms175915.aspx

Answer 1

You coud either use BULK INSERT directly in SQL Server Management Studio like this:

BULK INSERT [DataBase].[dbo].YourTable
FROM 'filepath'
WITH
(
 Firstrow = 1,
 Fieldterminator=';',
 Rowterminator='\n',
 Codepage = 'ACP'
)

For Excel-Files (.xls) you can use this:

SELECT * INTO YourTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=YourFilePath', [ExcelSheet$])

You may have to replace the 8.0 with your current office version (8.0 is for Office 2000, 14.0 is for Office 2010, for example).

There's also SqlBulkCopy in C#.

If you don't want to import all columns, I suggest you write a small program, load the file into a DataTable, drop all the columns you don't need and then use SqlBulkCopy

• Hannah Vernon: will that work for Excel? This looks a .csv import.
• G. Knoxx: This really looks like a .csv import; My Excel file have headers and if it's possible I don't want to import all columns
• waka: @G.Knoxx I updated the answer with an import for excel files. But it sounds like you're better off writing a small program in C#.