Importing FoxPro and Access databases into Oracle

How can we import FoxPro and Microsoft Access databases into Oracle? And how can we export an Oracle table to Foxpro and Microsoft Access databases?

    Requires Free Membership to View

    Login

The key to both of your scenarios lies in using an intermediary format. With FoxPro, you should be able to export a table's contents to a comma delimited file (or some other delimiter). To export from MS Access to a comma delimited file, select the table, then choose File->Export. Once you have it in this database-independent format, you can then load it into Oracle. Oracle's tool for loading text files into Oracle tables is SQL*Loader.

To go the other way, you need to get the data out of Oracle in comma delimited format. You can use the DBMS_UTILITY.TABLE_TO_COMMA supplied package for this exact task. Or, you can write a SQL statement similar to the following:

SELECT empno||',"'||ename||'",'||deptno FROM emp;

In the above SQL statement, I use the concatenation operator to make the output from multiple columns appear as one field. I also enclosed the ENAME value in double quotes. The SPOOL command can be used to write the output to a file.

Once you have the comma delimited file, you will need to use your database's utilities to load it into a table. I'm not sure how you do this in FoxPro. In MS Access, choose File->Get External Data->Import. Then select your file. A wizard will run you through the import process.

Another intermediary format to use is a program like Perl that can pull from one data source and push into another. Perl accomplishes this by connecting to both data sources through ODBC connections. MS Access can utilize an ODBC connection to an Oracle database without any middle man.


This was first published in September 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top