Search notes:
Oracle: SQL Loader Example: Load CSV with a header and data that contains commas
The following example tries to load the following
CSV file:
id,txt,num,dt
1,"foo,bar,baz",44.22,21.02.2014
2,"one,two,three",31.28,06.06.2014
3,bla bla,-19.2,28.08.2014
Two features of the CSV file are its header and that it contains data with commas which are embedded in quotes ("foo,bar,baz" and "one,two,three").
In order to load the data, we need to create a destination
table:
create table tq84_sql_loader_8 (
id number primary key,
txt varchar2(20),
num number,
dt date
);
The control file to load this data looks as follows.
SQL loader is instructed to skip the first line (the header) with options(skip=1).
Strings within in quotes are recognized with the optionally enclosed by '"' instruction.
options(skip=1)
load data
infile 'load_8.csv'
insert
into table tq84_sql_loader_8
fields
terminated by ','
optionally enclosed by '"'
(
id,
txt,
num "to_number(:num, '99999999999D9999', 'nls_numeric_characters=''.,''')",
dt "to_date(:dt, 'dd.mm.yyyy')"
)
Within SQL*Plus, the data can be loaded with this SQL script:
start create_table
host sqlldr control=load_8.ctl userid=rene
select * from tq84_sql_loader_8;
drop table tq84_sql_loader_8;
prompt
prompt Check load_8.bad file
prompt