What is the best route to import data from "Lotus 123 97" workbooks into Oracle 8.0.4?
- There are about 66 workbooks (Lotus 123 97), each with more than 10 spreadsheets.
- Each worksheet is arranged differently within one workbook.
- The only plus point is (with some doubt) each spreadsheet arranged identically from workbook to workbook. (Some QA will be needed though.)
- Having ODBC drive for Excel, have converted Lotus to Excel (which gives somes errors on label conversions exceeding 255 chars - but acceptable for now.)
- Using the data transformation service of MS-SQL server -- transferred one worksheet fine... and it appears that if I create seperate DTS for each type of worksheet (1 DTS/worksheet which can be used 66 times for each workbook), it would work just fine; but having all worksheets arranged differently, looking for any other easier way...
- Please do not advise to buy a sophisticated ETL tool, since this is only one time task and would not justify buying a new tool.
Likely, the best way to pull the Lotus data in would be to save the workbooks (one sheet at a time) to CSV (comma separated) or tab delimited text files. Once you have the data in that format, you can use SQL*Loader to "import" the data.
In Oracle, you'd first need to create the tables with columns that match the workbooks you wish to bring into Oracle. Then after you've saved the workbooks as CSV or TXT files, you can have SQL*Loader do the work of loading the data into the tables. If you need assistance with how to use SQL*Loader, see the Oracle8 Utilities document.
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in April 2002