CODEWITHSUNDEEP
sqlsql-serverexcel
TripWire
TripWire

Reputation: 71

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.

Upvotes: 1

Views: 2310

Answers (3)

David Cram
David Cram

Reputation: 778

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.

Upvotes: 2

Nambu14
Nambu14

Reputation: 380

You can do the following:

You need to create an SSIS package and then create a job to run the package. The easiest way to create the SSIS package is with "Import and Export Data" tool of SQL Server. It has a nice step by step wizard.

Here you set the source

You set everything it asks you from the source and the destinations. Until you get here, select the "Save SSIS Package":

enter image description here

Then you only have to create the job to run it :)

Upvotes: 1

Mat Richardson
Mat Richardson

Reputation: 3606

You can use SQL Server Agent to run a scheduled job that imports data from an Excel worksheet into a SQL Server table.

The import is relatively straightforward to do using Integration Services, but if you've not used either of these before you might need to do some reading up on it.

Upvotes: 2

Related Questions