DSXchange

Posted: **Fri Feb 25, 2011 1:24 pm**

One column is even simpler. The awk one liner is

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

Posted: **Fri Feb 25, 2011 1:30 pm**

cool :D

Where do I put this code?

Where do I tell the code what input field to use?

Posted: **Fri Feb 25, 2011 1:31 pm**

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.

Posted: **Fri Feb 25, 2011 1:37 pm**

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.

Posted: **Fri Feb 25, 2011 2:11 pm**

This is precisely what the new looping construct in the Transformer stage (version 8.5) is intended to do.

Posted: **Fri Feb 25, 2011 2:35 pm**

Then just put my initial code in the filter command and you are good to go.

Posted: **Fri Feb 25, 2011 3:58 pm**

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.

Posted: **Fri Feb 25, 2011 11:19 pm**

When the field delimiter is something else.

Code: Select all

42~DENT~Arthur~BA,BSc,FAISSA,HH2G~1998-10-21~V

Posted: **Fri Feb 25, 2011 11:39 pm**

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.

Posted: **Mon Feb 28, 2011 2:09 pm**

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.

Posted: **Mon Feb 28, 2011 3:50 pm**

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

DSXchange

How to make a record for each comma separated field