How to split a delimited string into rows?

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

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

Post by ray.wurlod »

Parse the comma-delimited field into separate columns using Transformer stage then use Pivot stage.
Last edited by ray.wurlod on Mon Nov 21, 2011 10:38 pm, edited 1 time in total.
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 »

Split off, linked and moved to the proper forum. :wink:

Another possibility - the technique mentioned in this FAQ post.
-craig

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Craig, is it possible to get the "special access" to read that FAQ link?
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

:?

AFAIK, there shouldn't be any "special access" required to read that forum, it's one of the public ones. Is that what it is telling you? Anyone else having an issue?

Eric, can you access it directly from the FAQ forum? It's almost at the bottom of the listed posts there.
-craig

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

chulett wrote::?

Eric, can you access it directly from the FAQ forum? It's almost at the bottom of the listed posts there.
I get this message when clicking any topic within the FAQ forum. Maybe it's just my ID having a problem.

"Sorry, but only users granted special access can read topics in this forum."

Thanks.
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

At a high level, the FAQ posting details writing a routine to loop thru the delimited string and building one long string where the 'key' fields are repeated per each pivoted column and then a record terminator (LF or CR/LF) concatenated into the string between each 'record'. What you get is one long record that when read back after writing it out get automagically turned into many records.

With 8.5 you could use the looping capabilites in the transformer for this rather than having to write a routine.
-craig

"You can never have too many knives" -- Logan Nine Fingers
datisaq
Participant
Posts: 154
Joined: Wed May 14, 2008 4:34 am

Post by datisaq »

You can write a shell script for the dynamic pivot.
I have one you can use this as reference and change accordingly

Code: Select all

#!/usr/bin/ksh

###########################################################################
#
#
# Script Name:  DynamicPivot.sh
#
#
# Input:        DynamicPivot.sh {Input File Absolute Path}
#
#
# Output:       Output File Absolute Path
#		0 - for success
#               Error Description - for failure
#
#
# Description:
#
#               This script gets executed on the command line, with
#               one arguments (see Input). 
#               This will perform the dynamic pivoting mechanism.
#               
#               This will pivot the data of file having two fields 
#		one file which is key field(Not Nullable) and the other field(pivot field)
#		 
#
###########################################################################
###########################################################################
#
# Setting the standard variables
#
###########################################################################

PROG=`basename ${0}`
EXIT_STATUS=0
NOW=`date`

###########################################################################
#
# Check command line arguments
#
###########################################################################

if [ ${#} -ne 2 ]
then
   echo "${NOW} ${PROG}: Invalid parameter list."
   echo "${NOW} ${PROG}: The script needs 2 parameters:"
   echo "${NOW} ${PROG}: Source File Absolute path and Target File Absolute path"
   exit 99;
fi

INPUT_FILE_PATH=${1}
OUTPUT_FILE_PATH=${2}

cat /dev/null > ${OUTPUT_FILE_PATH}

while read i
do
field1=`echo ${i} | awk -F '|' '{print $1;}'`
cnt_fld2=`echo ${i} | awk -F '|' '{print $2;}' | awk -F ',' '{print NF;}'`

j=1
if [ cnt_fld2 -eq 0 ];then
echo ${field1}"|" >> ${OUTPUT_FILE_PATH}
else
while [ j -le ${cnt_fld2} ]
do
echo ${field1}"|"`echo ${i} | awk -F '|' '{print $2;}' | awk -F ',' -v name="$j" '{print $name;}'` >> ${OUTPUT_FILE_PATH}
j=`expr ${j} + 1`
done
fi
done < ${INPUT_FILE_PATH}
exit 0;
IBM Certified - Information Server 8.1
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You could also convert the commas to value marks (@VM) and write to a hashed file, then read from the hashed file normalized on the multi-valued column.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
joycerecacho
Participant
Posts: 298
Joined: Tue Aug 26, 2008 12:17 pm

Post by joycerecacho »

Hi Guys,

At the end of the story, I created a routine which counts the number of delimiters in a list and sums one.
Example:
Joyce, John, Mary

Delimiters = 2 (+1) = "3"

Then, I wrote a FOR loop starting from 1 and going until Delimiters quantity.

Inside de Loop, it generates a string and between the values I put 'Char(10)' to break down in lines.
So, in the examples above, the result was: JoyceChar(10)JohnChar(10)Mary(Char10)
If I read this file, would be:

Joyce
John
Mary

It worked very well, was simple and fast.

Thank you guys.

Best Regards,
Joyce A. Recacho
São Paulo/SP
Brazil
karthi_gana
Premium Member
Premium Member
Posts: 729
Joined: Tue Apr 28, 2009 10:49 pm

Post by karthi_gana »

Ray,
...the hashed file normalized on the multi-valued column.
i dont understand this context. can you pls explain it?
Karthik
Post Reply