Home » RDBMS Server » Server Utilities » TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB (ORACLE 10g)
TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620232] Wed, 30 July 2014 07:23 Go to next message
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
Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620234 is a reply to message #620232] Wed, 30 July 2014 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

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

Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620291 is a reply to message #620234] Thu, 31 July 2014 00:52 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

i tried , that is also not working
Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620307 is a reply to message #620291] Thu, 31 July 2014 02:17 Go to previous messageGo to next message
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.
Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620318 is a reply to message #620307] Thu, 31 July 2014 02:55 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also give the precise DB and sqlloader version, it might be a bug in your version.
Re: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB [message #620322 is a reply to message #620232] Thu, 31 July 2014 03:27 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
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 Go to previous messageGo to next message
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 Go to previous message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@barbara thanks for the info. Smile
Previous Topic: Consistent backup using data pump
Next Topic: Expdp taking more time to dump the file
Goto Forum:
  


Current Time: Mon Dec 23 02:37:57 CST 2024