TripWire TripWire - 9 months ago 54
SQL Question

Automate export from MS Excel to MS SQL Server

Is there a way to export data from a MS Excel file into a SQL Server table automatically? Maybe this is done using a script of some kind.
If it's not possible to be completely automated, perhaps there's a way to do it using minimal user effort. (For example clicking a button or link)

There is a MS Excel spreadsheet where the data keeps having to be manually exported to SQL Server.

I've done this using Excel to Access before, but not too certain on how to do it using SQL Server (MS).

*MS Office 2013 and MS SQL Server 2012.

Answer Source

The other answers are ok. I just want to suggest an additional alternative.

If it is just 1 specific Excel file that is frequently updated, I would consider using VBA. For example, write some VBA code in Excel that uploads changes to the database when the spreadsheet is saved (or the user presses a button).

The problem with using a scheduled job is that Excel is basically a single user application. If someone has the spreadsheet open or is doing something in it when the scheduled job runs or moves the spreadsheet to a different folder, then the job may fail.

This way you also get the updated data in your database in something close to real time instead of waiting on a job to run. This might take more time and effort to set up though than some of the other answers.