Replacing a negative value by "0" on a column

Hello,

I have the following csv where I would like to replace negative values by "0"

and I would like to replace it only on the 4 column.

Can someone guide me, how to do it?

I found several topics here, but none where I can replace it only on the 4th column.

Best regards

Daniel

welcome to the community, @Damian73.
Please re-post your sample data as a PLAIN text, not using HTML table.
The code/data samples should be properly formated using markdown code tags.
You say you have a CSV file, but I don't see any C (Commas) in your CSV.
Please edit your original post with the proper formatting.
E.g. (but I don't know your data) your data might look like this - using proper formatting and CSV definition:

4061815000001,510700,0,79,N
4061815000001,520237,0,-2,N
4061815000001,520227,0,5,N
4061815000001,520220,0,20,N
4061815000001,520231,0,6,N
4061815000001,520228,0,18,N
4061815000001,520230,0,-21,N
4061815000001,520238,0,-3,N

@Damian73 , hi, have you made any attempt at this (the forum is primarily a collaboration - you present your challenge(s) along with yours and the team come back with suggestions (which may include working solutions !) ). As a challenge, this should be relatively easy on the how-to chart, there's many similar requests already answered in the forum archive - search.

come back with attempts - show workings and results, this is be best way to help the team help you.

Hi,

yes it is a valid csv file, as seperator I need to use Tabublator. (Tabulator is requested by receiver of the export).

That's the reason why it looks like that.

KR

Daniel

What I tried for instance is:

awk -F"\t" "$4<0{$4=0}1" Export.txt > ohneNegativ.txt

But then all values in column 4 are "0"

KR

Daniel

well, i get this when running yours ....

awk -F"\t" "$4<0{$4=0}1" Export.txt 
awk: cmd. line:1: <0{=0}1
awk: cmd. line:1: ^ syntax error

try

awk '{$4=$4<0?0:$4}1' OFS='\t' Export.txt

The final 0 in "$4<0{$4=0}0" is a Boolean which determines whether the line will be output. This code (as shown) will output a zero-length file. (That zero seems to have magically become a 1 since I first looked at this post.)

As least, it would if it did not have also a syntax error:

$ awk -F"\t" "$4<0{$4=0}0" foog
awk: cmd. line:1: <0{=0}0
awk: cmd. line:1: ^ syntax error

The shell expands things in double quotes, so $4 is set to the fourth shell argument to the command, which does not exist. Awk syntax needs to be enclosed in single quotes to avoid this.

$ awk -F"\t" '$4 < 0 { $4 = 0 } 1' foog | cat -vet
4061815000001^I510700^I0^I79^IN$
4061815000001 520237 0 0 N$
4061815000001^I520227^I0^I5^IN$
4061815000001^I520220^I0^I20^IN$
4061815000001^I520231^I0^I6^IN$
4061815000001^I520228^I0^I18^IN$
4061815000001 520230 0 0 N$
4061815000001 520238 0 0 N$

Now the unchanged lines stay tab-separated (shown as ^I), but the altered lines are space-separated. That's not good.

If you are dealing with fields, you need to define the OFS (output field separator), as well as -F which only sets the input field separator.

$ awk -v OFS=$'\t' -F"\t" '$4 < 0 { $4 = 0 } 1' foog | cat -vet
4061815000001^I510700^I0^I79^IN$
4061815000001^I520237^I0^I0^IN$
4061815000001^I520227^I0^I5^IN$
4061815000001^I520220^I0^I20^IN$
4061815000001^I520231^I0^I6^IN$
4061815000001^I520228^I0^I18^IN$
4061815000001^I520230^I0^I0^IN$
4061815000001^I520238^I0^I0^IN$

mnemonically speaking: " != '

awk 'BEGIN {FS=OFS=","} $4 < 0 {$4="0"} 1' yourfile.csv > output.csv

For a "not AWK" solution which might be easier for some "not AWK" people to read, try ruby:

Your Data (cat damian73.csv)

061815000001,510700,0,79,N
4061815000001,520237,0,-2,N
4061815000001,520227,0,5,N
4061815000001,520220,0,20,N
4061815000001,520231,0,6,N
4061815000001,520228,0,18,N
4061815000001,520230,0,-21,N
4061815000001,520238,0,-3,N

Code (cat damian73.rb)

clean_data = ""
File.foreach("damian73.csv") { |line| 
    array = line.split(",")
    test = array[3].to_i
    array[3] = 0 if test < 0
    clean_data <<  array.join(",")
}
File.open('damian73_clean.csv', 'w') { |file| file.write(clean_data) }

Your Clean Data (cat damian73_clean.csv)

4061815000001,510700,0,79,N
4061815000001,520237,0,0,N
4061815000001,520227,0,5,N
4061815000001,520220,0,20,N
4061815000001,520231,0,6,N
4061815000001,520228,0,18,N
4061815000001,520230,0,0,N
4061815000001,520238,0,0,N

Not trying to make it short, just making it clear, step-by-step, using another approach (one of countless approaches).

Computing is fun. Enjoy!

Sorry guys,

what I totally forgot to mention is, that I need to run that under Windows and I'm using "gawk" from the GNUUtils.

When I try to use singele quotes, I always run into a failure.

Hope that helps, to help me.

KR

Daniel

Show your workings and associated errors.

I put the awk into a script, try that (I don't have windows so can't test), there's NO single quotes !

cat dos.awk
BEGIN{OFS="\t"} {$4=$4<0?0:$4}1

file DOS.txt 
DOS.txt: ASCII text, with CRLF line terminators

gawk -f dos.awk DOS.txt  > DOS.out

file DOS.out
DOS.out: ASCII text, with CRLF line terminators

Not trying to convince you to use Ruby of course, but you can easily run Ruby on Windows.

I have a client who runs their entire back office accounting system using Ruby on Rails on a Windows server platform.

It's trivial to install Ruby on Windows.

I've been using Ruby for many years now and it's become my "goto" scripting language on Linux and macOS.

Of course, use gawk if that pleases you

(g)awk:
{$4=$4<0?0:$4}1
does $4=$4 assignments. I'd prefer
$4<0{$4=0}1