Ask the Expert

Uploading CSV files

In answering the question about loading data from Microsoft Excel, you indicated that CSV files could be uploaded to Oracle. Where can I find out more about this process? Which tables are involved for specific applications, what processes have to be run, etc.?

    Requires Free Membership to View

Oracle's SQL*Loader utility is used to load data from flat files into Oracle tables. You'll have to figure out how the data in the flat files maps to your table in the database. For instance, assume we have a CSV file call in_data.txt that looks like the following:

7782, "Clark", "Manager", 7839, 09-June-1981
7839, "King", "President", , 17-November-1981
7934, "Miller", "Clerk", 7782, 23-January-1982
7566, "Jones", "Manager", 7839, 02-April-1981
This data will be loaded into the SCOTT.EMP table. You can create a SQL*Loader control file called in_data.ctl that looks like the following:
LOAD DATA
INFILE in_data.txt
APPEND INTO TABLE scott.emp
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr, hiredate DATE(20) "DD-Month-YYYY")
Once you have the control file and the input file, you can load it all as follows:
sqlldr userid=scott/tiger control=in_data.ctl log=in_data.log
There are many more options for SQL*Loader in the Oracle documentation. Please read the Utilities guide.

This was first published in May 2005

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: