Q

Importing data from text file to table in 9i

I have to import data from a text file to a table in Oracle9i. Could you please explain in steps how to mount the...

file and how to import data into the table? Oracle uses a tool called SQL*Loader to load a text file into an Oracle table. First, you'll have to create a table in the database. Then, you'll have to create a control file telling SQL*Loader how to map the data in the text file to the table. A simple control file might look as follows:

LOAD DATA
INFILE 'ITS_data.txt'
BADFILE 'ITS_data.bad'
DISCARDFILE 'ITS_data.dsc'
APPEND
INTO TABLE pims_temp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(wbs_parent,wbs,ffs_account,ffs_account_desc,ffs_subtask_desc,
rescode,resource_name,resdept,type,hrs_dlrs_ind,)

The information above would be saved in a text file. It basically says that the input file is "ITS_data.txt". All records that are "bad" are placed into the bad file and discarded records into the discard file. The data will be appended into table PIMS_TEMP. The data in the text file is a comma-delimited file with each field optionally enclosed in double quotes. The fields in the text file then map to the columns in the table in the order shown. Then SQL*Loader is invoked as follows:

sqlldr userid=system/manager control=file.ctl

where "file.ctl" is the text control file similar to above.

There are many more options for SQL*Loader that you can find fully documented in the Oracle Utilities Guide. Please refer to this document for more information.

This was first published in June 2004

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close