Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Excel in the Clouds


Excel in the Clouds

Author
Message
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40785 Visits: 18861
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
My Blog: www.voiceofthedba.com
eric.fetterolf
eric.fetterolf
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 27
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.
Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1191 Visits: 3661
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
Eric M Russell
Eric M Russell
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6194 Visits: 10034
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


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
OCTom
OCTom
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2855 Visits: 4152
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.
Eric M Russell
Eric M Russell
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6194 Visits: 10034
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40785 Visits: 18861
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 w00t

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Peter Trast
Peter Trast
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 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 Smile

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! :-D

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
nigelc
nigelc
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 108
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
gorthog
gorthog
SSC Veteran
SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)

Group: General Forum Members
Points: 282 Visits: 167
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search