# How to use awk or sed to convert csv diffs into more readable format

Can anyone give me the example of how to use awk or sed (not sure which one, as I haven't use either of these much, as I have mostly been using grep and cut to work with csv data) to convert differences between two .csv files into something more readable.

e.g. if I have a generated a diff from old and new .csv files, which in a real life could be much more complex because of a large number of columns:

2,3c2,3
< Barbara,1093,19
< Magdaline,2093,20
\ No newline at end of file
---
> Barbara,1011,19
> Magdaline,12093,20
\ No newline at end of file


how could I convert it to this format:

Barbara  1093 1011
Magdaline 2093 12093


in a new format - a first column of data is a value from first column in both parts of the diff, to identify the line. Second column contains the data from the first csv file (old value) and third column - is the value from the second csv file (new value).

How can I perform such text conversion via awk or sed?

Thank you.

-
you say that the real-life file would have a large number of columns...which implies that there could be differences in the values of any or all of those columns, so a simple three-column output format will not be sufficient. –  cas Oct 5 '13 at 6:44
@cas - spot on. but given the code for a single column I would extend it to multicolumn case. –  Mitten Oct 5 '13 at 6:59
@Mitten instead of diff, output could please post your actual csv file and what you want to achieve them ? it can be simple using Python csv module. –  Rahul Patil Oct 5 '13 at 10:48
Is this CSV where some fields can be quoted, or CSV where no field contains an embedded comma? –  Gilles Oct 5 '13 at 14:41
this is the simplest csv possible - no quotations, values separated by comma. the reason I am working on diffs, not on actual .csvs is that the actual .csv are too large which makes it impossible to use any scripting solution over them. the resulting diffs are much smaller - a hundreds of lines. –  Mitten Oct 5 '13 at 23:22

Use awk with two associative arrays. Something like this:

awk -F, '
/^</{sub("< *","",$1);old[$1]=$2} /^>/{sub("> *","",$1);new[$1]=$2}
END{ for(k in old) print k,old[k],new[k] }
'


You can concatenate that all into a one-liner separated by spaces if you prefer - but I like multiple lines. ;) Here's a sample:

sauer@humpy:~$cat file < a,b,c > a,d,e gibberish < 1,2,3 > 1,4,5 sauer@humpy:~$ awk -F, '
/^</{sub("< *","",$1);old[$1]=$2} /^>/{sub("> *","",$1);new[$1]=$2}
END{ for(k in old) print k,old[k],new[k] }
' < file
a b d
1 2 4


Oh, if you have an old awk, the "< *" may need to be a fixed pattern rather than a regex, so lose the * and put in the literal amount of spaces after the directional indicator.

-
Using the comma to split makes picking out the field you want easier, but means you have to prune the leading stuff off of the first column before using it. Still, awk makes this easy, and more people should probably know awk. ;) –  dannysauer Oct 6 '13 at 19:35