How to take data from 1 csv file, and merge it with another into specific columns from Bash script?

Question

I currently have 2 csv files that I would like to merge together. Both have 5 lines and 8 columns, I need to combine both together to make a total of 16 columns and 5 lines.

CSV 1

01   14   2021   100,000   Q1   SW1   GB1  H8
01   14   2021   500,000   Q2   SW2   GB8  H4
01   14   2021   600,000   Q3   SW3   GB3  H6
01   14   2021   300,000   Q4   SW4   GB2  H2
01   14   2021   200,000   Q5   SW5   GB1  H1

CSV2

LT1   IU48  LO   N9   AS1   UT2   HH10  M2
LT1   IU33   LO   N9   AS7   UT9   HH4  M2
LT1   IU3   LO   N9   AS0   UT8   HH50  M2
LT1   IU49   LO   N8   AS3   UT9   HH74  M2
LT1   IU40  LO   N9   AS0   UT2   HH42  M2

Desired results

01   14   2021   100,000   Q1   SW1   GB1  H8   LT1   IU48  LO   N9   AS1   UT2   HH10  M2
01   14   2021   500,000   Q2   SW2   GB8  H4   LT1   IU33   LO   N9   AS7   UT9   HH4  M2
01   14   2021   600,000   Q3   SW3   GB3  H6   LT1   IU3   LO   N9   AS0   UT8   HH50  M2
01   14   2021   300,000   Q4   SW4   GB2  H2   LT1   IU49   LO   N8   AS3   UT9   HH74  M2
01   14   2021   200,000   Q5   SW5   GB1  H1   LT1   IU40  LO   N9   AS0   UT2   HH42  M2

Code I have tried

paste -d, csv1 > csv2 | awk -F, -v OFS=',' '{print $1,$2,$3,$4,$5,$6,$7,$8}'

awk -F"," '{$9="$1,$2,$3,$4,$5,$6,$7,$8" OFS $9; print}' csv1 > csv2

awk -F"," '{$8=$1,$2,$3,$4,$5,$6,$7,$8 OFS $8; print}' csv1 > csv2

awk -F"," 'BEGIN { OFS = "," } {print $1,$2,$3,$4,$5,$6,$7,$8}' csv1 > csv2

Thank you for your help!!

Kindly do add your efforts in form of code in your question, which is highly encouraged on SO, thank you. — RavinderSingh13, Jan 14 '21 at 13:39
ahh I knew I was forgetting something! I have it updated with the code i've tried. Thank you for the reminder! — newcoder1234, Jan 14 '21 at 13:42
How about using `paste file1.csv file2.csv`? Let me know if this helps you. — RavinderSingh13, Jan 14 '21 at 13:44
Thanks for the response ! Unfortunately, that did not work for me — newcoder1234, Jan 14 '21 at 13:57
With your shown samples looks like it should work, what's happening when you run that paste command, could you please do let me know in details, thank you. — RavinderSingh13, Jan 14 '21 at 13:58
You write "CSV" and use `,` as a field separator, but the input seems to be space or tab separated. — Benjamin W., Jan 14 '21 at 14:04
So it works when I manually run it on the command line, however when I paste the exact line into my script, for some reason its not working. @RavinderSingh13 — newcoder1234, Jan 14 '21 at 14:07
@newcoder1234, could you please check if you have control M characters in your files? Do a `cat -v file1.csv` and `cat -v file2.csv` once? — RavinderSingh13, Jan 14 '21 at 14:08
With that said, this is enough for me to figure the rest out. Thank you ! @RavinderSingh13 — newcoder1234, Jan 14 '21 at 14:09

How to take data from 1 csv file, and merge it with another into specific columns from Bash script?

0 Answers0