Oracle SQL Loader control file delimiter

Question

I want to import a table of customer information to my database. The structure of the data file (plain text) is briefly shown below.

"Cust_ID    Cust_Name   Level"
"0001   Eric Cartman    05"
"0002   Kyle Broflovski 02"

Each line enclosed by " is a record, and the delimiter is a tab. So in my SQL Loader control file, I'll write something like this:

FIELDS TERMINATED BY '  '
    (Cust_ID, --?????
     Cust_Name,
     Level TERMINATED BY '"' )

I'd like to tell SQL loader to ignore the " before each Cust_ID. What should I add here? Thank you.

I suggest loading it into a staging table first, complete with quotes. Then you can get rid of them with update queries. — Dan Bracuk, Oct 05 '13 at 23:13
@DanBracuk Thank you for your comment. If this were a real-life case instead of homework, I would definitely do as you said. But since it IS homework and the requirement is just loading it into a staging table, I guess I should find some way to make the staging table look beautiful :( — goldfrapp04, Oct 05 '13 at 23:18
Do you know how to get rid of the quotes once the data is in the staging table? — Dan Bracuk, Oct 06 '13 at 03:06

score 1 · Accepted Answer · answered Oct 06 '13 at 08:56

You can use a filler field to ignore the double-quote at the start of the record. This defines a filler field I've nominally called quote which is terminated by the first ":

options (skip=1)
load data
truncate into table t42
fields terminated by '  '
(
    quote filler terminated by '"',
    cust_id,
    cust_name,
    cust_level terminated by '"'
)

The value of that quote field would be null in this case, but we're ignoring it anyway so that's not important.

With your data file as a table defined as:

create table t42(cust_id number, cust_name varchar2(20), cust_level number);

... your data is inserted as:

select * from t42;

   CUST_ID CUST_NAME            CUST_LEVEL
---------- -------------------- ----------
         1 Eric Cartman                  5
         2 Kyle Broflovski               2

I've called the third column cust_level because level is a reserved word; you can force it to be used but it would cause you pain down the line. Also note I've used truncate so this will replace any existing data in your table; if you're adding new data then change that to append.

Oracle SQL Loader control file delimiter

1 Answers1