1

I have two csv files ,let's say A and B . I realized that I have accidentally mixed up things and now in order to undo the mistake I want to write a shell script such that the data in third column of file A to be written in the fourth column of file B and data in fourth column of file B to be written in third column of file A. I know that "cut" command can be used to fetch data from specific columns but I don't know what command to use so as to write on a csv file after a particular number of occurrences of the delimiter on a single row.

How to write such a script?

  • 4
    it would be better if you provide us some sample input file and expected output. – Rahul Jul 31 '16 at 18:04
  • @Rahul : let's say file A is supposed to be of format : [ field1, field2 ,field3 ,field 4] and file B : [ fieldX,fieldY,fieldZ,fieldV] and accidentally file A has become [field1,field2,fieldV,field4] and file B has become [fieldX,fieldY,fieldZ,field3]. – private ryan Jul 31 '16 at 18:21
  • You're probably looking for the paste command, plus shell process substitution to assemble the cut pieces - something like paste -d, <(cut ... ) <(cut ... ) <(cut ...) – steeldriver Jul 31 '16 at 18:33
  • @Theophrastus : It's not a homework assignment ...old enough to go to school :P – private ryan Jul 31 '16 at 18:38
  • 1
    @privateryan: This question by nature requires that you put a sample input and output for us to be more clear. Anyways I have a solution that might help. Please do gimme a f/b. – sjsam Jul 31 '16 at 18:48
2

Given

A.csv:
1,2,V,4

B.csv:
X,Y,Z,3

Then

paste -d, <(cut -d, -f1,2 A.csv) <(cut -d, -f4 B.csv) <(cut -d, -f4 A.csv)
1,2,3,4
  • Replace cut -d, -f by csvcut -c and paste -d, by csvjoin, both from csvkit, and you actually have an answer that work with CSV files. :) – Satō Katsura Aug 2 '16 at 15:45
1

awk is your friend

Make a copy of fileA to say fileA.bak & fileB to say fileB.bak :

#first changing fileB
awk -v FS="," 'NR==FNR{fileA[i++]=$3;next}{$4=fileA[j++];print}' fileA.bak fileB.bak > fileB 
#fileB should be Okay now.
#now changing fileA
awk -v FS="," 'NR==FNR{fileB[i++]=$4;next}{$3=fileA[j++];print}' fileB.bak fileA.bak > fileA
#fileA should be Okay now.
#verify that you have the desired contents in both fileA & fileB
#Now delete the backup files
rm fileA.bak fileB.bak

Notes
1. It is possible to combine both the awk scripts but why dividing them makes things clearer and more readable.
2. This solution assumes that you've same number of records in fileA and fileB as understood from the question.

  • If the fields contain quoted commas, awk isn't your friend anymore. – Thomas Dickey Jul 31 '16 at 18:53
  • @ThomasDickey : You're right, but I hope that'd be a corner case :) . This was more a blind shot as the op didn't put any sample input. – sjsam Jul 31 '16 at 18:55
  • @sjsam : I am yet to learn awk ..could you write something simpler ,like,cut command .if in case it is possible. – private ryan Jul 31 '16 at 18:58
  • @privateryan : I think the other answer gave you a cut N paste solution already. This one is also similar to that provided you know a bit about awk – sjsam Jul 31 '16 at 19:03
  • @ThomasDickey You can still mend your friendship with awk with the help of this tiny program: csvquote. Very useful little helper. – Satō Katsura Aug 2 '16 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.