| Single quotes in sqloader data [message #152477] |
Fri, 23 December 2005 07:35  |
rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England
|
Junior Member |
|
|
Hi there I have a data file like this :-
'A/147171';'T2549670';'T2549670';'';'01/07/2005';.... rest of data
The job is falling over on the date field saying
a non-numeric character was found where a numeric was expected
How do I get SQL Loader to ignore the 's - How do I get optionally enclosed to work with single quote
regards
bob..
|
|
|
|
| Re: Single quotes in sqloader data [message #152479 is a reply to message #152477] |
Fri, 23 December 2005 07:45  |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
I believe the error is generated due to two single quotes before date value and as you can see no data is present.
It would be helpful if you post the control file and excerpt from data file .
And OS, Oracle versions also.
regards,
tarun
|
|
|
|
| Re: Single quotes in sqloader data [message #152490 is a reply to message #152479] |
Fri, 23 December 2005 08:20 |
rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England
|
Junior Member |
|
|
Hi there,
here is the script
LOAD DATA
INFILE billhistory.txt
APPEND
INTO TABLE spainmigbillhistory2
fields terminated by ';'
( TPH_BILL_NUM ,
TPH_BILL_ACCOUNT_NUM,
TPH_ACCOUNT_NUM,
DUMMY_FIELD1 FILLER,
DUMMY_FIELD2 FILLER,
NOMINAL_BILL_DATE DATE "dd/mm/yyyy",
ACTUAL_BILL_DATE DATE "dd/mm/yyyy",
DUMMY_DATE ,
INVOICE_NET ,
VAT_RATE ,
VAT_AMOUNT ,
INVOICE_GROSS ,
RESULT ,
PREVIOUS_BALANCE ,
BALANCE_OUTSTANDING )
Here is an excerpt of the data file :-
'A/1';'T100002';'T100002';'';'01/05/2004';'31/05/2004';'01/06/2004';'08/06/2004';'1.36';'16';'0.22';'0';'0';'1.58';'0';'0';'0';'0';'0 ';'0';'clientes\resumen\200405\resumen_T100002_T100002_1.pdf';'';'0001';'S';'20040501';'';'02';'0';'S';'N'
'A/10';'T1720842';'T1720842';'';'01/06/2004';'30/06/2004';'01/07/2004';'08/07/2004';'15.67';'16';'2.51';'0';'0';'18.18';'0';'0';'0';' 0';'0';'0';'clientes\resumen\200406\resumen_T1720842_T1720842_10.pdf';'';'0001';'S';'20040601';'';'02';'0';'S';'N'
'A/100';'T179447';'T179447';'';'01/08/2004';'31/08/2004';'01/09/2004';'08/09/2004';'3.1';'16';'0.5';'0';'0';'3.6';'0';'0';'0';'0';'0' ;'0';'clientes\resumen\200408\resumen_T179447_T179447_100.pdf';'';'0001';'S';'20040801';'';'02';'0';'S';'N'
'A/1000';'T1989140';'T1989140';'';'01/09/2004';'30/09/2004';'01/10/2004';'11/10/2004';'0.65';'16';'0.1';'0';'0';'0.75';'0';'0';'0';'0 ';'0';'0';'clientes\resumen\200409\resumen_T1989140_T1989140_1000.pdf';'';'0001';'S';'20040901';'';'02';'0';'S';'N'
'A/10000';'T2128653';'T2128653';'';'19/11/2004';'30/11/2004';'01/12/2004';'08/12/2004';'0';'16';'0';'0';'0';'0';'0';'0';'0';'0';'0';' 0';'clientes\resumen\200411\resumen_T2128653_T2128653_10000.pdf';'';'0001';'S';'20041101';'';'02';'0';'S';'N'
'A/100000';'T2434899';'T2434899';'';'01/05/2005';'31/05/2005';'01/06/2005';'14/06/2005';'20.24';'16';'3.24';'0';'0';'23.48';'0';'0';' 0';'0';'0';'0';'clientes\resumen\200505\resumen_T2434899_T2434899_100000.pdf';'';'0001';'S';'20050501';'';'02';'0';'S';'N'
I'm using 9i on a sun machine
thanks bob...
|
|
|
|
| Re: Single quotes in sqloader data [message #152567 is a reply to message #152490] |
Fri, 23 December 2005 23:55 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Try adding the clause
OPTIONALLY ENCLOSED BY X'nn'
where nn is the hex for the quote character (sorry, can't look it up myself right now). Or this might work:
OPTIONALLY ENCLOSED BY X''''
_____________
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
