2

I would like to replace the contents of the 3rd column of a .CSV file (using awk) by looking for a specific string and replace with another string that has a single quote and copies the output of that into another file. Any advice on what am I doing wrong?

For instance:

column1, coluumn2, coulumn3, column4, column5
1, item1, WALMART, 2.39, 50
2, item2, TARGET, 4.99, 52
3, item3, SAMS CLUB, 8.19, 15
4, item4, KROGER, 12.49, 33
5, item6, WEGMANS, 32.69, 75
6, item6, TARGET, 12.99, 25
7, item7, SAMS CLUB, 8.19, 92

I tried using the below awk but doesn't work:

awk '{gsub("SAMS CLUB","SAM\'S CLUB",$3);print}' filename1 > filename2
5

You need to set the field separator (FS) properly. By default, awk uses any horizontal whitespace(s) as the field separator, so in your case SAMS becomes one field and CLUB becomes another. Hence, {gsub("SAMS CLUB","SAM\'S CLUB",$3);print} is not working expectedly.

You can do:

awk -F ', +' '{gsub("SAMS CLUB","SAM'\''S CLUB",$3); print}' OFS=", " file.txt
  • -F ', +' sets FS as comma, followed by one or more space(s). If you are unsure about the spaces, use character class [:blank:] instead to represent any horizontal whitespace and change OFS to meet your need as well.

Example:

% cat file.txt                                                                      
column1, coluumn2, coulumn3, column4, column5
1, item1, WALMART, 2.39, 50
2, item2, TARGET, 4.99, 52
3, item3, SAMS CLUB, 8.19, 15
4, item4, KROGER, 12.49, 33
5, item6, WEGMANS, 32.69, 75
6, item6, TARGET, 12.99, 25
7, item7, SAMS CLUB, 8.19, 92

% awk -F ',[[:blank:]]+' '{gsub("SAMS CLUB","SAM'\''S CLUB",$3); print}' OFS=", " file.txt
column1, coluumn2, coulumn3, column4, column5
1, item1, WALMART, 2.39, 50
2, item2, TARGET, 4.99, 52
3, item3, SAM'S CLUB, 8.19, 15
4, item4, KROGER, 12.49, 33
5, item6, WEGMANS, 32.69, 75
6, item6, TARGET, 12.99, 25
7, item7, SAM'S CLUB, 8.19, 92
2
awk -F, '{gsub("SAMS CLUB","SAM'\''S CLUB",$3);print}' filename1 > filename2

You were close -- just missing two pieces:

  1. telling awk to split the input into fields based on commas (-F,), and
  2. getting the single-quote into the replacement text

Because the awk script is surrounded by single-quotes, one way to do it is to end the single-quoted text, insert an (escaped) single quote, then resume the single-quoted text. Another way is to use variables:

awk -F, -v old="SAMS CLUB" -v new="SAM'S CLUB" '{gsub(old,new,$3);print}' filename1 > filename2

Yet another way would be to use the ENVIRON array variable.

old="SAMS CLUB"
new="SAM'S CLUB"
export old new
awk -F, '{gsub(ENVIRON["old"], ENVIRON["new"], $3);print}'  

Yet another would be to save the awk-script into a file, and call it with:

awk -F, -f awk-script-filename filename1 > filename2

Using a script file would allow you to simplify the quoting:

{gsub("SAMS CLUB","SAM'S CLUB",$3);print}
0

Another short awk solution:

awk -F, '$3~"SAMS CLUB"{sub("S ","\047S ",$3)}1' OFS=',' filename1 > filename2

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.