Replace column values in a CSV file with awk

Question

I have this file

error.log

[00:00:00.284],501,

[00:00:00.417],5,5294100071980

[00:00:02.463],501,

[00:00:05.169],501,

[00:00:05.529],501,

[00:00:05.730],501,

so, if the field $3 its empty i want to print "No value"

Im trying this code

awk '{{FS=","} if($3=="") {print $1,$2,"No value"}}'

But it prints

>[00:00:00.284] 501 No value
>[00:00:02.463] 501 No value
>[00:00:05.169] 501 No value
>[00:00:05.529] 501 No value
>[00:00:05.730] 501 No value
>[00:00:07.193] 501 No value
>[00:00:09.899] 501 No value
>[00:00:31.312] 501 No value

Do you REALLY have blank lines between each real data line in your input file? — Ed Morton
– Ed Morton, Commented Feb 8, 2014 at 5:05

mklement0 · Accepted Answer · 2014-02-08 05:09:32Z

11

awk -F ',' -v OFS=',' '$1 { if ($3=="") $3="No value"; print}' in.txt

Passes the field separator via the -F option.
Variable OFS, the output-field separator, is set to ,, so that output fields are also separated by ,.
Pattern $1 ensures that only non-empty lines are processed (that is, the associated action is only executed if the first field is non-empty) - if your input file has no empty lines, you can remove this pattern.
If the 3rd field is empty, it is assigned string "No value"
Finally, the line (with the potentially modified 3rd field) is output.

The above is how I suggest you approach the problem, but here are the problems with your original command:

{{FS=","}... Inside your single action - which due to not having a preceding pattern is executed for every input line - you set variable FS for every line - which is not only unnecessary but too late, because the first input line has already been parsed by that time (thanks, @EdMorton) - either set it in a BEGIN block (BEGIN { FS="," }) or, as in my answer, with command-line option -F (-F ',').
if($3=="") {...}
You only produce output if field $3 is empty - presumably, though, you want to output all lines, so with this approach you'd need an else branch (to print unmodified lines).
print $1,$2,"No value"
The , chars. here are part of the syntax - they simply separate the arguments passed to print. Given separate arguments, print concatenates them with the value of the special OFS variable, whose value is a single space by default; to use , instead, you have to assign it to OFS - again, either in a BEGIN block or via the -v option (-v OFS=',').

edited Feb 8, 2014 at 5:09

answered Feb 8, 2014 at 3:21

mklement0

453k68 gold badges728 silver badges988 bronze badges

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

2 Comments

Ed Morton Over a year ago

{FS=","}... [not a problem functionally, but inefficient] IS a problem as it won't be executed until after the first line of the input file is split into fields using the defaults FS.

Ed Morton Over a year ago

We can tell from the posted input that $3 can be numeric, and we know he wants to print "No value" when it's empty, but he doesn't say that it can't have the numeric value 0 so testing !$3 is not the same as testing for "empty", that'd be $3 == "".

Jotne · Accepted Answer · 2014-02-08 07:43:14Z

3

With this file

cat file
[00:00:00.284],501,
[00:00:00.417],5,5294100071980
[00:00:02.463],501,
[00:00:05.169],501,
[00:00:05.529],501,
[00:00:05.730],501,

This awk should do

awk -F, '$3=="" {$3="No value"}1' OFS=, file
[00:00:00.284],501,No value
[00:00:00.417],5,5294100071980
[00:00:02.463],501,No value
[00:00:05.169],501,No value
[00:00:05.529],501,No value
[00:00:05.730],501,No value

answered Feb 8, 2014 at 7:43

Jotne

41.7k13 gold badges54 silver badges58 bronze badges

2 Comments

mklement0 Over a year ago

+1 for concision; I'm curious as to why you chose the pseudo-filename form OFS=, over the option form -v OFS=, - just typing convenience? The semantics are subtly different, though in this case they work out the same. (Also, you assume there are no blank lines - which is probably a fair assumption.)

Jotne Over a year ago

Just to save three characters :) -v no other reason.

Ed Morton · Accepted Answer · 2014-02-08 05:09:42Z

2

You should post some expected output but I THINK what you want is:

awk 'BEGIN{FS=OFS=","} NF{print $1, $2, ($3=="" ? "No value" : $3)}' file

answered Feb 8, 2014 at 5:09

Ed Morton

209k18 gold badges90 silver badges212 bronze badges

1 Comment

mklement0 Over a year ago

+1 for elegance. (The only thing to watch out for - IF generalization were needed - is that the solution is limited to 3 output columns).

Collectives™ on Stack Overflow

Replace column values in a CSV file with awk

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related