Trim

[just4u_sharath]

Guys,
I have an issue with the data that is loaded in to the sequential file.

1) 10161|000471276|Baja||||||||||||TX||4|0
2) 10175|000471276|Baja||||||||||||TX||4|0
3) 10161|000474056
|Baja||||||||||||TX||4|0
4) 10175|000474056
|Baja||||||||||||TX||4|0.

I am getting that extra spaces or line for the 3rd and 4th rows. Except for these 2 rows entire file is proper. I have used TRIM and TRIMB function in the transformer for the 2nd column still i am getting the error. TRIMB(DB_Data.DUNS). The data type for this column is defined as varchar(30).

thanks

[anbu]

Pre process your file and then run your job

Code: Select all

$ cat file
10161|000471276|Baja||||||||||||TX||4|0
10175|000471276|Baja||||||||||||TX||4|0
10161|000474056
|Baja||||||||||||TX||4|0
10175|000474056
|Baja||||||||||||TX||4|0
$ awk -F"|" ' NF != 18 { a=$0; getline;$0=a$0 } ; 1 ' file
10161|000471276|Baja||||||||||||TX||4|0
10175|000471276|Baja||||||||||||TX||4|0
10161|000474056 |Baja||||||||||||TX||4|0
10175|000474056 |Baja||||||||||||TX||4|0

[arunkumarmm]

Guys,
I have an issue with the data that is loaded in to the sequential file.

1) 10161|000471276|Baja||||||||||||TX||4|0
2) 10175|000471276|Baja||||||||||||TX||4|0
3) 10161|000474056
|Baja||||||||||||TX||4|0
4) 10175|000474056
|Baja||||||||||||TX||4|0.

I am getting that extra spaces or line for the 3rd and 4th rows. Except for these 2 rows entire file is proper. I have used TRIM and TRIMB function in the transformer for the 2nd column still i am getting the error. TRIMB(DB_Data.DUNS). The data type for this column is defined as varchar(30).

thanks

Yes. That is not a space. It seems to be a line terminator.

[just4u_sharath]

I am getting a new line character in the data so i am using Ereplace function to replace newline character with ''
TRIM(Ereplace(Ereplace(DB_Data.DUNS , CHAR(10), ''), CHAR(13), '')).

[ray.wurlod]

If the values are quoted you can use the "contains terminators" property in the Columns grid of a Sequential File stage.