Using SQL LOADER in Oracle to import a CSV file

Question

I am new to databases and programming. I'm not very good at computer jargon, so hold on to me. I have a csv file that I am trying to upload to my Oracle database. It contains account information such as name, phone number, service dates, etc. I installed Oracle 11g Release 2 . This is what I have done so far step by step.

1) Ran SQL Loader

I created a new table with the columns that I need. for instance

 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) This prompted me that the table was created. Then I created a control file for the data in notepad, which was located in the same directory as my billing table, and has the extension .ctl. GIS.csv is an im file that receives data, and is also in the same directory and named it Billing.ctl, which looked like this.

 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 the command line to use the control file

 sqlldr myusername/mypassword Billing.ctl 

This is where I am stuck. I saw video tutorials about what I am doing, but getting this error:

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

Any ideas on what I can do wrong here?

Update

I just moved the files to a separate directory, and I believe that the previous error has passed. By the way, yes Billing.ctl and GIS.csv are in the same directory.

But now I have one more error:

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

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

I do not understand why this is happening with this 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?

Answer 1

Sqlldr wants to write the log file in the same directory as the management file. But obviously this is not possible. He probably does not have the necessary permission.

If you are running Linux or Unix, try running the following command the same way you run sqldr:

 touch Billing.log 

It will show if you have permissions.

Update

The correct command line is:

 sqlldr myusername/mypassword control=Billing.ctl 

Answer 2

I had a csv file named FAR_T_SNSA.csv that I wanted to import directly into the oracle database. To do this, I took the following steps, and it worked absolutely fine. Here are the steps you can follow:

HOW TO IMPORT A CSV FILE IN ORACLE BASES?

• Get the .csv format file to be imported into the oracle database. Here it is called "FAR_T_SNSA.csv"

• Create a table in sql with the same column name as in the CSV file. create table Billing (iocl_id char (10), iocl_consumer_id char (10));

• Create a control file containing sql * loder script. In notepad, enter the script as shown below and save it with the .ctl extension, selecting the file type as "All types" (*). Here the control file is called Billing. And the script matches the following:

   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 ) 

• Now at the command prompt, launch the command:

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

Answer 3

When invoking the sql loader, you must specify the log file name.

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

I ran into this problem when I called the sql loader from inside python. The following article lists all the parameters that you can assign when calling sql loader http://docs.oracle.com/cd/A97630_01/server.920/a96652/ch04.htm

Answer 4

try it

load infile data 'datafile location' into the schema.tablename table fields completed with ',' optionally enclosed in '|' (Field1, field2, field3 ....)

At the command line:

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

Answer 5

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

Answer 6

If your text is: Joe said, "Fred was here with his Wife."
This is saved in CSV as:
"Joe said," Fred was here with his "wife." "
(A rule is double quotes around the entire field, and double quotes are converted to two double quotes). Thus, just an optional Enclosed By condition is required, but not enough. Because of this, CSV rules are tough. Sometimes you can use the Replace clause in the loader for this field, but depending on your data this may not be enough. Often, CSV preprocessing is required to load into Oracle. Or save it as XLS and use the Oracle SQL Developer application to import into the table - great for one-time use, not so good for scripting.

Answer 7

LOADED DATA INFILE 'D: \ CertificationInputFile.csv' INTO TABLE CERT_EXCLUSION_LIST FIELDS TESTED BY "|" ADDITIONALLY INCLUDED '' '' (CERTIFICATION, CERTIFICATION)

Answer 8

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

 SELECT * FROM BILLING; 

- Step 2: Create a management file.

Loading data infile IN_DATA.txt into the table Billing fields interrupted by ',' (TAP_ID, ACCT_NUM)

- Step 3: Create an input file. The contents of the IN_DATA.txt file: 100.15678966

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