Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Excel in the Clouds Expand / Collapse
Author
Message
Posted Monday, June 20, 2011 9:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:18 PM
Points: 33,155, Visits: 15,289
Comments posted to this topic are about the item Excel in the Clouds






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1128715
Posted Tuesday, June 21, 2011 5:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 6:47 AM
Points: 193, Visits: 26
Excel is the ultimate business tool. It essentially fills in the informtaion gap that the systems have not been programmed to allow. Just about every instance of an Excel file exists to take an export or summerization of system data and manipulate it into a more meaningful or presentable format.

The greatest challenge for developers it to provide tools and documentation to allow users to meaningfully report, group, sort and present the data the user and their customers (bosses, internal and external) require to make better business decisions. Unless and until systems allow users to understand, combine and report on the data in ways the original spec's required, Excel will continue to be an essential tool.
Post #1128909
Posted Tuesday, June 21, 2011 6:55 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
Excel is still a great application with extensive features and a robust programming language.

Just this weekend I had to import a large spreadsheet of data into SQL Server, but all the data were in pivot table format, and each sampling location was on its own separate tab. 15-20 tabs, each tab with several dozen rows and columns: in all, around 30,000 data points needing transformation into 5 columns.

The kicker: each tab had extraneous data such as header, notes, etc. and blank rows/columns, all of which needed to be skipped based on programmed criteria. Though I've used SSIS to import a single-tab, well-formatted, predictably layed-out spreadsheet, I haven't tried SSIS to deal with multi-tab, irregularly formatted spreadsheets. My guess is stepping through them and selecting only those meeting my criteria would not be fun.

An hour and half with VBA in Excel and I was done, and the code runs in well under a second. From there it was a trivial OPENROWSET to grab the correctly formatted data into SQL.

The right tool for the job!

Rich
Post #1128960
Posted Tuesday, June 21, 2011 7:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 1,644, Visits: 4,684
While we're on the topic of Excel in the Clouds, Microsoft's Excel Web App service provides not only the data but also the application in the cloud. It also allows the embedding of data from an Excel sheet into a web page using as an IFrame. This would be useful for sql server related blog posts where the readers can actually interfact with sample datasets that in the past have been confined to static HTML tables or screenshots.
http://office.microsoft.com/en-us/excel/embed-an-excel-spreadsheet-on-a-web-page-FX102602478.aspx
Post #1129014
Posted Tuesday, June 21, 2011 9:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:32 AM
Points: 2,543, Visits: 3,779
Excel has its place. However, it can be misused.

I have been on the receiving end of Excel-based databases. As they become bigger, the data is harder to handle and can impede data accuracy and integrity.

I was told once in the 1990's by the president of a company they didn't need a formal database because he could run his business on Excel. Before he was the president, the company had been running an AS400 with its embedded database. He pulled the AS400 in favor of his spreadsheets to save money.It didn't take long before the orders were wrong, the shipments were wrong, and customers were unhappy.

Excel is nice but you can't run but the very smallest organizations on it.

Oops, sorry. The editorial is about Excel in the cloud... Are we certain that running applications in the cloud won't bring more thunderstorms than pleasant Spring days? I still hesitate doing mission critical stuff in the cloud.
Post #1129080
Posted Tuesday, June 21, 2011 9:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 1,644, Visits: 4,684
OCTom (6/21/2011)
Excel has its place. However, it can be misused.

I have been on the receiving end of Excel-based databases. As they become bigger, the data is harder to handle and can impede data accuracy and integrity.

I was told once in the 1990's by the president of a company they didn't need a formal database because he could run his business on Excel. Before he was the president, the company had been running an AS400 with its embedded database. He pulled the AS400 in favor of his spreadsheets to save money.It didn't take long before the orders were wrong, the shipments were wrong, and customers were unhappy.

Excel is nice but you can't run but the very smallest organizations on it.

Oops, sorry. The editorial is about Excel in the cloud... Are we certain that running applications in the cloud won't bring more thunderstorms than pleasant Spring days? I still hesitate doing mission critical stuff in the cloud.

I can totally manage my household expenses or neighborhood association budget in Excel. There are not that many medium sized or larger organizations in 2011 (or 2001) who would dump their database server in favor of Excel, however, there was this awkward period back in the early 90's when corporate IT departments were transitioning from mainframe / terminal based systems to PCs, when I could see that sort of thing happening.
I think what often times happens with Excel, even today, is scope creep. Someone in accounting or a production line manager starts a spreadsheet to fill some narrow purpose within their department, and then it evolves over time become intangled with the company's daily operations or accounting. There may be a dozen or more Excel "data marts" in a large organization.
Post #1129093
Posted Tuesday, June 21, 2011 10:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:18 PM
Points: 33,155, Visits: 15,289
OCTom (6/21/2011)

Oops, sorry. The editorial is about Excel in the cloud... Are we certain that running applications in the cloud won't bring more thunderstorms than pleasant Spring days? I still hesitate doing mission critical stuff in the cloud.


I wouldn't want to store mission critical data in the clouds, or even important data, but putting a copy up there for analysis in the short term might be nice. As long as you have a local copy







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1129117
Posted Tuesday, June 21, 2011 7:19 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
I love Excel! I wrote my first GUI "app" as a bunch of macro buttons and learned alot about VB from reading and manipulating the resulting code of the created macros. Yeah that was 1997 and I probably wouldn't do that today, although someone else new to IT might :)

I use Excel regularly when I want to do simple things that would be harder to do in SQL server, things that require analyzing data one time, etc.

Long live Excel!


Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #1129411
Posted Tuesday, June 21, 2011 9:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 29, 2013 5:19 PM
Points: 29, Visits: 106
It seems that no matter what BI tool you have the first thing any user does is put the data into Excel so they manipulate it

If you have a well set up data structure having your users link straight in through Excel saves a lot of cost in 3rd party products
Post #1129429
Posted Saturday, June 25, 2011 11:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 9:27 AM
Points: 266, Visits: 161
Excel rocks for data analysis, but it does have some irritating quirks, particularly when working with csv files. I frequently receive customer data in csv format that contains leading zeros (think account numbers, personal identification numbers, etc.). Without fail, Excel drops the leading zeros when opening a csv file. My advice to Excel programmers is to present all csv data with text formatting on every cell.
Post #1131732
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse