Q

Insert date from a .csv file into an Oracle table

I want to insert date from my .csv file into an Oracle table. Please tell me how to do that by using the SQL*Loader. I'd also like to know how to create control files, and SQL*Loader command syntax for the same.
The Oracle documentation is a really good place for getting information on SQL*Loader and its control file. Particularly, Chapter 8 of the Oracle 10g Utilities Guide is the SQL*Loader Control File Reference section. Chapter 12 of this document lists many examples as well.

The contents of the control file will depend on the structure of the data you are loading. Here is an example of a control file that loads data from a comma-delimited file called "myfile.csv".

LOAD DATA
INFILE "myfile.csv"
APPEND INTO TABLE emp
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
hiredate DATE(20) "DD-Month-YYYY",
sal, comm, deptno, projno)

In the comma-delimited file, the value in the field for the HIREDATE column is in a specific date format that may differ from our database's default date format. This is not a problem as we can change the format in our control file. Notice that the HIREDATE column in the control file sample above contains the date format "DD-Month-YYYY". You can use any valid date format to match your requirements.

This was first published in April 2005

Dig deeper on Oracle database backup and recovery

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close