OraFAQ Forum: Server Utilities » SQL*Loader question

Home » RDBMS Server » Server Utilities » SQL*Loader question

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

SQL*Loader question [message #177731]

Fri, 16 June 2006 05:32

Fred Easey
Messages: 73
Registered: January 2005

Member

Hi,

I'm trying to load in some data from a text flat file using SQL*Loader.

I'm getting an error when I run the batch file:

Quote:

Record 2: Rejected - Error on table FE_EXPERIAN_DATA, column PCD_H_TCH_I.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 1: Rejected - Error on table FE_EXPERIAN_DATA, column LAST_POKER_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected

This is my control file:

options(skip=1, errors=0, rows=1000)
load data
append into table fe_experian_data
fields terminated by ',' optionally enclosed by '"'
(
   account_id integer external,
   last_activity_date date "dd/mm/yyyy hh24:mi:ss",
   Surname char,
   Firstname char,
   Address_Line_1 char,
   Address_Line_2 char,
   Address_Line_3 char,
   Address_Line_4 char,
   Town char,
   Post_Code char,
   County char,
   Country_of_Registration_Name char,
   Billing_Address_Line_1 char,
   Billing_Address_Line_2 char,
   Billing_Address_Line_3 char,
   Billing_Address_Line_4 char,
   Billing_Post_Code char,
   Billing_County char,
   Last_Bet_date date "dd/mm/yyyy hh24:mi:ss",
   Last_Poker_date date "dd/mm/yyyy hh24:mi:ss",
   bs_id integer external,
   tch_i integer external,
   P_FSS2_Type_i char,
   H_Touchpoint_i integer external,
   H_FSS2_Type_i integer external,
   PC_FSS2_Type_i integer external,
   pcd_h_tch_i integer external

)

Here is a sample of the text file

Quote:

Account_Id,Last_Activity_Date,Surname,Firstname,Address_Line_1,Address_Line_2,Address_Line_3,Address_Line_4,Town,Post_Code,County,Cou ntry_of_Registration,Billing_Address_Line_1,Billing_Address_Line_2,Billing_Address_Line_3,Billing_Address_Line_4,Billing_Post_Code,Bi lling_County,Last_Bet_Date_Time,Last_Poker_Date_Time,bs_id,tch_i,_P_FSS2_Type_i,H_Touchpoint_i,_H_FSS2_Type_i,_PC_FSS2_Type_i,pcd_h_t ch_i
10.00,16/03/2006 11:49:05,thomas,james,Flat 3a,54 Grange Park,Ealing,#EMPTY,#EMPTY,W5 3PR,#EMPTY,United Kingdom,Flat 3A,54 Grange Park,Ealing,#EMPTY,W5 3PR,#EMPTY,16/03/2006 11:49:05,#EMPTY,1,,,,,1,4
25.00,17/05/2006 21:30:28,Katz,Philipe,Citigroup Centre,"Canada Square,Canary Wharf",London,#EMPTY,,E14 5LB,#EMPTY,United Kingdom,Citigroup Centre,Canada Square,Canary Wharf,#EMPTY,E14 5LB,#EMPTY,17/05/2006 21:30:28,#EMPTY,2,,,,,,
59.00,21/05/2006 19:27:38,washbourne,gary,347 Ware Road,Hertford,Herts,#EMPTY,#EMPTY,SG13 7EL,#EMPTY,United Kingdom,3 Aspen Way,WELWYN GARDEN CITY,Hertfordshire,#EMPTY,AL7 1HR,#EMPTY,21/05/2006 19:27:38,31/10/2005 16:36:56,3,,,16,24,24,3
82.00,19/05/2006 23:56:00,Craig,Geoff,7 channocks wharf,42 narrow st canary wharf,london,#EMPTY,#EMPTY,e14 8dj,#EMPTY,United Kingdom,7 channocks wharf,42 narrow st canary wharf,london,#EMPTY,e14 8dj,#EMPTY,22/04/2006 16:31:08,19/05/2006 23:56:00,4,4,01a,4,1,1,2
90.00,05/05/2006 17:46:54,Strang,Robert,FLAT 5 17 MOWBRAY ROAD,#EMPTY,#EMPTY,#EMPTY,LONDON,NW6 7QX,#EMPTY,United Kingdom,FLAT 5 17 MOWBRAY ROAD,#EMPTY,#EMPTY,#EMPTY,NW6 7QX,#EMPTY,05/05/2006 17:46:54,#EMPTY,5,,,,,22,3
1000.00,21/05/2006 16:55:41,evans,clifford,17 oaktree drive,gedling,nottingham,#EMPTY,#EMPTY,ng4 4da,#EMPTY,United Kingdom,17 oak tree drive,gedling,nottingham,#EMPTY,ng44da,#EMPTY,21/05/2006 16:55:41,#EMPTY,6,13,28a,13,28,34,14
1800.00,21/05/2006 16:05:55,rix,henry,Tuffields house,Tuffields road,Whepstead bury st edmunds,#EMPTY,#EMPTY,IP29 4TN,#EMPTY,United Kingdom,Tuffields House,Tuffields Road Whepstead,BURY ST EDMUNDS Suffolk,#EMPTY,IP29 4TN,#EMPTY,21/05/2006 16:05:55,#EMPTY,7,10,26c,18,26,23,18
2700.00,26/12/2000 12:19:31,Jones,Richard,35 Brierley Street,#EMPTY,Oldham,#EMPTY,#EMPTY,OL8 3DZ,#EMPTY,United Kingdom,35 Brierley Street,OLDHAM,Lancashire,#EMPTY,OL8 3DZ,#EMPTY,26/12/2000 12:19:31,#EMPTY,8,,,12,20,20,12
3200.00,12/01/2006 17:34:11,rivolta,scott,19 highfield drive,garforth,leeds,#EMPTY,,ls251jy,#EMPTY,United Kingdom,19 Highfield Drive,Garforth,LEEDS West Yorkshire,#EMPTY,LS25 1JY,#EMPTY,12/01/2006 17:34:11,#EMPTY,9,5,17a,6,17,33,18

Any idea what's wrong here?

I'm guessing it's the blank data for the numeric fields, is it that SQL*Loader is expecting something there (like the word NULL?)

Is there a way round this?

[Updated on: Fri, 16 June 2006 05:34]

Report message to a moderator

Re: SQL*Loader question [message #177733 is a reply to message #177731]

Fri, 16 June 2006 05:41

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Add the TRAILING NULLCOLS clause.

options(skip=1, errors=0, rows=1000)
load data
append into table fe_experian_data
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
   account_id integer external,
....

Report message to a moderator

Re: SQL*Loader question [message #177734 is a reply to message #177731]

Fri, 16 June 2006 05:50

Fred Easey
Messages: 73
Registered: January 2005

Member

Thanks for that.

ok, now I'm getting this error:

Record 1: Rejected - Error on table FE_EXPERIAN_DATA, column LAST_POKER_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected

This is the record from bad:

Quote:

10.00,16/03/2006 11:49:05,thomas,james,Flat 3a,54 Grange Park,Ealing,#EMPTY,#EMPTY,W5 3PR,#EMPTY,United Kingdom,Flat 3A,54 Grange Park,Ealing,#EMPTY,W5 3PR,#EMPTY,16/03/2006 11:49:05,#EMPTY,1,,,,,1,4

So presumably it's because I have the string "#EMPTY" where it's expecting a NULL?

Any way of fixing this?

Report message to a moderator

Re: SQL*Loader question [message #177740 is a reply to message #177734]

Fri, 16 June 2006 06:26

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

NULLIF.

options(skip=1, errors=0, rows=1000)
load data
append into table fe_experian_data
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
   account_id integer external,
   last_activity_date date "dd/mm/yyyy hh24:mi:ss",
   Surname char,
   Firstname char,
   Address_Line_1 char,
   Address_Line_2 char,
   Address_Line_3 char,
   Address_Line_4 char,
   Town char,
   Post_Code char,
   County char,
   Country_of_Registration_Name char,
   Billing_Address_Line_1 char,
   Billing_Address_Line_2 char,
   Billing_Address_Line_3 char,
   Billing_Address_Line_4 char,
   Billing_Post_Code char,
   Billing_County char,
   Last_Bet_date date "dd/mm/yyyy hh24:mi:ss",
   Last_Poker_date date "dd/mm/yyyy hh24:mi:ss" "NULLIF(:last_poker_date,'#EMPTY')",
   bs_id integer external,
   tch_i integer external,
   P_FSS2_Type_i char,
   H_Touchpoint_i integer external,
   H_FSS2_Type_i integer external,
   PC_FSS2_Type_i integer external,
   pcd_h_tch_i integer external
)

Report message to a moderator

Re: SQL*Loader question [message #177756 is a reply to message #177740]

Fri, 16 June 2006 08:07

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

And just to add to Art's answer, the reason you are getting this is because you have a DATE column, but your data shows #EMPTY. This is not a date from where I come from.

Also, your header record is exactly that; a header record of all character strings.

Report message to a moderator

Re: SQL*Loader question [message #177760 is a reply to message #177731]

Fri, 16 June 2006 08:31

Fred Easey
Messages: 73
Registered: January 2005

Member

Thanks again fo helping me out with this.

The NULLIF option has fixed the previous issue, I'm now getting a new error Sad

The log file is as follows:

Quote:

Record 4425: Rejected - Error on table FE_EXPERIAN_DATA.
ORA-01858: a non-numeric character was found where a numeric was expected

Here's the .bad:

Quote:

,London,#EMPTY,#EMPTY,#EMPTY,SE5 8NY,#EMPTY,United Kingdom,14 Maude Road,LONDON,#EMPTY,#EMPTY,SE5 8NY,#EMPTY,18/07/2004 18:13:36,#EMPTY,,,,,4425,,,,,1,3

I suspect it's releated to the first field being empty?

Is there such a thing as "leading nullcols" I can turn on?

Report message to a moderator

Re: SQL*Loader question [message #178117 is a reply to message #177760]

Mon, 19 June 2006 07:55

Fred Easey
Messages: 73
Registered: January 2005

Member

Anyone?

Report message to a moderator

Re: SQL*Loader question [message #178138 is a reply to message #178117]

Mon, 19 June 2006 09:52

Fred Easey
Messages: 73
Registered: January 2005

Member

Ok, I fixed the other problem (using when (01) <> ','), I'm now getting another error (I think) because of a field starting with a double quote, but with no terminating double quote.

Error message is:

Quote:

Record 6904: Rejected - Error on table FE_EXPERIAN_DATA, column ADDRESS_LINE_1.
no terminator found after TERMINATED and ENCLOSED field

.bad file is:

Quote:

53918.00,27/12/2005 21:34:18,Pope,Adam," Viking Way,Brentwood,Essex,#EMPTY,#EMPTY,CM159HX,#EMPTY,United Kingdom," Viking Way,Brentwood,Essex,#EMPTY,CM159HX,#EMPTY,29/06/2005 16:07:12,27/12/2005 21:34:18,6904,7,14a,7,14,17,14

Is there any way of fixing this?

Fred

Report message to a moderator

Re: SQL*Loader question [message #179597 is a reply to message #178138]

Wed, 28 June 2006 03:00

Fred Easey
Messages: 73
Registered: January 2005

Member

Anyone?

Report message to a moderator

Re: SQL*Loader question [message #180221 is a reply to message #179597]

Fri, 30 June 2006 18:22

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

Senior Member

You can only do so much to clean up bad data. The best you could do is to go through the bad file, make manual corrections, then run a second load using the corrected bad file as the infile.

Report message to a moderator

Previous Topic:	loading data that needs or condition
Next Topic:	Loading into multiple rows from same record from csv file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 08:39:43 CST 2024