CODEWITHSUNDEEP
bashshellappend
rsicil
rsicil

Reputation: 11

Bash shell scripting - Appending a column of values in a csv file to another existing csv file

I am trying to append one column of data in a csv file (let's call it file2) with another csv file (let's call it file1). csv files are tab delimited.

For example, file1:

Second rateself
1 2
2 4
3 6
4 50
5 44
6 47
7 49
8 50

and file2:

Second rateoth
1 44
2 43
3 37
4 20
5 50
6 55
7 61
8 66

I'm trying to use bash shell scripting to automate and combine date. The desired data:

desired output:

Second rateself rateoth
1 2 44
2 4 43
3 6 37
4 50 20
5 44 50
6 47 55
7 49 61
8 50 66

I've tried a slew of cat, awk, paste, and sed statements. I am new to scripting and do not have all the exact errors from each of my attempts; actually many have not had any errors, but simply not given me my desired output. Generally, the cat, paste, and sed statements have resulted in the data being combined, but by adding additional rows not the desired column. I have been unsuccessful with the rest, where my output file is blank.

tried and unsuccessful:

$join -t file2.csv file1.csv > combined.csv
$printf "%s/n" "file2.csv" | paste -d \t file1.csv > combined.csv
$paste -d <(cut -d \t -f3 file2.csv) file1.csv > combined.csv
$cat file1.csv <(tail -n +2 file2.csv) > combined.csv
$awk -F "\"*,\"*" '{print $1}' file2.csv > file1.csv
$paste -d file1.csv file2.csv > combined.csv
$cat -d file1.csv file2.csv > combined.csv

any solutions? i'm on a MacOS.

Upvotes: 1

Views: 1641

Answers (1)

Zak
Zak

Reputation: 7515

Here is a small script that would take the 2 files and combine them into 1. This assumes you have:

  1. file_1.csv with Second and rateself
  2. file_2.csv with Second and rateoth

#! /bin/bash
echo $'Second\trateself\trateoth' > file_3.csv

while read -r col_1 col_2
    do

    # ignore header line of file 2
    col_plus_1=$(( col_1 + 1 ))

    # read line number col_plus_1 into file_2 variable
    file_2=$(awk "NR == $col_plus_1" file_2.csv)

    # separate the tabbed line into array
    file_2=($file_2)

    # assign array indexed 1 to col_3
    col_3=${file_2[1]}

    # write to final file
    echo $'$col_1\t$col_2\t$col_3' >> file_3.csv

# ignore the header on read in
done < <(tail -n +2 file_1.csv)

Upvotes: 1

Related Questions