0

I have this csv file separated by semicolon. I want to replace only the first occurrence of 1 with 77 in the 8th field. But the awk command is replacing all 1s with 77 in the 8th field. what do i do?

original csv

1;abc;1;;;;;1;;1;;1;;;;;1;;;1;;
2;def;1;;;;;1;1;1;;;;;1;;;;1;;;
3;ghi;1;;;;1;1;1;1;;1;;1;1;;;;;1;;
4;jkl;1;;;;;1;;1;;1;1;;;1;;;;1;;

I tried this

awk 'BEGIN{FS=OFS=";"} {sub(1, 77, $8)} {print}' ser.csv > tmp.csv

i was expecting this

1;abc;1;;;;;77;;1;;1;;;;;1;;;1;;
2;def;1;;;;;1;1;1;;;;;1;;;;1;;;
3;ghi;1;;;;1;1;1;1;;1;;1;1;;;;;1;;
4;jkl;1;;;;;1;;1;;1;1;;;1;;;;1;;

but i am getting this.

1;abc;1;;;;;77;;1;;1;;;;;1;;;1;;
2;def;1;;;;;77;1;1;;;;;1;;;;1;;;
3;ghi;1;;;;1;77;1;1;;1;;1;1;;;;;1;;
4;jkl;1;;;;;77;;1;;1;1;;;1;;;;1;;
12
  • 2
    Please take a look at stackoverflow.com/help/formatting Commented Dec 27, 2022 at 17:10
  • 2
    Also edit your question to clarify what you mean by first occurrence - tell us if you mean a) first occurrence in each line or b) first occurrence in each file or c) first occurrence across the whole input or d) something else.
    – Ed Morton
    Commented Dec 27, 2022 at 17:20
  • 1
    "Global substitution" means that all possible substitutions are made in the current string (which is the current line if not specified), or the current field (as in your $8), or the specified variable. The gsub() itself (as with all actions) is executed for every input line (unless there are further patterns or tests which avoid gsub() being executed). Commented Dec 27, 2022 at 17:22
  • 1
    @dhm that wouldn't work in a couple of different ways.
    – Ed Morton
    Commented Dec 27, 2022 at 20:18
  • 2
    Note that there is a subtle difference between "the first 1 in the 8th field" (would change 100 in the first record's 8th field into 7700), "the first 8th field that is 1 (would only change 1 but not e.g. 11 or 100, and may not change the first record at all), and "the 8th field of the record whose 1st field is 1" (the record of interest may not be the first one if the data isn't sorted).
    – Kusalananda
    Commented Dec 28, 2022 at 7:54

1 Answer 1

5

Assuming that by "first occurrence" you mean "first occurrence across the whole input", then this is probably what you want using any awk (untested):

awk 'BEGIN{FS=OFS=";"} !f && sub(1, 77, $8){f=1} {print}'

The above replaces the first 1 that occurs in the 8the field across all your input. If instead you want to replace the 8th field the first time it has the value 1 across all your input then that'd be this (or similar):

awk 'BEGIN{FS=OFS=";"} !f && ($8==1){$8=77; f=1} {print}'
8
  • 1
    Thanks mate. This works exactly as i want. Please help me understand what is !f and {f=1} Commented Dec 28, 2022 at 7:36
  • Ok, So it does work but i noticed a new issue. If the number is 12 instead of just 1 the command is replacing the 1 of 12 also with 77. How to make awk search for strictly only 1 and do the replacement. if the column contains lets say . 12, 1 , 1, Then i want only the middle 1 to be replaced and not the 1 of 12. The middle 1 is the first occurrence of 1 as a whole word . Commented Dec 28, 2022 at 8:10
  • 1
    @EmmanuelFernando Use sub(/^1$/, 77, $8). sub() first field is a pattern (regular expression). The ^ and $ anchor the pattern to the start and end (respectively) of the string. Commented Dec 28, 2022 at 9:00
  • @EmmanuelFernando ! f means not f -- it is checking that f has not already been set non-zero. { f = 1 } sets f when a sub has succeeded. Together, this makes a mechanism where matches after the first are disabled. The name f is probably chosen to represent "first". Commented Dec 28, 2022 at 9:05
  • 1
    awk 'BEGIN{FS=OFS=";"} !f && ($8==1){$8=77; f=1} {print}' this one works perfectly. I realized now the difference between input and string. i had unclear ideas about that before. Thanks a lot for guiding me. Commented Dec 29, 2022 at 4:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .