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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Import Excel workbook/form into SQL Server

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.
Go to Top of Page

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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-10-27 : 09:26:01
Other method is to use OPENROWSET http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -