Read columns from file, then column into an exsisting csv file

Question

Essentially, I have a csv file that contain multiple columns, called cols.csv

1,a,100
2,b,200
3,c,300
4,e,400

and I have a new csv file that has one column, called col.csv

f
g
h
i

I want to copy the items in col.csv and append them to the end of each line in cols.csv so that, cols.csv now contains those

1,a,100,f
2,b,200,g
3,c,300,h
4,e,400,i

is that possible at all? i tried join, paste, nothing worked

Answer 1

Contents of test1.txt

1,a,100
2,b,200
3,c,300
4,e,400

Contents of test2.txt

f
g
h
i

Sample.

$ paste -d, test1.txt test2.txt 
1,a,100,f
2,b,200,g
3,c,300,h
4,e,400,i

Explanation

We're using the -d flag to set the delimiter to a ,

Answer 2

Use the -d flag to paste to set the delimeter to a comma:

paste -d, cols.csv col.csv

Answer 3

Assuming we are dealing with CSV input possibly containing quoted fields with embedded newlines, we can't use line-based tools such as paste to combine our two files. Doing so would generate improperly formatted output.

The following example uses two files with three records in each, where the second field in the first file contains embedded newlines in every record.

file1:

Field 1,Field 2
YYYYMMDD-XXXX,"10 Somestreet
NNNNNN Somecity"
YYYYMMDD-YYYY,"20 Otherstreet
MMMMMM Somecity"
YYYYMMDD-ZZZZ,"25 Bumble Rd
KKKKKK Village"

file2:

Field 3
"1,a"
"1,b"
"2,c"

Using paste here would obviously not do the correct thing:

$ paste -d , file1 file2
Field 1,Field 2,Field 3
YYYYMMDD-XXXX,"10 Somestreet,"1,a"
NNNNNN Somecity","1,b"
YYYYMMDD-YYYY,"20 Otherstreet,"2,c"
MMMMMM Somecity",
YYYYMMDD-ZZZZ,"25 Bumble Rd,
KKKKKK Village",

Instead, we use the CSV-aware tool called Miller (mlr) to add record numbers to each record in the two files in a field called nr. Adding the field to the files is done "in place" by using the -I option of Miller:

$ mlr --csv -I put '$nr=FNR' file1 file2

We then use these numbers to join the files together.

$ mlr --csv join -f file1 -j nr file2
nr,Field 1,Field 2,Field 3
1,YYYYMMDD-XXXX,"10 Somestreet
NNNNNN Somecity","1,a"
2,YYYYMMDD-YYYY,"20 Otherstreet
MMMMMM Somecity","1,b"
3,YYYYMMDD-ZZZZ,"25 Bumble Rd
KKKKKK Village","2,c"

With a cut operation, we can remove the nr field if it's no longer useful:

$ mlr --csv join -f file1 -j nr then cut -x -f nr file2
Field 1,Field 2,Field 3
YYYYMMDD-XXXX,"10 Somestreet
NNNNNN Somecity","1,a"
YYYYMMDD-YYYY,"20 Otherstreet
MMMMMM Somecity","1,b"
YYYYMMDD-ZZZZ,"25 Bumble Rd
KKKKKK Village","2,c"