Reading through the delimter 000

bala_135 · Post by **bala_135** » Fri Jun 20, 2008 3:47 am

Hi,

I have a file in the below mentioned format.
UHDR1
1234567890 AB1CD12345 ABCDEFGH ABC@ABC.COM
UTRL1

Since the Header and Tralier Format is going to be the same for all the files,I am creating a template job to remove the Header and Tralier.

Below is the design
1.Read all the records as a row using the datatype as Varchar(8000)
2.Using the following formats(000 as delimter and 000 as quote character).As some files also contain datas which has ,.* etc
3.Read the trmmed file as a fixed width file with appropriate metadata.
4.For the above mentioned file alone it is not able to read the entire record.

To be precise it is able to read till
1234567890 AB1CD12345 ABCDEFGH

then it throws the warning(view data
)
ext_trim_Header_trailer_mlol_consent..Seq_Input.InFrmSeq: read_delimited() - row 1, too many columns in record

If I use other delimiter it is able to read it.
My speculation is some NULL character is appearing in the file after that part of the record.Kindly let me know if my speculation is right.
Also suggest me some delimter which is hard to come by in the file(I have changed it to ^).

Thanks in advance.

Regards,
Bala.

chulett · Post by **chulett** » Fri Jun 20, 2008 6:06 am

Either use a delimiter that won't be found (and you need to determine it) or enable the Suppress row truncation warnings option if you believe that the found delimiter comes after all your data.

bala_135 · Post by **bala_135** » Fri Jun 20, 2008 6:58 am

Hi,

Thanks Craig.
I cant enable the supress row truncations as data is not read fully.

I have used some of the delimters(144 or 161) suggested in the below link.These are very rare to come from the source.
No worries my data is Engish not french
http://www.asciitable.com/

Out my curiosity where can we find the allowable delimiters for datastage.Because not all extended ascii values are working(eg :128 throws error.

Thanks once again.

Regards,
Bala.

chulett · Post by **chulett** » Fri Jun 20, 2008 7:03 am

128 throws what kind of error? At runtime or compile time?

bala_135 · Post by **bala_135** » Fri Jun 20, 2008 7:11 am

Hi,
Its compile time.During view data itself its not accepting.

Error calling subroutine: DSD.Browse (Action=3); check DataStage is set up correctly in project Test

Regards,
Bala.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 20, 2008 7:17 am

128 is the internal representation of NULL. Avoid it as a delimiter!

chulett · Post by **chulett** » Fri Jun 20, 2008 7:22 am

Ah... I thought that was '000'. Avoid! Avoid!

(Funny, but I heard your advice in a Dalek voice in my head)