16

I know it is possible to get data from a SQL database into an excel sheet, but i'm looking for a way to make it possible to edit the data in excel, and after editing, writing it back to the SQL database.

It appears this is not a function in excel, and google didn't come up with much usefull.

Rob1n
  • 215
  • 1
  • 4
  • 8

6 Answers6

6

If you want to have the Excel file do all of the work (retrieve from DB; manipulate; update DB) then you could look at ActiveX Data Objects (ADO). You can get an overview at:

http://msdn.microsoft.com/en-us/library/ms680928(VS.85).aspx

barrowc
  • 10,444
  • 1
  • 40
  • 53
5

You want the Import/Export wizard in SQL Management Studio. Depending on which version of SQL Server you are using, open SSMS (connect to the SQL instance you desire), right click on the database you want to import into and select Tasks.. "Import Data".

In the wizard, click Next (past the intro screen) and from the Data Source drop list select "Microsoft Excel". You specify the path and file name of the Excel spreadsheet, whether you have column headings or not.. then press Next. Just follow the wizard through, it'll set up the destination (can be SQL Server or another destination) etc.

There is help available for this process in SQL Server Books Online and more (a walkthrough) from MSDN.

If you need something deployable/more robust (or less wizard driven) then you'd need to take a look at SQL Server Integration Services (for a more "Enterprise" and security conscious approach). It's probably overkill for what you want to accomplish though.

RobS
  • 9,382
  • 3
  • 35
  • 63
3

There is a new Excel plug-in named "MySQL for Excel" : http://www.mysql.com/why-mysql/windows/

JSuar
  • 21,056
  • 4
  • 39
  • 83
Tansu KAYRAK
  • 31
  • 1
2

I just had a need to do this, and this thread has been quiet for a long time, so I thought it might be useful to supply a recent data point.

In my application roving salespeople use a copy of an Excel workbook that tracks the progress of a prospect through a loan application. The current stage of the application needs to be automatically saved back to a remote SQL database so that we can run reporting on it.

Rejected methods for updating the database from Excel:

SSIS and OpenRowSet are both methods for allowing SQL Server to pull the data from Excel, and don't work very well when the Excel workbook is sitting in an undefined location on a user's computer, and certainly not when the workbook is currently open in Excel.

ADO is now, if not actually deprecated, nevertheless looking very long in the tooth. Also, I wanted the solution to be robust in the face of the user possibly not being connected to the internet.

I also considered running a web API on the destination server. Macros in the Excel workbook connect to the web API to transfer data. However, it can sometimes be painful to allow a web API to talk to the outside world. Also, the code to make it robust in the face of temporary loss of internet connection is painful.

The adopted solution:

The solution I plan to adopt is low-tech: email. Excel emails the data to an address hosted on an Exchange server. Everyone in the company has Outlook installed, so the emails are sent by programmatically adding them to the Outlook Outbox. Outlook nicely handles the case when the user is offline. At the server end, a custom C# executable, fired up at regular intervals by the Task Scheduler, polls the inbox and processes the emails.

Andrew Partridge
  • 71
  • 1
  • 2
    This answer smells of ["Contrived Complexity"](https://en.wikipedia.org/wiki/Code_smell#Common_code_smells). Additionally, unless the "salesperson-loan app" relationship is strictly 1-to-1, as soon as you have multiple reps working on the same "account/app", you'd be running into major [data concurrency](https://stackoverflow.com/questions/4681280/whats-the-best-way-to-manage-concurrency-in-a-database-access-application) issues. Yes, the first email on the same account "wins" on the update, but the 2nd email from the 2nd salesperson with now-outdated data would overwrite the first. – katzbatz Jun 01 '20 at 15:34
1

You could use try these add-ins :

www.QueryCell.com (I created this one) www.SQLDrill.com www.Excel-DB.net

SamH
  • 1,238
  • 3
  • 17
  • 26
0

You can use the OPENROWSET function to manipulate Excel data from a T-SQL script. Example usage would be:

UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c:\MySpreadsheet.xls', 'Select * from MyTable') SET Field1='Value1' WHERE Field2 = 'Value2'

pmarflee
  • 3,428
  • 20
  • 21