Save Excel sheet into SQL

views:

139

answers:

+3 Q:

Save Excel sheet into SQL

Excel has a Get External Data ribbon bar in the Data tab where we can choose to import tables from SQL databases. This process worked out nicely for me. But my question is, is there any way to save this data back into SQL? Does Excel provide some API that facilitates the coding of such a function without parsing everything and doing it from scratch?

Thanks

The easiest way to do this is to use the import function within SSMS. You can select which sheets to use, customise column mappings and so on. If creates an SSIS that you can then manipulate further if required. However that approach is a pull from Sql, not a push from Excel, if you want to do that you'd have to code some VBA to do it for you.

MrTelly 2010-06-30 07:05:17

if little data is changed each time, can I write some sort of a connector in VBA such that each change is updated in the SQL table instead of taking the entire Excel sheet and rewriting everything in SQL?

Xster 2010-06-30 07:14:13

You'd have to code up all the logic, detect whether a cell was dirty and then run the relevant update. The code wouldn't be too hard, but there's plenty of things to take account of - right database, table, what's the key, how do you detect what's changed - .. http://stackoverflow.com/questions/2295225/concurrently-access-database-with-excel-as-frontend-doable

MrTelly 2010-06-30 10:13:33

+1 A:

It may not be the solution you are looking for, but I posted some VBA code a long while back that will take a range in Excel and convert it to XML and builds the SQL to put that data into a temp table in SQL Server. Here's a link if you are interested.

wcm 2010-06-30 13:45:41

Non-programmatically:

http://office.microsoft.com/en-us/excel-help/connect-to-import-sql-server-data-HA010217956.aspx

Programmatically - I can only think of the OpenRowSet function in MSSQL:

http://www.mssqltips.com/tip.asp?tip=1540

http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx

http://stackoverflow.com/questions/863735/using-openrowset-to-read-an-excel-file-into-a-temp-table-how-do-i-reference-that

code4life 2010-07-01 03:41:06

ansaurus

tags:

views:

answers:

Save Excel sheet into SQL

related questions