November 9, 2010 at 1:17 pm
I'm curious if anyone on the forums here has done this or even read about it; using Excel as a front end to ETL data in a table. In our company the accountants are the power users of our primary app and as with all accountants they LOOOOOVE using Excel. To that end they are always taking data out of our accounting software app (usually via report) and pasting it into an Excel workbook/sheet, doing some massaging of the data and then make one or more decisions based on what they see.
I'd like to look into automating this with 1, possibly 2 custom tables. We need to implement a custom process into our primary accounting software to do something it can't do and for which it provides no customization equivalent. I could create something as I have before but after a meeting with the accountants and seeing firsthand how they arrive at what numbers to use I realize that the number of judgment calls they make in determining what kind of numbers to use makes any kind of automation of the process messy at best.
If my accountants could simply open Excel and have it query a table or two (thru a view possibly) and then let the user take that data and tweak the numbers as they like using whatever logic they choose and then have Excel push the update info back into an existing table or optionally insert new rows into a table (to retain a before and after set of the data) that could prove not only useful but possibly open up some options for a few other customization projects on the horizon.
Has anyone done anything like this and if so would you share your experiences and how you did this? I know its possible to export to Excel and even import from Excel to SQL Server but I was hoping that maybe there might be a not-so well-known method or even Excel Plug-In designed for just this kind of thing.
Any comments are appreciated.
Thanks
Kindest Regards,
Just say No to Facebook!November 9, 2010 at 1:22 pm
VBA (Visual Basic for Automation) is designed for exactly that kind of thing. You can program an Excel sheet to pull from SQL, push to SQL, pivot on SQL, etc. It's purely a matter of how much Visual Basic you know and what you want to get done.
You can probably do all of it through .NET these days. I haven't tried it since Office 2003, and it was all VBA then.
Wasn't all that hard to do, but I don't remember the details, and don't have access to any of that code any more. But I do remember that a complete coding newb (me) was able to get some pretty slick stuff done pretty easily, once I decoded the gibberish in Microsoft's documentation on the Excel objects.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2010 at 11:02 am
It is still handled through VBA. If you familair with VB6 this will not be a big stretch. I have done tons of this stuff but it has been several years. There is pretty much no limit to what you can make it do. If VB6 can do it you can make it work in Excel.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 11, 2010 at 12:03 pm
For returning the data you could call a stored procedure like below. This will allow you to pass parameters as well.
Uploading the data to sql server I haven't done but I would try to
1) save the data to csv using vba
2) call a stored procedure to import the csv
Private Sub comReturnData_Click()
Rows("6:500").Select
Selection.ClearContents
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String
Set cn = New ADODB.Connection
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "SERVER=Server1;INITIAL CATALOG=Database1;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
cn.Open strConn
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "usp1"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = Range("B1")
Set rs = cmd.Execute()
If Not rs.EOF Then
Worksheets("Yourworksheet").Range("A6:D500").CopyFromRecordset rs
rs.Close
Else
MsgBox "No data."
End If
cn.Close
Set rs = Nothing
Set cn = Nothing
Range("A1").Select
End Sub
November 11, 2010 at 12:26 pm
This is on my list of "stupid things no-one in their right mind would try" and I apologise to any DBA for revealing how you do this.
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\David\My Documents\TestData.xls',
NULL,
'Excel 5.0'
GO
My Excel file has Sheet1 as a two column table called ProviderID and ProviderName
SELECT [ProvideID]
,[ProviderName]
FROM [ExcelSource]...[Sheet1$]
GO
I am actively trying to stop people using Excel to read data where possible because I operate in a company where security and customer privacy is VERY important. The instant you get data flying into Excel spreadsheets you have a potential security breach.
If you end up with accountants building mission critical spreadsheet operations then once the data security cat is out of the bag you will never get it back in.
If someone can extract data from your database into Excel then File, Send, To Mail Recipient DodgyGeezer@DataTheftLocation.Com.
T-Mobile had a theft of their customers whose contracts were about to expire and it was internal employees who carried out the theft. I guess their renewal rate plummeted.
If you are listed on the stock exchange then your companies value is in some part due to perception. If a data security breach became public then your companies worth could plumet. If your company is worth something in terms of concrete assets then its hostile takeover time with the potential for corporate restructuring and redundancies.
People use Excel because it is flexible and easy to use. Ultimately it fulfills a business need. My approach in your situation would be to find out what processes they are actually carrying out in Excel and see if you can shift them into the DB. Sell it as a performance benefit. If necessary let them have summary data but keep access to raw data to a bare minimum.
November 11, 2010 at 12:34 pm
David.Poole (11/11/2010)
This is on my list of "stupid things no-one in their right mind would try" and I apologise to any DBA for revealing how you do this.EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\David\My Documents\TestData.xls',
NULL,
'Excel 5.0'
GO
YIKES!!!! That is scary. You should probably take a more tiered approach and create a webservice that Excel can consume. Then you do not have to expose your server to the outside world as much. Your webservice can then connect to the database and extract the data. I would have to agree this is not the best approach but sometimes business demands things that damn scary for the rest of us.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 11, 2010 at 1:17 pm
Sean Lange (11/11/2010)
David.Poole (11/11/2010)
This is on my list of "stupid things no-one in their right mind would try" and I apologise to any DBA for revealing how you do this.EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\David\My Documents\TestData.xls',
NULL,
'Excel 5.0'
GO
YIKES!!!! That is scary. You should probably take a more tiered approach and create a webservice that Excel can consume. Then you do not have to expose your server to the outside world as much. Your webservice can then connect to the database and extract the data. I would have to agree this is not the best approach but sometimes business demands things that damn scary for the rest of us.
Sean, you're reading it backwards. That's a connection TO Excel, a linked server to it to be precise.
The other way around is more complex. And, yes, a web service is better than a direct connection to SQL Server for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2010 at 1:20 pm
Guess I need to pick my head up off my pillow...that happens after lunch sometimes. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 11, 2010 at 1:21 pm
Sean Lange (11/11/2010)
Guess I need to pick my head up off my pillow...that happens after lunch sometimes. 😛
Pillow ?= Keyboard
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2010 at 1:22 pm
Ed Zachary
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 11, 2010 at 3:57 pm
David.Poole (11/11/2010)
This is on my list of "stupid things no-one in their right mind would try" and I apologize to any DBA for revealing how you do this.EXEC sp_....
People use Excel because it is flexible and easy to use. Ultimately it fulfills a business need. My approach in your situation would be to find out what processes they are actually carrying out in Excel and see if you can shift them into the DB. Sell it as a performance benefit. If necessary let them have summary data but keep access to raw data to a bare minimum.
David
I appreciate your stance and I agree but in this case we are working with custom data thats is neither private nor in need of any security beyond what is already in place. I could write some code to do what the accountants want for those things that a fixed formula exists however there are too many 'judgement call' formulas used to make something like that possible. Right now they get the data from running a report and exporting to Excel then they massage it as they like and I have to reimport into a temp table and manually update it simply because they can't nor do they know how to and more often then not I have to reformat some cells or columns because they have changed the data type without realizing it.
With the integration of Excel and Analysis Service (via PowerPivot or at elast I think thats what I reall it being named) I was hoping there was some kind of ismiliar integration with regular databases but alas I guess thats not true.
Thanks to all who posted rpelies and while there are some good ideas I was hopiong someone would point out an integarted feature betwen Excel and SQL Server that I just was not aware of.
Thanks again to all who posted.
Kindest Regards,
Just say No to Facebook!November 12, 2010 at 1:12 am
Following post may help: http://sqlwithmanoj.wordpress.com/category/excel/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply