Field with 'delim=end' did not consume entire input

deepa_shenoy · Post by **deepa_shenoy** » Sat Nov 21, 2009 9:33 am

The source and target columns of the files have Varchar(1). 90% records are passed and 10% rejected. Why are the 10% getting rejected?

The error thrown is:

Sequential_File_9,0: Field "DP_Customer_Sol" with 'delim=end' did not consume entire input, at offset: 145

chulett · Post by **chulett** » Sat Nov 21, 2009 9:55 am

In case anyone hasn't seen the previous conversation on this topic and wants to know how we got here:

viewtopic.php?t=130698

ArndW · Post by **ArndW** » Sat Nov 21, 2009 12:23 pm

I guess my answers there weren't good enough.

deepa_shenoy · Post by **deepa_shenoy** » Sun Nov 22, 2009 12:32 am

The error still persists.

I posted a new topic as the previous topic's subject line would have been misleading.

deepa_shenoy · Post by **deepa_shenoy** » Sun Nov 22, 2009 12:35 pm

Hi All,

Does anyone have any solution??

Thanks in advance.

Regards,
Deepa

datskosaraju · Post by **datskosaraju** » Sun Nov 22, 2009 1:34 pm

deepa_shenoy wrote:Hi All,

Does anyone have any solution??

Thanks in advance.

Regards,
Deepa

change the record delimiter from unix new line to MS DOS......and see if that works

datskosaraju · Post by **datskosaraju** » Sun Nov 22, 2009 1:36 pm

deepa_shenoy wrote:Hi All,

Does anyone have any solution??

Thanks in advance.

Regards,
Deepa

change the record delimiter from unix new line to MS DOS......and see if that works

deepa_shenoy · Post by **deepa_shenoy** » Sun Nov 22, 2009 10:34 pm

It did not work... any other solution?

ray.wurlod · Post by **ray.wurlod** » Sun Nov 22, 2009 10:53 pm

Please tell us how you changed your record delimiter.

Also, can you please post a hex dump of the first line or three of the file in question, and the record schema you are using when reading it?

deepa_shenoy · Post by **deepa_shenoy** » Sun Nov 22, 2009 11:41 pm

I gave Record delimiter string=DOS Format in the Formats Tab.

Sample data in the file which is getting rejected is:
0472 3599 INDUSTRIES, INC. AURN WA 980 3223 STREET, NE, UNIT 6 NULL NULL NULL NULL NULL 0 NULL NULL
100 3542 PRODUCTS, INC. OHAM NE 681 126 STREET NULL NULL CHUCK ODDO 402-333 NULL 0 NULL

ray.wurlod · Post by **ray.wurlod** » Mon Nov 23, 2009 4:03 am

That's not a hex dump. I'd like to see what's really there. What are you using as your Null Field Value property? I did also ask for the record schema.

deepa_shenoy · Post by **deepa_shenoy** » Mon Nov 23, 2009 4:11 am

I'm sorry, but what is a 'hex dump'?

I have not used any Record Schema.

Also, the value I am giving to the Null Field value is '\xAB'

I notice the problem is because it is the LAST column.

Field Delimiter is comma.
Record Delimiter is NEW UNIX Line
Final Delimiter is end
Quote is double.

Thanks in advance.

Regards,
Deepa

ray.wurlod · Post by **ray.wurlod** » Mon Nov 23, 2009 4:26 am

A hex dump is output that shows each byte as a pair of hex characters. You can use a command like od -c filename to get one.

You must be using a record schema. Open the generated OSH in the Job Properties dialog; you will find it there. You will also find it on the Layout tab of the table definition you loaded into your job design. There are various other ways to get the record schema, but I won't bore you with these.

deepa_shenoy · Post by **deepa_shenoy** » Mon Nov 23, 2009 5:27 am

Thank you for that information.

A portion of the hex dump is

547756620 253 " \n " L E S B " ,
547756640 " 253 " , " G o o d m a n & S
547756660 o n s " , " S u l l i v a n " ,
547756700 " I N " , " 4 7 8 8 2 " , " R .
547756720 R . 5 B o x 1 6 B " , " 253
547756740 " , " 253 " , " S c o t t G o o
547756760 d m a n " , " 8 1 2 2 6 8 4 7 0
547757000 0 " , " 253 " , " 0 " , " 253 " \n "
547757020 L E S B 5 " , " 253 " ,
547757040 " J o b T . M a n k e " , "
547757060 S a n A n t o n i o " , " T X
547757100 " , " 7 8 2 5 8 " , " 2 5 1 1 1
547757120 A r r o w R i d g e " , " 253
547757140 " , " 253 " , " J o n L a n g e
547757160 " , " 2 1 0 4 9 7 4 6 1 5 " , "
547757200 253 " , " 0 " , " 253 " \n " Q 2 7 2
547757220 1 " , " 253 " , " D a l
547757240 l a s H a k a n s o n " , " C
547757260 l o v i s " , " C A " , " 9 3 6
547757300 1 2 " , " 1 1 0 5 C o l e B
547757320 " , " 253 " , " 253 " , " 253 " , " 2
547757340 0 9 2 9 8 4 6 0 2 " , " 253 " , "
547757360 0 " , " 253 " \n
547757367

The record schema of the two files-Previous and Current are given below.
ExecSH moves data from Current to Previous before executing the job.
90% records get transferred. 10% records rejected.

#################################################################
#### STAGE: SEQL_Previous
## Operator
import
## Operator options
-schema record
{final_delim=end, record_delim='\n', delim=',', quote=double}
(
ccan:string[max=10] {quote=double};
sic_cd:nullable string[max=6] {quote=double, null_field='\xAB'};
cust_name:nullable string[max=50] {quote=double, null_field='\xAB'};
cust_city:nullable string[max=30] {quote=double, null_field='\xAB'};
cust_state:nullable string[max=2] {quote=double, null_field='\xAB'};
cust_zip:nullable string[max=10] {quote=double, null_field='\xAB'};
cust_addr1:nullable string[max=50] {quote=double, null_field='\xAB'};
cust_addr2:nullable string[max=30] {quote=double, null_field='\xAB'};
cust_fed_id:nullable string[max=20] {quote=double, null_field='\xAB'};
cust_contact_name:nullable string[max=25] {quote=double, null_field='\xAB'};
cust_contact_phone:nullable string[max=15] {quote=double, null_field='\xAB'};
cust_cr_rating:nullable string[max=4] {quote=double, null_field='\xAB'};
num_of_contracts:nullable int32 {quote=double, null_field='\xAB'};
cd_facility_score:nullable string[max=17] {quote=double, null_field='\xAB'};
DP_Customer_Solicit:nullable string[max=1] {quote=double, null_field='\xAB'};
)
-file '[&"$envLANDINGFILEPATH"][&"ExtractParamSet.pSRCSYS_CODE"][&"ExtractParamSet.pDATA_IN_PREVIOUS"]'
-rejects continue
-reportProgress yes

## General options
[ident('SEQL_Previous'); jobmon_ident('SEQL_Previous')]
## Outputs
0> [] 'SEQL_Previous:LinkFromPrevious.v'
;

#################################################################
#### STAGE: SEQL_Current
## Operator
export
## Operator options
-schema record
{final_delim=end, record_delim='\n', delim=',', quote=double}
(
ccan:string[max=10] {quote=double};
sic_cd:nullable string[max=6] {quote=double, null_field='\xAB'};
cust_name:nullable string[max=50] {quote=double, null_field='\xAB'};
cust_city:nullable string[max=30] {quote=double, null_field='\xAB'};
cust_state:nullable string[max=2] {quote=double, null_field='\xAB'};
cust_zip:nullable string[max=10] {quote=double, null_field='\xAB'};
cust_addr1:nullable string[max=50] {quote=double, null_field='\xAB'};
cust_addr2:nullable string[max=30] {quote=double, null_field='\xAB'};
cust_fed_id:nullable string[max=20] {quote=double, null_field='\xAB'};
cust_contact_name:nullable string[max=25] {quote=double, null_field='\xAB'};
cust_contact_phone:nullable string[max=15] {quote=double, null_field='\xAB'};
cust_cr_rating:nullable string[max=4] {quote=double, null_field='\xAB'};
num_of_contracts:nullable int32 {quote=double, null_field='\xAB'};
cd_facility_score:nullable string[max=17] {quote=double, null_field='\xAB'};
DP_Customer_Solicit:nullable string[max=1] {quote=double, null_field='\xAB'};
)
-file '[&"$envLANDINGFILEPATH"][&"ExtractParamSet.pSRCSYS_CODE"][&"ExtractParamSet.pDATA_OUT_CURRENT"]'
-overwrite
-rejects continue

## General options
[ident('SEQL_Current'); jobmon_ident('SEQL_Current'); nocombine]
## Inputs
0< [] 'COPY_CUSTOMER:LinkToCurrent.v'
;

#################################################################

deepa_shenoy · Post by **deepa_shenoy** » Tue Nov 24, 2009 3:54 am

Anyone any solution?