CODEWITHSUNDEEP
sqlcsvoracle11gdatabase-connection
Mark Nelson
Mark Nelson

Reputation: 103

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?

Upvotes: 8

Views: 204749

Answers (9)

Hassan Abdelrahman
Hassan Abdelrahman

Reputation: 57

you can use the following control file and make sure of the permission required to allow sql*loader to write two files within the same directory (results.log & bad file).

options  ( 
skip=1,
PARALLEL=true,
DIRECT=true,
MULTITHREADING=false
)
load data
infile              'dataset.csv'           
APPEND INTO TABLE   XX_DUMMY_DATA_CSV
FIELDS TERMINATED BY ","       
optionally enclosed by '"' 
( 
   CASE_NUMBER,
   NASA_CENTER,
   SRA_DATE DATE "mm/dd/YYYY HH24:MI",
   SRA_FINAL,
   NTR_TITLE
)

Source: Import data from CSV file to oracle database table using SQL*Loader.

Upvotes: 1

VXJ8464
VXJ8464

Reputation: 51

-- 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'

Upvotes: 0

syed rizwan
syed rizwan

Reputation: 11

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'

Upvotes: 1

Mr.T
Mr.T

Reputation: 33

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

Upvotes: 0

Mot
Mot

Reputation: 1

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.

Upvotes: 0

casey_barwell
casey_barwell

Reputation: 21

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

Upvotes: 2

Piyush Mehtani
Piyush Mehtani

Reputation: 21

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.

Upvotes: 2

Franto
Franto

Reputation: 16

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

Upvotes: 0

Codo
Codo

Reputation: 79033

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

Upvotes: 4

Related Questions