How to copy specific columns from one csv file to another csv file?

Question

File1.csv:

File2.csv:

I want to replace the contents of configSku,selectedSku,config_id in File1.csv with the contents of configSku,selectedSku,config_idfrom File2.csv. The end result should look like this:

Here are the links to download the files so you can try it yourself:

File1.csv: https://www.dropbox.com/s/2o12qjzqlcgotxr/file1.csv?dl=0
File2.csv: https://www.dropbox.com/s/331lpqlvaaoljil/file2.csv?dl=0

Here's what I have tried but still failed:

#!/bin/bash

INPUT=/tmp/file2.csv
OLDIFS=$IFS
IFS=,

[ ! -f $INPUT ] && { echo "$INPUT file not found"; exit 99; }
echo "no,my_account,form_token,fingerprint,configSku,selectedSku,config_id,address1,item_title" > /tmp/temp.csv
while read item_title configSku selectedSku config_id
do
    cat /tmp/file1.csv | 
    awk -F ',' -v item_title="$item_title" \
    -v configSku="$configSku" \
    -v selectedSku="$selectedSku" \
    -v config_id="$config_id" \
    -v OFS=',' 'NR>1{$5=configSku; $6=selectedSku; $7=config_id; $9=item_title; print}' >> /tmp/temp.csv
done < <(tail -n +2 "$INPUT")
IFS=$OLDIFS

How do I do this ?

Please post sample Input files in code tags rather than posting images or links. — RavinderSingh13
– RavinderSingh13, Commented Feb 26, 2018 at 11:36
minimal reproducible example We aren't interested in Tokens like NEWconfigSKu4 or the like, you could post "abc" as example, to keep it brief. — user unknown
– user unknown, Commented Feb 27, 2018 at 1:09

Vinicius Placco · Accepted Answer · 2018-02-26 18:09:53Z

3

If I understood the question correctly, how about using:

paste -d, file1.csv file2.csv | awk -F, -v OFS=',' '{print $1,$2,$3,$4,$11,$12,$13,$8,$10}'

This is not as nearly as robust as the other answer, and assumes that file1.csv and file2.csv have the same number of lines and each line in one file corresponds to the same line on the other file. the output would look like this:

no,my_account,form_token,fingerprint,configSku,selectedSku,config_id,address1,item_title
1,account1,asdf234safd,sd4d5s6sa,NEWconfigSku1,NEWselectedSku1,NEWconfig_id1,myaddr1,Samsung Handsfree
2,account2,asdf234safd,sd4d5s6sa,NEWconfigSku2,NEWselectedSku2,NEWconfig_id2,myaddr2,Xiaomi Mi headset
3,account3,asdf234safd,sd4d5s6sa,NEWconfigSku3,NEWselectedSku3,NEWconfig_id3,myaddr3,Ear Headphones with Mic
4,account4,asdf234safd,sd4d5s6sa,NEWconfigSku4,NEWselectedSku4,NEWconfig_id4,myaddr4,Handsfree/Headset

The first part is using paste to put the files side-by-side, separated by comma, hence the -d option. Then, you end up with a combined file with 13 columns. The awk part first tells that the input and output field separators should be comma (-F,and -v OFS=',', respectively) and then prints the desired columns (columns 1-4 from first file, then columns 2-4 of the second file, which now correspond to columns 11-13 on the merged file.

edited Feb 26, 2018 at 18:09

answered Feb 26, 2018 at 15:02

Vinicius Placco

1,7412 gold badges17 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joe Over a year ago

I can't believe that actually worked, can you please explain what this line does {print $1,$2,$3,$4,$11,$12,$13,$8,$10} ?

Vinicius Placco Over a year ago

I added an explanation to the answer. Hope is clear enough!

silel · Accepted Answer · 2018-02-27 00:50:09Z

The main issue in your original script is that you are reading one file (/tmp/file2.csv) one line at a time, and for each line, your parse and print the whole other file (/tmp/file1.csv).

Here is an example how to merge two csv files in bash:

#!/bin/bash

# Open both files in "reading mode"
exec 3<"$1"
exec 4<"$2"

# Read(/discard) the header line in both csv files
read -r -u 3
read -r -u 4

# Print the new header line
printf "your,own,header,line\n"

# Read both files one line at a time and print the merged result
while true; do
    IFS="," read -r -u 3 your own || break
    IFS="," read -r -u 4 header line
    printf "%s,%s,%s,%s\n" "$your" "$own" "$header" "$line"
done

exec 3<&-
exec 4<&-

Assuming you saved the script above in "merge_csv.sh", you can use it like this:

$ bash merge_csv.sh /tmp/file1.csv /tmp/file2.csv > /tmp/temp.csv

Be sure to modify the script to suit your needs (I did not use the headers you provided in your question).

If you are not familiar with the exec command, the tldp documentation and the bash hackers wiki both have an entry about it. The man page for read should document the -u option well enough. Finally, the VAR="something" command arg1 arg2 (used in the script for IFS=',' read -u -r 3) is a common construct in shell scripting. If you are not familiar with it, I believe this answer should provide enough information on what it does.

Note: if you want to do more complex processing of csv files I recommend using python and its csv package.

Collectives™ on Stack Overflow

How to copy specific columns from one csv file to another csv file?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related