Conversion tool for MS-Excel spreadsheets with macros and VB to Oracle?

Question

Our users have created MS-Excel spreadsheets which over time have evolved into fairly complex applications. They run their part of the business with them. But, never having been exposed to software development discipline, these spreadsheets are brittle, single point of failure, solutions.

Our development group uses Oracle primarily with Java and some other technologies. Is there a tools for conversion from MS-Excel to Oracle? At least part way so we can get a head start and not just have to reverse engineer and rewrite?

Answer 1

No there aren't any tools that will covert the formulas or logic. You will have to do that the hard way. You can get the data into Oracle by exporting it as a CSV and using SQL Loader to import it into the database.

Answer 2

You can find tools to migrate the data, but other than that you wouldn't find a tool to automatically do this, you would have to do it all manually.

Even directly copying what was there would not make sense, you were using very limited tools (Excel), you would need to re-analyze the requirements, and possibly modify them before doing anything else.

Answer 3

There are tools that could convert the data, but trying to converting the formulas would cause the design to be inefficient at best and unusable at worst. The difference between spreadsheets and an Oracle database are similar to the differences between a home gardener and a farmer. Both are useful on their level and some of the same principles apply, but the techniques employed are entirely different.

I suggest you examine the spreadsheets until you understand the goals they are trying to meet and then architect a system in Oracle that meets those goals using the best techniques available in Oracle. The processing will end up being quite different, but the product will be significantly better for it.

Answer 4

You can suck a spreadsheet into MS Access, then push it directly into Oracle as a table (or append it to an existing table). I'm sure you could write an MS Access macro to do it.

It also appears to be possible using SQL Loader from Oracle, but I've never tried that myself.