Re: Import Excel file data into Oracle

While there are a lot more details than I will discuss, perhaps the following overview will give you enough of the basics to decide what to chase further in the docco and on Oracle Technet . (The documents I identify are available at http://docs.oracle.com ... look at the list of docs from http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/nav/docindex.htm )

Since you are on Oracle9i Release 2, you have a lot of options open to getting informtaiton from Excel files. Since you indicated a data transfer from SQL Server ... I'm assuming an ongoing or periodic transfer and not a migration (and eventually turning off) SQL Serer .....

1. Oracle provide a utility call SQL Loader which is very flexible.

Basically it allows a flat file to be loaded into one or more tables. The flat file has defined 'columns' of data, although the columns do not have to occur in every record (especially if a 'record type' can be determined. Columns can be detected based on absolute position, relative length or delimiter. Data in a column can be interpreted, reformatted, or used to enter a completely new value as well as used to determine the table into which the data is to be loaded. Records failiing to load are placed in a 'bad' file and a runtime log file of actions is also created.

Since the rules can be stored in a parameter file, this is a great mechanism for repetative loads such as an Excel-produced .csv file.

Check the Utilities manual for details.

2) External tables can be viewed as SQL Loader flat data files that are live in the database. You identify the table to the database using the 'Create Table' command just liek a normal table, but yoiu use 'SQL Loader'-like syntax to identify the columns.

After the external table is defined (created), you can slide flat files under the definition at the operating system level and do selects and joins directly from the flat file.

There are some performance limits, some database issues (such as scalability issues, backup/recovery, etc.) and likely some hic-cups as this is relatively new. But it can be used in place of SQL Loader for environments that

Check the SQL Reference manual for syntax.

3) AFAIK, Heterogeneous Services is the replacement for the Oracle Gateways. Gateways allow a remote database to be 'linked' to an Oracle database effectively using database links. One (not accurate but useful) way to look at this is to say Heterogeneous Services puts an Oracle-shell in front of the non-Oracle database. The shell acts as an Oracle-aware proxy for the non-Oracle database, allowing that database to particpate in Oracle distributed database requests.

Over the years there have been gateways built for virtually every known database-ish mechanism including flat files and Cobol copy books ... Additional software MAY be required. Although I suspect your SQL Server interface does not require any additional stuff. (Since Excvel is not based on a flat file concept, nore doers it have a dictionary, probably no direct interchange with Excel files is available.)

There are some issues in setting this up. The easiest set-ups tend to be SQL dialects such as DB2, SQL Server, etc. And there can be a lot of restrictions when specific dialects are not recognized or operations can not be duplicated. For example, the SQL Server 'identity' capability (exactly the same as Oracle's Sequence, only a lot different <g>) or, writing to flat files, get to be problems.

This is great for longer term operations and situations where you want to avoid duplicated data, live connection to the data source, and CPU cycles are a secondary issues (you can end up chewing up CPUs doing dialect translation)

More details in the Heterogeneous Connectivity Administrator's Guide

4) One of the newer mechanisms available in SQL Server and available in Oracle since 8.1.5 is the ability to extract and input XML data. As described below, XML documents and SQL databases can be coerced to map one-to-one.

XML (eXtended Markup Language) allows you to define what a document can look like. The document will normally be plain ASCII (or readable text), and each piece of data is identified by a "the following data is of type xxx" marker and terminated by a "end of type xxx" marker. The markers and data can be nested and multiple levels can be duplicated. This nesting can effectively simulate a Table-Column structure and the duplication can simulate rows in the table.

The strengths of XML include the extensibility - a lot of utilities have been created including XSL, XSQL, and so on ... these allow direct interface to database, transforming the data based on 'style sheet' rules, etc. As a result, there is a whole industry of XML-based data interchange/integration between systems and databases that has grown in the past 3 years.

There are a number of good books on this - my personal favorite is the O'Reilly "Building Oracle XML Applications" By Steve Muench (http://www.oreilly.com/catalog/orxmlapp/) - which includes some actual interchange demo code and all the logic necessary to get there yourself.

Oracle's XML capabilities are very mature and they have a significant set of examples and code on OTN. I've found they are quick to publish code updates to conform to new releases of the standard, and most of the stuff can be downloaded easily from OTN.

More info in XML Database Developer's Guide - Oracle XML DB and XML Developer's Kits Guide - XDK

5) Go to OTN and look up 'integration' for a lot more!

Hope this helps
/Hans Received on Sat Jun 14 2003 - 11:06:55 CDT