ignore space in column value while loading data using sql loader by oracle

Question

I am trying to load data using oracle sql loader and using space as separator of columns but I am facing problem that one of the columns value including space, I need your support to avoid considering this space as column separator.

I tried to use regexp_replace and replace functions

DSTCOUNTRY " REGEXP_REPLACE(:DSTCOUNTRY,'dstcountry=','')",

the column value is: dstcountry="United States"

and the expecting value to be stored in the table is: United States

The sql loader command is: load data infile 'in' append into table test_table fields terminated by " " optionally enclosed by '"' TRAILING NULLCOLS DSTCOUNTRY " REPLACE(:DSTCOUNTRY,'dstcountry=','')",

I am using oracle 10G and 12C.

Can you please share the SQL loader command or parameter file which you have used to load the data? - Edit the question and add the details. — Popeye
– Popeye, Commented Jul 22, 2019 at 13:55
You may need to use the Oracle DataPump utility which has more flexibility on input data. Can you also edit your question and add a tag for your Oracle version, such as Oracle12c — Mark Stewart
– Mark Stewart, Commented Jul 22, 2019 at 15:28

Littlefoot · Accepted Answer · 2019-07-22 16:01:12Z

1

According to what you posted so far, it is optionally enclosed you're looking for. Here's an example.

Test table:

SQL> create table test (id number, dstcountry varchar2(20));

Table created.

Control file (contains sample data as well):

load data 
infile *
replace
into table test
fields terminated by " " 
optionally enclosed by '"'
trailing nullcols
(
id,
dstcountry)

begindata
123 "Croatia"
125 "United States"

Loading session and result:

SQL> $sqlldr scott/tiger control=test08.ctl log=test08.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pon Srp 22 17:59:23 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2

SQL> select * from test;

        ID DSTCOUNTRY
---------- --------------------
       123 Croatia
       125 United States

SQL>

answered Jul 22, 2019 at 16:01

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Rami Over a year ago

Hello,Thanks for your answer but according to this the loader will consider dstcountry="United States" 2 columns since there is space between United and States?

Littlefoot Over a year ago

You're welcome. Didn't you see the example? Was "United States" loaded into two columns? No, it was not.

Rami Over a year ago

but I have the data as the following: dstcountry="United States" so I have to use the function as the following: DSTCOUNTRY " REGEXP_REPLACE(:DSTCOUNTRY,'dstcountry=','')",

Rami Over a year ago

I am using the function REGEXP_REPLACE to remove the part dstcountry= from the data.

Littlefoot Over a year ago

Aha. Only the country name is enclosed into double quotes (e.g. dstcountry="United States"), not the whole column value (e.g. "dstcountry=United States"). Replace removes dstcountry=, but SQL Loader doesn't take the remaining string as the "new" input, but uses the space as a field terminator and loads only the first "word" - United. I'm not sure you can fix that in SQL Loader. Consider pre-processing the file; either by search/replace function, or see whether you can get the file without it.

|

Collectives™ on Stack Overflow

ignore space in column value while loading data using sql loader by oracle

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related