Complex Flat File trimming trailing blanks

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
jcronje69
Participant
Posts: 2
Joined: Mon Aug 28, 2017 3:30 am

Complex Flat File trimming trailing blanks

Post by jcronje69 »

Hi

I use CFF (Complex Flat File) to populate a MS SQL DB with hundreds of CFF files. The issue I have is that the CFF use a Character native data type for string values and the trailing blanks are not trimmed automatically.
Options I have is to use the Datastage TRIM function or set ANSI_PADDING OFF on the MS SQL table. There are too many columns to implement the TRIM function. The ANSI_PADDING OFF setting is set to be discontinued for future SQL Server releases.

Any other option I can use?

thank you
Jaco
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Welcome aboard.

Using a Transformer stage, select all the columns that need to be trimmed, right click, and choose Derivation Substitution from the menu. Type Trim($1) into the expression field. All done.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Just make sure you are trimming into a "varchar" rather than a character field.
-craig

"You can never have too many knives" -- Logan Nine Fingers
jcronje69
Participant
Posts: 2
Joined: Mon Aug 28, 2017 3:30 am

Post by jcronje69 »

thank you, nice to be on-board
that's an easy enough solution
thank you kindly
Post Reply