Q

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.?

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

Dig deeper on Oracle database export, import and migration

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close