[sql] Using SQL LOADER in Oracle to import CSV file

I'm pretty new to databases and programming. I'm not very good with the computer lingo so stick with me. I have a csv file that I'm trying to load into my Oracle database. It contains account information such as name, telephone number, service dates etc. I've installed Oracle 11g Release 2. This is what I've done so far step by step..

1) Ran SQL Loader

I created a new table with the columns that I needed. For example

create table Billing ( TAP_ID char(10), ACCT_NUM char(10), MR_ID char(10), HOUSE_NUM char(10), STREET char(30), NAME char(50)

2) It prompted me that the Table was created. Next I created a control file for the data in notepad which was located in the same directory as my Billing table and has a .ctl extension. GIS.csv is the file im getting the data from and is also in the same directory and named it Billing.ctl, which looked like so..

load data
infile GIS.csv
into table Billing
fields terminated by ','
(TAP_ID, ACCT_NUM, MR_ID, HOUSE_NUM, STREET, NAME)

3) Run sqlldr from command line to use the control file

sqlldr myusername/mypassword  Billing.ctl

This is where I am stuck. Ive seen video tutorials of exactly what I'm doing but I get this error:

SQL*Loader-522: lfiopn failed for file (Billing.log)

Any ideas on what I could be doing wrong here?

Update

I just moved the files into a separate directory and I suppose I got past the previous error. By the way yes Billing.ctl and GIS.csv are in the same directory.

But now I have another error:

'SQL*Loader-350: Syntax error at line 1.

Expecting keyword LOAD, found "SERV TAP ID". "SERV TAP ID","ACCT NUMBER","MTR ID","SERV HOUSE","SERV STREET","SERV ^'

I dont understand why its coming up with that error. My billing.ctl has a load.

LOAD data
infile GIS.csv
into table Billing
fields terminated by ','
(TAP_ID, ACCT_NUM, MTR_ID, SERV_HOUSE, SERV_STREET, SERV_TOWN, BIL_NAME, MTR_DATE_SET, BIL_PHONE, MTR_SIZE, BILL_CYCLE, MTR_RMT_ID)

Any thoughts?

-- Step 1: Create temp table. create table Billing ( TAP_ID char(10), ACCT_NUM char(10));

SELECT * FROM BILLING;

-- Step 2: Create Control file.

load data infile IN_DATA.txt into table Billing fields terminated by ',' (TAP_ID, ACCT_NUM)

-- Step 3: Create input data file. IN_DATA.txt file content: 100,15678966

-- Step 4: Execute command from run: .. client\bin>sqlldr username@db-sis__id/password control='Billing.ctl'

I hade a csv file named FAR_T_SNSA.csv that i wanted to import in oracle database directly. For this i have done the following steps and it worked absolutely fine. Here are the steps that u vand follow out:

HOW TO IMPORT CSV FILE IN ORACLE DATABASE ?

1. Get a .csv format file that is to be imported in oracle database. Here this is named “FAR_T_SNSA.csv”

2. Create a table in sql with same column name as there were in .csv file. create table Billing ( iocl_id char(10), iocl_consumer_id char(10));

3. Create a Control file that contains sql*loder script. In notepad type the script as below and save this with .ctl extension, in selecting file type as All Types(*). Here control file is named as Billing. And the Script is as Follows:

   LOAD DATA
   INFILE 'D:FAR_T_SNSA.csv'
   INSERT INTO TABLE Billing
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   TRAILING NULLCOLS
   (
   iocl_id,
   iocl_consumer_id
   )
   

4. Now in Command prompt run command:

   Sqlldr UserId/Password
   Control = “ControlFileName” 
   --------------------------------
   Here ControlFileName is Billing.
   

If your text is: Joe said, "Fred was here with his "Wife"".
This is saved in a CSV as:
"Joe said, ""Fred was here with his ""Wife""""."
(Rule is double quotes go around the whole field, and double quotes are converted to two double quotes). So a simple Optionally Enclosed By clause is needed but not sufficient. CSVs are tough due to this rule. You can sometimes use a Replace clause in the loader for that field but depending on your data this may not be enough. Often pre-processing of a CSV is needed to load in Oracle. Or save it as an XLS and use Oracle SQL Developer app to import to the table - great for one-time work, not so good for scripting.

LOAD DATA INFILE 'D:\CertificationInputFile.csv' INTO TABLE CERT_EXCLUSION_LIST FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' ( CERTIFICATIONNAME, CERTIFICATIONVERSION )

Try this

load data infile 'datafile location' into table schema.tablename fields terminated by ',' optionally enclosed by '|' (field1,field2,field3....)

In command prompt:

sqlldr system@databasename/password control='control file location'

Sqlldr wants to write a log file in the same directory where the control file is. But obviously it can't. It probably doesn't have the required permission.

If you're on Linux or Unix, try to run the following command the same way you run sqldr:

touch Billing.log

It will show whether you have the permissions.

Update

The proper command line is:

sqlldr myusername/mypassword control=Billing.ctl

You need to designate the logfile name when calling the sql loader.

sqlldr myusername/mypassword control=Billing.ctl log=Billing.log

I was running into this problem when I was calling sql loader from inside python. The following article captures all the parameters you can designate when calling sql loader http://docs.oracle.com/cd/A97630_01/server.920/a96652/ch04.htm

"Line 1" - maybe something about windows vs unix newlines? (as i saw windows 7 mentioned above).