Excel in the Clouds

  • Comments posted to this topic are about the item Excel in the Clouds

  • 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.

  • 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

  • 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

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • 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.

  • 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.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • 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:

  • 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

  • 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

  • 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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply