How to split a delimited string into rows?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
"You can never have too many knives" -- Logan Nine Fingers
I get this message when clicking any topic within the FAQ forum. Maybe it's just my ID having a problem.chulett wrote:
Eric, can you access it directly from the FAQ forum? It's almost at the bottom of the listed posts there.
"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
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.
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
"You can never have too many knives" -- Logan Nine Fingers
You can write a shell script for the dynamic pivot.
I have one you can use this as reference and change accordingly
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
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,
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
São Paulo/SP
Brazil
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm