merge csv files in unix, bash

Question

I have a few csv files that are formatted like:

test1.csv:

    field,port1
    a1,0.2
    a2,0.3
    a3,0.6

test2.csv:

field,port2
b1,0.5
b2,0.6
b3,0.7
b4,0.1
b5,0.5

test3.csv:

field, port3
c1,0.1
c2,0.4

and so forth.I want to merge these csvs into a single so it would look like:

field,port1,field,port2,field,port3
a1,0.2,b1,0.5,c1,0.1
a2,0.3,b2,0.6,c2,0.4
a3,0.6,b3,0.7,,
,,b4,0.1,,
,,b5,0.5,,

how can I do this? I cat >> but that would but everything in the first two columns. I can go about that way if I have to but having a merge like this can make my life a lot simpler.

Thanks

fedorqui · Accepted Answer · 2013-07-24 11:52:32Z

4

paste can do quite a similar thing:

$ paste -d, test[1-3].csv
field,port1,field,port2,field, port3
a1,0.2,b1,0.5,c1,0.1
a2,0.3,b2,0.6,c2,0.4
a3,0.6,b3,0.7,
,b4,0.1,
,b5,0.5,

Note that -d, stands for delimiter to be a comma.

answered Jul 24, 2013 at 11:52

fedorqui

293k113 gold badges592 silver badges640 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

yatici Over a year ago

hmm didn't know there was a paste command. This makes things easier.

yatici Over a year ago

yea I lose commas with this and order shifts

Walter Tross Over a year ago

@glennjackman that is not enough: it does't solve the case when there is a shorter file between two longer ones. See my answer for the solution

Walter Tross · Accepted Answer · 2013-07-24 13:53:13Z

1

Building on fedorqui's answer:

paste -d: test[1-3].csv | sed -e's/^:/,:/' -e's/::/:,:/g' -e's/::/:,:/g' -e's/:$/:,/' -e's/:/,/g'

(assuming you have no : in your files - but you can choose another temporary separator)

This restores all the commas you expect. The pair of identical substitution instructions is needed because a substituted string is not taken into account for another substitution.

In general:

paste -d'T' file... | sed -e's/^T/ET/' -e's/TT/TET/g' -e's/TT/TET/g' -e's/T$/TE/' -e's/T/S/g'

where T is the temporary separator (: above), E is the string that should replace an empty or missing line (, above), and S is the separator between the lines of the pasted files (, above). The temporary separator T (a generic string) must not appear in the files and in E, while the final separator S can.

Warning: The above commands may need spaces before the quoted strings in your shell

edited Jul 24, 2013 at 13:53

answered Jul 24, 2013 at 12:18

Walter Tross

12.7k3 gold badges43 silver badges70 bronze badges

4 Comments

yatici Over a year ago

I get an error sed: Unrecognized command: -es/::/:,:/g. Can you help?

Walter Tross Over a year ago

@yatici: try adding a space after -e (that is not necessary, because of the single quotes, in bash, but might be necessary in other shells)

yatici Over a year ago

yep seems like it works. I am on bash too. Maybe just a different version issue.

Walter Tross Over a year ago

@yatici, I have tried on bash 4.1.5 and bash 4.1.10, no space needed - funny

Collectives™ on Stack Overflow

merge csv files in unix, bash

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related