append data from one file to anoher file from right

Question

I have two files with below formats:

file1:

Sub_amount , date/time

12  ,    2018040412   
78  ,    2018040413   
26  ,    2018040414

file2:

Unsub_amount , date/time   
76     , 2018040412   
98     , 2018040413   
56     , 2018040414

what I need is, append file2 to file1 from right. what I mean is:

Sub_amount, Unsub_amount , date/time   
12      ,       76      , 2018040412    
78       ,       98      , 2018040413   
26       ,       56      , 2018040414

At the end, what is needed to be shown is:

date/time , Unsub_amount , Sub_amount  
2018040412, 76 , 12  
2018040413, 98 , 78  
2018040414, 26 , 56

I would be appreciate if anyone can support :) Thanks.

What language? What have you tried? – Yserbius Apr 13 '18 at 17:32 — Yserbius, Apr 13 '18 at 17:32
Are the headers part of the file? – glenn jackman Apr 13 '18 at 18:02 — glenn jackman, Apr 13 '18 at 18:02
shell scripting Yserbius / Yes glenn, headers are with file – Smdevfunia Apr 13 '18 at 21:34 — Smdevfunia, Apr 13 '18 at 21:34

score 1 · Answer 1 · answered Apr 13 '18 at 18:54

I would use awk for this:

awk -F'[[:blank:]]*,[[:blank:]]*'  -v OFS="," '
    # remove leading and trailing blanks from the line
    { gsub(/^[[:blank:]]+|[[:blank:]]+$/, "") }

    # skip empty lines
    /^$/ { next }

    # store the Sub values from file1
    NR == FNR { sub_amt[$2] = $1; next }

    # print the data from file2, matching the cached value from file1
    { print $2, $1, sub_amt[$2] }
' file1 file2

date/time,Unsub_amount,Sub_amount
2018040412,76,12
2018040413,98,78
2018040414,56,26

Leonard Pauli · Answer 2 · 2018-04-14T12:02:04.793

edit: Not as cool or well-working as the awk solution, but an alternative. Trims the columns (using sed) and extracts them (using cut), then joins them (using paste). Assumes that the rows match up. Happy coding!

#!/usr/bin/env sh
# copy this code
# pbpaste > csv-col-merge.sh # paste/create the file
# chmod u+x csv-col-merge.sh # make it executable
# ./csv-col-merge.sh --gen-example
# ./csv-col-merge.sh demo/subs.csv demo/unsubs.csv demo/combined.csv
# 
# learn more:
# - "paste": https://askubuntu.com/questions/616166/how-can-i-merge-files-on-a-line-by-line-basis
# - "<<- EOF": https://stackoverflow.com/questions/2953081/how-can-i-write-a-heredoc-to-a-file-in-bash-script
# - "$_": https://unix.stackexchange.com/questions/271659/vs-last-argument-of-the-preceding-command-and-output-redirection
# - "cat -": https://stackoverflow.com/questions/14004756/read-stdin-in-function-in-bash-script
# - "cut -d ',' -f 1": split lines with ',' and take the first column, see "man cut"
# - "sed -E 's/find/replace/g'"; -E for extended regex, for ? (optional) support, see "man sed"
# - 

# --gen-example
if [ "$1" = '--gen-example' ]; then
    mkdir -p demo && cd $_

    cat <<- EOF > subs.csv
    sub_amount, date/time
    12, 2018040412
    78, 2018040413
    26, 2018040414
    EOF

    cat <<- EOF > unsubs.csv
    unsub_amount, date/time
    76, 2018040412
    98, 2018040413
    56, 2018040414
    EOF

    exit
fi

# load
trim () { cat - | sed -E 's/ ?, ?/,/g'; }
subs="$(cat "$1" | trim)"
unsubs="$(cat "$2" | trim)"
combined=$3

# intermediate
getcol () { cut -d ',' -f $1; }
col_subs="$(echo "$subs" | getcol 1)"
col_unsubs="$(echo "$unsubs" | getcol 1)"

col_subs_date="$(echo "$subs" | getcol 2)"
col_unsubs_date="$(echo "$unsubs" | getcol 2)"
if [ ! "$col_subs_date" = "$col_unsubs_date" ]; then echo 'Make sure date col match up'; exit; fi

# process
mkdir tmp
echo "$col_subs_date" > tmp/a
echo "$col_unsubs" > tmp/b
echo "$col_subs" > tmp/c
paste -d ',' < tmp/a tmp/b tmp/c
rm -rf tmp

append data from one file to anoher file from right

2 Answers2