Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tmcrouse
Starting Member
12 Posts |
 Posted - 2014-10-20 : 08:51:40
|
| My database has mirgrated from Access to a SQL Server. As of now, we use SharePoint for our data entry form and it works great exporting an Excel workbook to my desktop and then import to the SQL Server and drop it in the tables that apply. The management team wants to get rid of SP and have all 110 project owners use a template Excel workbook I created and then anytime they have new projects or changes to existing they use this template and email it to me. The problem is the template has partial text, combox boxes, checkboxes and drop-down boxes. In Access I can spend a couple weeks creating VBA code to import this but not sure how this is done in SQL Server. The 2nd issue is why in the heck would they even want to go this route. This seems to be a coding nightmare, then I have to store all the Excel workbooks somewhere for history sake. It just seems to me that keeping the SP site is much smarter for them and for me. My question is not related to SP because I am going to push to keep that up anyway. I just want to know is it possible for SQL Server to import a workbook that has form functions on it easily or is it a stored procedure coding nightmare?tmc |
|
|
Mar
Starting Member
47 Posts |
Posted - 2014-10-21 : 10:53:14
|
| A stored procedure coding nightmare is about as subjective as you can get, so I can not answer that one.But if you know VBA then it should be a piece of cake. Excel can leverage the power of VBA, you may just have to figure out how to connect to a database from Excel using ODBC. http://www.connectionstrings.com/excel/ may help. To import the data you can use SQL, namely INSERT or UPDATE statements. If you do not know SQL then you could have problems. Just remember that SQL operates on sets and is different than traditional coding languages. Comboboxes, checkboxes, dropdowns and cells are all really pretty much the same thing as far as data is concerned, they just display it in a different way and/or restrict input to/for the users. You just need to get the value from the appropriate control and store it in the appropriate field.If you do not like coding then it can be a coding nightmare. A programmer could enjoy this project. |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-21 : 10:57:43
|
| I'd do it with SSIS. I would not use email to get the spreadsheets, but have them dropped in a shared folder. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
hungtq2701
Starting Member
1 Post |
Posted - 2014-12-02 : 10:47:45
|
| Hi all,I have a small tool to Import Excel, it from [url]http://softwaredanang.wordpress.com[/url]Regards. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-12-03 : 08:56:01
|
quote:
Originally posted by hungtq2701
Hi all,I have a small tool to Import Excel, it from [url]http://softwaredanang.wordpress.com[/url]Regards.
I think your tool is redundant. It does little more than what SSIS does out of the box |
|
|
|
|
|
|
|
|
Import Excel workbook/form into SQL Server