How to Parse a CSV file into a Different Format

UNIX for Advanced & Expert Users
1

Hi

I have a CSV file with me in this format
Currency, USD, EUR,
USD, 1.00, 1.32,
EUR, 0.66, 1.00,

How do I transpose the file to get to the format below.
currency, currency, rate
USD, USD, 1.00
USD, EUR, 1.32
EUR, USD, 0.66
EUR, EUR, 1.00

Thanks for your help

We are using ksh on Unix.

Sampath

2

This is awk...

BEGIN {
        FS = OFS = ","
}

NR == 1 {
        for (i = 2; i <= NF; i++) {
                a = $i
        }
        print $1, $1, "Rate"
        next
}

{
        for (i = 2; i < NF; i++) {
                print $1, a, $i
        }
}
3

You can also use this:

sed 's/,[ ]*$//g' original
head -1 original|tr ',' '\n'|tail +2|sed 's/^/,/'>file1
tail +2 original|join -1 100 -2 100 - file1|awk -F"," '{print $1","$NF}'>file2
tail +2 original|cut -d"," -f2-|tr "," "\n">file3
paste -d"," file2 file3 >final
rm file1 file2 file3

"original" is the name of the file you start with (the csv file).
"final" contains the transformed data.

(There is a small hitch with the join statement used above (statement 3). It uses columns 100 in both files two join. This yields a cartesian product. This wont work if either file contains more than or equal to 100 columns. In such a situation change the 100 to column number that doesnt exist. I know, this is not a very neat method...but didnt work on join much :slight_smile: )