OraFAQ Forum: Server Utilities » TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB

Home » RDBMS Server » Server Utilities » TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB (ORACLE 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620232]

Wed, 30 July 2014 07:23

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

following is the code is which i have written inside .ctl file

OPTIONS (SKIP=1,ERRORS=999999)
load data
infile 'D:\FILEUPLOAD\NEW_Q4_DATA.csv'
append
into table TEMP_VCON_CDR_DTL
fields terminated by "\t" optionally enclosed by '"' TRAILING NULLCOLS (MSISDN_NO,CIRCLE,REC_TYPE,CALLING_NO,CALLED_NO,DATE_TIME,DURATION)

following is the sample data which i am trying to insert using sqlldr

918264068	GJ	GPRS	9182640683	 	1/18/2014 1:50:01 AM	4392.0000

there is a tab delimeter after 9182640683 and then space and after that one more tab delimeter.
but when i check data in table it shifts values of EVENT_DURATION in CALL_DATE_TIME column
it considers two tab spaces as one which is wrong .

the actual data that sqlldr inserts in table:-

MSISDN_NO  CIRCLE  REC_TYPE  CALLING_NO   CALLED_NO               DATE_TIME   DURATION
918264068   GJ	    GPRS     9182640683	 1/18/2014 1:50:01 AM	  4392.0000

but the desired output is

MSISDN_NO  CIRCLE  REC_TYPE  CALLING_NO   CALLED_NO   DATE_TIME             DURATION
918264068   GJ	    GPRS     9182640683	             1/18/2014 1:50:01 AM   4392.0000

Please tell how to solve this problem.

Thanks

Report message to a moderator

Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620234 is a reply to message #620232]

Wed, 30 July 2014 08:05

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Try X'09' instead of "\r".

Report message to a moderator

Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620291 is a reply to message #620234]

Thu, 31 July 2014 00:52

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

i tried , that is also not working

Report message to a moderator

Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620307 is a reply to message #620291]

Thu, 31 July 2014 02:17

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Thu, 31 July 2014 11:22

that is also not working

That is not going to help anyway. Show us using copy paste what you tried. Nobody can fix something which cannot be seen.

Report message to a moderator

Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620318 is a reply to message #620307]

Thu, 31 July 2014 02:55

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Also give the precise DB and sqlloader version, it might be a bug in your version.

Report message to a moderator

Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620322 is a reply to message #620232]

Thu, 31 July 2014 03:27

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

yeah both X'9' and X'09' are working but i had to remove

optionally enclosed by '"' TRAILING NULLCOLS

statements from the control file.
please tell me the reason why it was not working along with " optionally enclosed by '"' TRAILING NULLCOLS "

one more question , as i know we can use X'9'or X'09' etc for tab delimeter , do we have similiar things for comma delimeter also. ?

[Updated on: Thu, 31 July 2014 03:28]

Report message to a moderator

Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620332 is a reply to message #620322]

Thu, 31 July 2014 05:34

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Thu, 31 July 2014 13:57

as i know we can use X'9'or X'09' etc for tab delimeter , do we have similiar things for comma delimeter also. ?

FIELDS TERMINATED BY ","

You can read some useful stuff in our OraFAQ FAQ SQL*Loader

Edit : typo

[Updated on: Thu, 31 July 2014 05:34]

Report message to a moderator

Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620348 is a reply to message #620322]

Thu, 31 July 2014 09:06

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

jgjeetu wrote on Thu, 31 July 2014 01:27

...please tell me the reason why it was not working along with " optionally enclosed by '"' TRAILING NULLCOLS "...

It is a known bug that double tabs without quotes are not recognized unless you remove the optionally enclosed by. I believe you can keep the trailing nullcols. The following is an example of a thread where I first encountered the issue a long time ago.

http://www.orafaq.com/forum/t/26197/0/

Additional info, according to Laurent Schneider on December 14, 2012:

This is bug 21040, reported in 5.1.22 and closed Jun 7, 1989 as 92 Closed, Not a Bug

The workaround is documented in note 414394.1 :
Remove clause OPTIONALLY ENCLOSED BY from control file

source: http://laurentschneider.com/wordpress/2012/10/ora-01722-invalid-number-and-sql-loader.html

[Updated on: Thu, 31 July 2014 09:15]

Report message to a moderator

Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620400 is a reply to message #620332]

Thu, 31 July 2014 23:54

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

Quote:

FIELDS TERMINATED BY ","

@lalit that i know anyways thanks for the link , i said something similiar like X'9' , normally we use "\t","tab"or
" " for tab delimeter, and "," for comma delimeter.
but if i use

FIELDS TERMINATED BY ","

i will have to use

optionally enclosed by '"' TRAILING NULLCOLS

along with this, so i was asking for alternative , as we have for tab delimeter.

[Updated on: Fri, 01 August 2014 01:30]

Report message to a moderator

Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620410 is a reply to message #620348]

Fri, 01 August 2014 01:32

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

@barbara thanks for the info. Smile

Report message to a moderator

Previous Topic:	Consistent backup using data pump
Next Topic:	Expdp taking more time to dump the file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:23:33 CST 2025