Importing CSV data into Oracle

Loading data into Oracle table requires the use of the sqlldr executable. To load data I create two files, one a CSV file containing the data to import and a control (.ctl) file containing the command information to pass to sqlldr.exe.

The Control File

load data
infile 'addresses.csv'
 
INTO TABLE myschema.mytable fields terminated BY "," 
         optionally enclosed BY '"'
(
    NAME
    ,TELEPHONE
    ,ADDRESS
    ,LAST_UPDT DATE 'dd-Mon-yyyy HH:MI:SS PM'
)

Notice that I’ve put a format on the date, I’ve had problems with dates if I don’t give the format for the date when importing. Save this file locally as addresses.ctl

The Data File

"Matt","3094785","55 My Street","25-Oct-07"
"Ricardo","354102","12 Easy Road","10-Mar-06"
........

Save this file as addresses.csv as declared in the control file. The file should be sve in the same directory as the control file.

To import the data, open a command prompt window and type the following:

c:\path\to\oracle\bin\sqlldr  username@oracleservername 
                        control=\path\to\addresses.ctl

That should import all the data into oracle. A relevant log file will be created as well as a bad file for rows that could not be imported. I tend to create a batch file to execute so I don’t have to remember the syntax for sqlldr.

If things go wrong

As stated above, I’ve had problems with date formatting before when importing. I’ve also come across issues with field terminations. By default CSV exports tend to be terminated by a comma (,) but I tend to use a semi colon (;) I’ve had less problems using this method.