Hawaiʻi's Technology Community
Excel workbook to database and back
I have a project where we have to compile multiple (30+) copies of an excel workbook into essentially one master workbook. The data is then pushed out again. And summary reports are written. Each workbook has about 20 sheets in it. The column names on each page are often vague.
We've thought about a database with a table representing each sheet. Uploading from a workbook is tricky (they don't want to export each sheet to csv and then upload them individually), or so it seems. I'm not a VB programmer (hence the post here).
I thought about turning the workbook into an Adobe Acrobat form (20 pages or so). It works in test, but making the form is tedious work and there's no export capability back to a worksheet (although perhaps that isn't absolutely necessary).
Google answers have ODBC plugins and Navicat imports, neither of which truly resolve my question.
I'm just trolling around for new ideas, past experiences, war stories.
Ken
We've thought about a database with a table representing each sheet. Uploading from a workbook is tricky (they don't want to export each sheet to csv and then upload them individually), or so it seems. I'm not a VB programmer (hence the post here).
I thought about turning the workbook into an Adobe Acrobat form (20 pages or so). It works in test, but making the form is tedious work and there's no export capability back to a worksheet (although perhaps that isn't absolutely necessary).
Google answers have ODBC plugins and Navicat imports, neither of which truly resolve my question.
I'm just trolling around for new ideas, past experiences, war stories.
Ken
Views: 53
Replies to This Discussion
-
Permalink Reply by Brian Kirsch on
-
Hi Ken,
I would look into using OLE automation to read and write the values out the Excel files.
It is pretty trivial to get the same type of functionality VB guys have with Excel through a package such as win32OLE for Ruby.
http://homepage1.nifty.com/markey/ruby/win32ole/index_e.html
You could then handle all the csv import and export of the Workbook sheets using a little bit of Ruby hacking.
Hope that helps,
Brian -
-
-
Forgot to mention that if you have Ruby 1.8 or greater (and why wouldn't you) on Windows, win32ole should already be available ie.
require "win32ole" -
-
Permalink Reply by Laurence A. Lee on
-
Been there, done that; I feel your pain. :-(
If you want to build and keep your solution in-house, here's what I've done in the past: Save the spreadsheets in Office 2003 format, and parse out the XML. As long as the spreadsheets are in a standard format, you can glean quite a lot of information.
You could also create and include a VBA module in each spreadsheet to "scrape" the data on your worksheets and write out another file in whatever format you need.
Be careful if you go down the ruby/win32ole path -- OLE object/memory leaks are very easy to do, and can funk up your server enough to require a reboot.
Of course, other options exist. Some techniques are guarded as trade secrets, and others are made available in commercial products. -
© 2024 Created by Daniel Leuck.
Powered by
