I have a file with an input :
rownum,identifier,items_in_list
1,"ABC",{(123),(345),(69),(95),(90),(83),(3A)}
with expected output as :
rownum,identifier,items_in_list
1,"ABC",123
1,"ABC",345
1,"ABC",69
1,"ABC",95
1,"ABC",90
1,"ABC",83
1,"ABC",3A
I tried using "awk" but it was for turning all items in a column to rows, but mine just needs some columns to rows..
My Code:
echo "1,"ABC",{(123),(345),(69),(95),(90),(83),(3A)}" | awk -vRS="{" 'NF'
but this converts to:
1,ABC,
(123),(345),(69),(95),(90),(83),(3A)}
Update:
All of your commands work fine, but for one small glitch sorry being a newbie I can vote only one as a answer.
Thank You! but I am having trouble if the rows dont have multiple numbers and have just one.. eg, in this format:
Input
1,33262,"ABC",{(64)}
1,33263,"ABC",{(66),(57)}
Actual Output:
1,33262,SOME_FIELD_NAME
1,33262,64
1,33263,SOME_FIELD_NAME
1,33262,65,66
Required Output:
1,33262,SOME_FIELD_NAME,64
1,33263,SOME_FIELD_NAME,65
1,33263,SOME_FIELD_NAME,66
Update:
"Actual Output" of code suggested by Jotne: awk -F, '{a=$1","$2;gsub(/[{()}]/,"");for (i=3;i<=NF;i++) print a","$i}' file.
Sorry my input has 2 leading fields sometimes and 3-10 leading fields some other times, but the row that we want to convert to column always begins with '{' , individual numbers are enclosed in '()' and end of the row is denoted by '}'. Jotne's code works fine for 2 leading fields but fails for 3 leading fields. Can someone suggest a generic way to parse the fields?
Here is one way with awk
awk -F, '{a=$1","$2;gsub(/[{()}]/,"");for (i=3;i<=NF;i++) print a","$i}' file
1,"ABC",123
1,"ABC",345
1,"ABC",69
1,"ABC",95
1,"ABC",90
1,"ABC",83
1,"ABC",3A
Using RS
awk -vRS=, '{gsub(/[{()}]/,"")} NR==1 {a=$1;next} NR==2 {a=a","$1;next} {print a","$1}' file
1,"ABC",123
1,"ABC",345
1,"ABC",69
1,"ABC",95
1,"ABC",90
1,"ABC",83
1,"ABC",3A