How to make a record for each comma separated field

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

DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

One column is even simpler. The awk one liner is

Code: Select all

awk -F"," '{for (i=1; i<=NF; i++) { print $i}}'
So for testing this code out. Go into your unix command prompt and type

Code: Select all

echo field1,field2,field3,field4 | awk -F"," '{for (i=1; i<=NF; i++) { print $i}}'
Your result will be

Code: Select all

field1
field2
field3
field4
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Marley777
Premium Member
Premium Member
Posts: 316
Joined: Tue Jan 27, 2004 3:26 pm

Post by Marley777 »

cool :D



Where do I put this code?

Where do I tell the code what input field to use?
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

Thats what I was asking. How many fields are there in your file and what field number is this particular field that needs to be pivoted and how is the entire record delimited. That code will be different.
The code goes into the filter command of the sequential file stage.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Marley777
Premium Member
Premium Member
Posts: 316
Joined: Tue Jan 27, 2004 3:26 pm

Post by Marley777 »

We have just one record with one field. However the one field will contain comma delimited values. If the one input field contains 10 comma seperated values, then we should end up with 10 records with one field on each reacord. But we will never know how many delimited values are in the one input field, could be one to many.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

This is precisely what the new looping construct in the Transformer stage (version 8.5) is intended to do.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

Then just put my initial code in the filter command and you are good to go.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Marley777 wrote:We have just one record with one field. However the one field will contain comma delimited values.
OK... this has been bugging me.

What is the difference between a file where each record contains "one field" and that field contains comma delimited data and a plain old regular comma delimited file? None that I can see. Unless, I suppose, they are delivering it with quotes around the "one field". :?

And while you may not know how many fields there may be, there is definitely a limit to how many you can squeeze into 255 characters.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

When the field delimiter is something else.

Code: Select all

42~DENT~Arthur~BA,BSc,FAISSA,HH2G~1998-10-21~V
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 »

You are missing the point specific to this discussion - there is only one field. People initially made the assumption it was something like you posted, but alas 'twas much simpler and hence my question.

Nice Hitchhiker's reference, btw. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Marley777
Premium Member
Premium Member
Posts: 316
Joined: Tue Jan 27, 2004 3:26 pm

Post by Marley777 »

Thanks everyone for your help.

I'm going to mark this resolved

We have two things that will work

awk -F"," '{for (i=1; i<=NF; i++) { print $i}}' (credit to DSguru2B)

or a script I will post once finished and tested.
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

You can embed the same awk command in a shell script as well. Something like

Code: Select all


#!/usr/bin/ksh 

export inFile=/my/fully/qualified/inputfilename.txt 
export outFile=/my/fully/qualified/outputfilename.txt


#remove previous output file if exists. 
if [ -f $outFile] 
then 
  rm -f $outFile 
fi 

#the main body
cat $inFile | while read fileLine 
do 
  awk -F"," '{for (i=1; i<=NF; i++) { print $i}}' $fileLine >> $outFile
done 
Give fully qualified path for the variables inFile and outFile
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Post Reply