Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: