Home » RDBMS Server » Server Utilities » SQL loader - import is having issue in new line \n (SQL LOADER , )
SQL loader - import is having issue in new line \n [message #548801] Mon, 26 March 2012 06:19 Go to next message
rohitmathur11@gmail.com
Messages: 4
Registered: March 2012
Location: INDIA
Junior Member
Hi,
I am working on 10.2.0.3 database.
The requirement is to insert data from DAT file in to table.
I am using following file to insert data as in date2.csv file.

Data2.ctl

LOAD DATA
INFILE '/home/oracle/data2.csv'
CONTINUEIF LAST != "$"
INTO TABLE emp
APPEND
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS
(ename,eadd,emob)


Data2.csv

Jack ADD
RE
SS1 9740$
Mike ADD
RE
SS2 0456$


Command to execute sql loader -

sqlldr userid=mtest/mtest@rp10g control=data2.ctl log=data2.log


The current control file is inserting data like following -

ename eadd     emob 
Jack ADDRESS1 9740
Mike ADDRESS2 0456 


But now requrement is like following to insert data in to table ... \n (newline) should be visiable in column data..
As there is a new line in CSV file also in ename filed ....

Like

ename    eadd      emob 
Jack ADD\nRE\nSS1 9740
Mike ADD\nRE\nSS2 0456 


there are only 2 rows in table in this case ...

Any idea how to do this ....


[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Mon, 26 March 2012 13:50] by Moderator

Report message to a moderator

Re: SQL loader - import is having issue in new line \n [message #548866 is a reply to message #548801] Mon, 26 March 2012 14:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Instead of using CONTINUEIF to check for $, you can specify $ as the record terminator. This will result in concatenation of the last linefeed to the beginning of the first field of the next record, so that needs to be trimmed. Please see the suggested revised control file below.

LOAD DATA
INFILE '/home/oracle/data2.csv' "str'$'"
INTO TABLE emp
APPEND
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS
(ename "LTRIM(LTRIM(:ename, CHR(13)),CHR(10))", eadd, emob)

Re: SQL loader - import is having issue in new line \n [message #548984 is a reply to message #548866] Tue, 27 March 2012 07:04 Go to previous messageGo to next message
rohitmathur11@gmail.com
Messages: 4
Registered: March 2012
Location: INDIA
Junior Member
Thanks for providing solution..
I need one more help from your side..
Actually now our client want to use following control file
I will do modification according you suggested.
But one change in this that is...client want to remove INFILE
clause from control file ,they want to specify DATA clause while
executing sqlldr command to provide data file path.
Please can u suggest if we remove INFILE claue from control file then where to put "str '| \n'" clasue..?

Control file --

LOAD DATA
INFILE '/home/oracle/sqlldr_test/data2.csv' "str '| \n'"
CONTINUEIF LAST != "$"
INTO TABLE emp
APPEND
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS
(ename,
edd ,
emob)
Re: SQL loader - import is having issue in new line \n [message #549042 is a reply to message #548984] Tue, 27 March 2012 12:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Just replace the filename in the control file with an asterisk:

INFILE * "str '$'"

Then specify the filename in the command line:

sqlldr userid=mtest/mtest@rp10g control=data2.ctl log=data2.log data='/home/oracle/sqlldr_test/data2.csv'


Re: SQL loader - import is having issue in new line \n [message #549156 is a reply to message #549042] Wed, 28 March 2012 06:14 Go to previous messageGo to next message
rohitmathur11@gmail.com
Messages: 4
Registered: March 2012
Location: INDIA
Junior Member
thanks
Re: SQL loader - import is having issue in new line \n [message #549157 is a reply to message #549042] Wed, 28 March 2012 06:15 Go to previous message
rohitmathur11@gmail.com
Messages: 4
Registered: March 2012
Location: INDIA
Junior Member
thanks for your help ,it helped me lot
Previous Topic: SQL loader
Next Topic: sqlldr - datatype
Goto Forum:
  


Current Time: Fri Jan 17 18:50:47 CST 2020