Convert Excel spreadsheet into SQL Server stored procedure?

Question

I have an Excel spreadsheet that has a dozen calculated fields based off of users populating cells and looking up data in multiple tabs with static data. I would like to be able to create a stored procedure that takes parameters, populates values in the spreadsheet and returns values in specific cells.

Is this possible without reinventing the wheel and creating the data in SQL Server and moving the Excel formulas to SQL logic? I know there is an ODBC driver for Excel which SQL Server can connect to but I don't believe a stored procedure can reference values from formulas in the spreadsheet and populate specific cells?

Check this video: youtube.com/watch?v=gK2yBpiITvI. It might help you get start with what you want to achieve. The feasibility of your idea depends on how much data entry a user is doing in Excel vs. how much data is already in the database, and how Excel and database data are related. — zedfoxus
Thank you @zedfoxus but I want access to this through a SQL Server stored procedure — AAA
While possible, it'd be very awkward to do this. A first pass would have to write the values into Excel, and a second pass would be required to read the results. More importantly, the architecture makes me cringe. Why would you do this? SQL databases are horrible UIs. Whatever you're using to access SQL, why not just use it to access the spreadsheet? Too, why have a full-fledged database hosted on a database server configured to access a mere hackable spreadsheet? — Philip Kelley

RamenZzz RamenZzz · Accepted Answer · 2019-04-17T20:15:48

This sounds like it would be a job for SQL Server Integration Services SSIS which is a part of the Microsoft Business Intelligence Suite. If you are using company software it is likely that you'll have access to it. I say sounds like because I'm neither an expert in using SSIS and I'm not sure of the details of what you want to do. But I've used SSIS to update excel spread sheets, and looking into posts like the following indicate that you can probably individually pluck or populate excel cells: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/89cbc83b-46e3-41b5-9461-c1a4182880d8/how-to-update-multiple-single-excel-cells-using-ssis?forum=sqlintegrationservices

The wikipedia article will give you a broader overview of what SSIS can do and beardeddev has some good tutorials on youtube: https://www.youtube.com/watch?v=yV91Xkf9sKo

Convert Excel spreadsheet into SQL Server stored procedure?

1 Answers