Problem solve Get help with specific problems with your technologies, process and projects.

Best route to import data from

What is the best route to import data from "Lotus 123 97" workbooks into Oracle 8.0.4?

Background/pertinent info:

  • 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.)
Some preliminary tasks already done:
  1. Having ODBC drive for Excel, have converted Lotus to Excel (which gives somes errors on label conversions exceeding 255 chars - but acceptable for now.)
  2. 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...
  3. 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.

Dig Deeper on Oracle database export, import and migration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.