How to merge all columns of one CSV file with another CSV file?

[cirosantilli]

I have two CSV files, both have the same number of rows:

in1.csv

a,b
1,2
3,4

in2.csv

c,d
5,6
7,8

How to create a merged CSV as:

a,b,c,d
1,2,5,6
3,4,7,8

My first instinct would be to try and use join and try to add a virtual ID column to both CSV that contains the row index and use that as the join key, but I'm not sure if that is possible and what's the syntax.

It is OK to assume that the header titles are different, or that there are no headers.

Perhaps this is what was asked at: #1200 ? But there wasn't a minimal example and a minimal answer :-)

This would also answer: https://unix.stackexchange.com/questions/293775/merging-contents-of-multiple-csv-files-into-single-csv-file

[aborruso]

Hi @cirosantilli , it seems to me that you would like a verb to do the paste, which is merge horizontally.

I also asked for this feature: #335

Using the linux cli, you should use paste and run

paste -d ',' in1.csv in2.csv >out.csv

[cirosantilli]

Hi thanks for linking to the feature request. Yes, paste works if your CSV doesn't have newlines, but mine did :-) I ended up going with jsonl + paste + jq this time as I had a choice.

[aborruso]

Hi thanks for linking to the feature request. Yes, paste works if your CSV doesn't have newlines, but mine did :-) I ended up going with jsonl + paste + jq this time as I had a choice.

Hi @cirosantilli I have used your sample files, they do not have newlines. Could you share some real file of yours?

[cirosantilli]

Something like:

a,b
1,"2
2"
3,4

[aborruso]

If you cannot remove carriage returns. a great tool is duckdb

duckdb --csv -c "SELECT  one.*, two.*
    FROM 'in1.csv' AS one
    POSITIONAL JOIN 'in2.csv' AS two"

In output

a,b,c,d
1,"2
2",5,6
3,4,7,8

❯ duckdb -json -c "SELECT  one.*, two.*
    FROM 'in1.csv' AS one
    POSITIONAL JOIN 'in2.csv' AS two"
[{"a":1,"b":"2\n2","c":5,"d":6},
{"a":3,"b":"4","c":7,"d":8}]

[cwarden]

You can use 3 mlr invocations with process substitutions to add line numbers, then join on them:

$ mlr --csv join --ul -j line_num -f <(mlr --csv put '$line_num=NR' in1.csv) <(mlr --csv put '$line_num=NR' in2.csv)

[aborruso]

Great answer, thank you

[aborruso]

I would only add cut -x -f line_num to not have the extra column:

mlr --csv join --ul -j line_num -f <(mlr --csv put '$line_num=NR' in1.csv) then cut -x -f line_num <(mlr --csv put '$line_num=NR' in2.csv)