1

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?

Improve this question
6
  • If you have access to SQL Server Management Studio, you can use the Import/Export wizard. Commented Mar 7, 2017 at 19:48
  • I have but others users won't, there's anyway to "call" the wizard using C#? Commented Mar 7, 2017 at 19:49
  • What version of SQL Server do you have installed? Commented Mar 7, 2017 at 19:51
  • excel-sql-server.com/… Commented Mar 7, 2017 at 19:52
  • msdn.microsoft.com/en-us/library/ms175915.aspx Commented Mar 7, 2017 at 19:53

1 Answer 1

Reset to default
2

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

Improve this answer
3
  • will that work for Excel? This looks a .csv import. Commented Mar 7, 2017 at 21:29
  • 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 Commented Mar 7, 2017 at 21:48
  • 1
    @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#. Commented Mar 7, 2017 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.