Replace multiple value of csv file

Question

I have a csv file :

1,1,1,2
2,2,1,2
3,3,1,2
4,4,1,2
5,5,1,2
6,6,1,2
7,7,1,2
8,8,1,2
9,9,1,2
10,10,2,2
11,11,2,2
12,12,2,2
13,13,3,2

I want to replace each third value to this : If 1; then 22 If 2; then 35 If 3; then 14

This is what I have made :

awk -F , -v OFS=, '{if ($3=="1") $3="22";if ($3=="2") $3="35";if ($3=="3") $3="14"} {print "\""$1"\""",""\""$2"\""",""\""$3"\""",""\""$4"\""}' /tmp/test.csv

It's work well on Debian but not on Ubuntu. What is the problem ? Thanks you

[EDIT] With the example I cited yesterday, it works , but not with this one : cat -v test.csv

1,1,1,2
2,2,1,2
3,3,1,2
4,4,1,2
5,5,1,2
6,6,1,2
7,7,1,2
8,8,1,2
9,9,1,2
10,10,1,2
11,11,1,2
12,12,1,2
13,13,1,2
14,14,1,2
15,15,1,2
16,16,1,2
17,17,1,2
18,18,1,2
19,19,1,2
20,20,1,2
21,21,1,2
22,22,1,2
23,23,1,2
24,24,1,2
25,25,1,2
26,26,1,2
27,27,1,2
28,28,1,2
29,29,1,2
30,30,1,2
31,31,1,2
32,32,1,2
33,33,1,2
34,34,1,2
35,35,1,2
36,36,1,2
37,37,1,2
38,38,1,2
39,39,1,2
40,40,1,2

And now, the command return :

awk -F , -v OFS=, '{if ($3=="1") $3="2";if ($3=="2") $3="3";if ($3=="3") $3="5"} {print "\""$1"\""",""\""$2"\""",""\""$3"\""",""\""$4"\""}' toast.csv
"1","1","5","2"
"2","2","5","2"
"3","3","5","2"
"4","4","5","2"
"5","5","5","2"
"6","6","5","2"
"7","7","5","2"
"8","8","5","2"
"9","9","5","2"
"10","10","5","2"
"11","11","5","2"
"12","12","5","2"
"13","13","5","2"
"14","14","5","2"
"15","15","5","2"
"16","16","5","2"
"17","17","5","2"
"18","18","5","2"
"19","19","5","2"
"20","20","5","2"
"21","21","5","2"
"22","22","5","2"
"23","23","5","2"
"24","24","5","2"
"25","25","5","2"
"26","26","5","2"
"27","27","5","2"
"28","28","5","2"
"29","29","5","2"
"30","30","5","2"
"31","31","5","2"
"32","32","5","2"
"33","33","5","2"
"34","34","5","2"
"35","35","5","2"
"36","36","5","2"
"37","37","5","2"
"38","38","5","2"
"39","39","5","2"
"40","40","5","2"

All third values are equal to 5 instead of 2. Same issue with this example on Debian.

Command run without error, but it's return me a field (the third) that is different from what I put in the script. I can't show you the correct output because i currently don't have access to my computer. — Kamiz
– Kamiz, Commented Apr 25, 2016 at 15:35
It's strange because I just installed Ubuntu on a virtual machine, and I have no problem ... Anyway, I'll post the output of the command tomorrow — Kamiz
– Kamiz, Commented Apr 25, 2016 at 17:21

Ed Morton · Accepted Answer · 2016-04-26 13:54:52Z

None of the code you have posted will behave differently on any given machine vs any other machine. You saying that it did and posting the wrong code initially was a red herring, you just have buggy code, that's all.

The code you added in your latest edit says:

if ($3=="1") $3="2";if ($3=="2") $3="3";if ($3=="3") $3="5"

So let's say you start with a $3 in your input file that has value 1. Your first test/assignment is if ($3=="1") $3="2" so after that code executes $3 has value 2. Now your second test/assignment is if ($3=="2") $3="3" Well, $3 IS now 2 after your first code segment executes, so now it gets set to 3. And then your next test/assignment sets it to 5.

So given a $3 that is 1 you set $3 to 2, then you set it to 3 then you set it to 5 - net result is it's always 5. Throw in in some "else"s:

if ($3=="1") $3="2"; else if ($3=="2") $3="3"; else if ($3=="3") $3="5"

but at least change your script to avoid having to print each field individually:

awk -F, -v OFS='","' '{if ($3=="1") $3="2"; else if ($3=="2") $3="3"; else if ($3=="3") $3="5"} {print "\""$0"\""}' toast.csv

and consider using a more idiomatic approach:

$ cat file
9,9,1,2
10,10,2,2
13,13,3,2

$ awk -F, -v OFS='","' 'BEGIN{split("2,3,5",m)} {$3=m[$3]} {print "\""$0"\""}' file
"9","9","2","2"
"10","10","3","2"
"13","13","5","2"

The above assume your $3 is always one of the values you show/test for. If not there's easy tweaks.

In general to map one set of arbitrary numbers to another and allow for some input data that doesn't need to get mapped:

$ awk -F, -v OFS='","' 'BEGIN{split("1,2,3",a); split("2,3,5",b); for (i in a) m[a[i]]=b[i]} {$3=($3 in m ? m[$3] : $3)} {print "\""$0"\""}' file
"9","9","2","2"
"10","10","3","2"
"13","13","5","2"

or if you prefer:

$ awk -F, -v OFS='","' 'BEGIN{split("1,2,2,3,3,5",t); for (i=2;i in t;i+=2) m[t[i-1]]=t[i]} {$3=($3 in m ? m[$3] : $3)} {print "\""$0"\""}' file
"9","9","2","2"
"10","10","3","2"
"13","13","5","2"

Mikel · Accepted Answer · 2016-04-25 17:47:51Z

0

It might be easier with sed:

sed 's/\([0-9]*,[0-9]*,\)1\(,[0-9]*\)/\122\2/' /tmp/test.csv
sed 's/\([0-9]*,[0-9]*,\)2\(,[0-9]*\)/\135\2/' /tmp/test.csv
sed 's/\([0-9]*,[0-9]*,\)3\(,[0-9]*\)/\114\2/' /tmp/test.csv

I believe that should do the trick and will most likely work on most sh/bash environments.

EDIT: Note that this just prints out the actual replacements each command does, so you know what is going to happen before you actually change anything. You may want to first back up your file and then do inplace replacements with the -i flag:

$ cat /tmp/test.csv
1,1,1,2
2,2,1,2
3,3,1,2
4,4,1,2
5,5,1,2
6,6,1,2
7,7,1,2
8,8,1,2
9,9,1,2
10,10,2,2
11,11,2,2
12,12,2,2
13,13,3,2
$ cp /tmp/test.csv /tmp/test.csv.bak
$ sed -i 's/\([0-9]*,[0-9]*,\)1\(,[0-9]*\)/\122\2/' /tmp/test.csv
$ sed -i 's/\([0-9]*,[0-9]*,\)2\(,[0-9]*\)/\135\2/' /tmp/test.csv
$ sed -i 's/\([0-9]*,[0-9]*,\)3\(,[0-9]*\)/\114\2/' /tmp/test.csv
$ cat /tmp/test.csv
1,1,22,2
2,2,22,2
3,3,22,2
4,4,22,2
5,5,22,2
6,6,22,2
7,7,22,2
8,8,22,2
9,9,22,2
10,10,35,2
11,11,35,2
12,12,35,2
13,13,14,2

edited Apr 25, 2016 at 17:47

answered Apr 25, 2016 at 15:02

Mikel

92 bronze badges

3 Comments

Ed Morton Over a year ago

No, it will not be easier with sed, it will be more complicated and less efficient as demonstrated by your 3 scripts (which will not produce the desired output btw).

Mikel Over a year ago

@Ed Morton, boy somebody woke up with their panties all ruffled up. Sorry if you don't like the solution, but it does get the job done, and should work on just about any bash environment.

Ed Morton Over a year ago

No ruffling involved, it just doesn't produce the expected output (it's missing the double quotes around every field) and you're wrong in saying it's easier with sed than awk, plus it's less efficient, that's all. One way to write an awk script to do what your sed scripts do in combination would simply be awk 'BEGIN{FS=OFS=","; split("22,35,14",m)} {$3=m[$3]} 1' file and you'd just call it once to do every substitution in one pass of the input file but that doesn't produce the desired output and more importantly it doesn't address the OPs question.

Collectives™ on Stack Overflow

Replace multiple value of csv file

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related