Ask the Expert

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.

    Requires Free Membership to View

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

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: