0

I have CSV data with two price columns. If a value exists in the $4 column I want to copy it over the $3 column of the same row. If $4 is empty then $3 should be left as is.

Neither of these work:

awk -F',' '{ if (length($4) == 0) $3=$4 }'
awk -F',' '{ if(!length($4) == 0 ) print $4 }'

This will output every line with the sample table

awk -F',' '{ if(!length($4) == 0 ) print $0 }' inputfile

This will output nothing with the sample table

awk -F',' '{ if(length($4) == 0 ) print $3 }' inputfile

I've cleaned my two input files, fixed the header row, and joined them using sed, awk, sort, and join. Now what I am left with is a CSV which looks like this:

itemnumber,available,regprice,mapprice
00061,9,19.30,
00061030,31,2.87,3.19
00062,9,15.44,
00062410,2,3.59,3.99
00064,9,15.44,
00066850,29,2.87,3.99
00066871,49,4.19,5.99
00066878,3,5.63,7.99

I need to overwrite the $3 column if the $4 column in the same row has a value. The end result would be:

itemnumber,available,regprice,mapprice
00061,9,19.30,
00061030,31,3.19,3.19
00062,9,15.44,
00062410,2,3.99,3.99
00064,9,15.44,
00066850,29,3.99,3.99
00066871,49,5.99,5.99
00066878,3,7.99,7.99
John Kugelman
  • 349,597
  • 67
  • 533
  • 578
Pyralix
  • 41
  • 10

4 Answers4

2
$ awk 'BEGIN{FS=OFS=","} (NR>1) && ($4!=""){$3=$4} 1' file
itemnumber,available,regprice,mapprice
00061,9,19.30,
00061030,31,3.19,3.19
00062,9,15.44,
00062410,2,3.99,3.99
00064,9,15.44,
00066850,29,3.99,3.99
00066871,49,5.99,5.99
00066878,3,7.99,7.99
Ed Morton
  • 188,023
  • 17
  • 78
  • 185
  • `awk 'BEGIN{FS=OFS=","} (NR>1) && ($4!=""){$3=$4} 1' file` gave me ,0061,9, ,3.19030,31,3.19 ,0062,9, ,3.99410,2,3.99 ,0064,9, ,3.99850,29,3.99 ,5.99871,49,5.99 ,7.99878,3,7.99 ,4.99070,39,4.99 Any suggestions? – Pyralix Apr 23 '19 at 02:03
  • You can't put formatted text in a comment. I assume there's something about the output that's not what you expected - what is it? As you can see in my answer the script produces the output you asked for given the input you posted and the script will work in all awks in all shells on all UNIX boxes. – Ed Morton Apr 23 '19 at 02:41
  • 1
    FWIW my crystal ball is suggesting you might have DOS line-endings in your files since the script is trivial, robust and portable and your data is very simple so I can't imagine what else could be wrong unless your real data is somehow wildly different from your posted sample data or you copy/pasted the script wrong. See https://stackoverflow.com/a/45772568/1745001 for information on that. – Ed Morton Apr 23 '19 at 02:51
1

Let's have a look at all the things you tried:

  1. awk -F',' '{ if (length($4) == 0) $3=$4 }'

    This states, if the length if field 4 is zero then set field 3 equal to field 4. You do not ask awk to print anything, so it will not do anything. This would have printed something: 

    awk -F',' '{ if (length($4) == 0) $3=$4 }{print $0}'

    but with all field separators equal to a space, you should have done:

    awk 'BEGIN{FS=OFS=","}{ if (length($4) == 0) $3=$4 }{print $0}'

  2. awk -F',' '{ if(!length($4) == 0 ) print $4 }'

    Here you state, if the length of field 4 equals zero is not true, print field 4. As you mention that nothing is printed, it most likely indicates that you have hidden characters in field 4, such as a CR (See: Remove carriage return in Unix), or even just spaces. You could attempt something like

    awk -F',' '{sub(/ *\r?$/,""){ if(!length($4) == 0 ) print $4 }'`**

  3. awk -F',' '{ if(!length($4) == 0 ) print $0 }' inputfile

    See 2

  4. awk -F',' '{ if(length($4) == 0 ) print $3 }' inputfile

    This confirms my suspicion of 2

My solution for your problem would be based on the suggestion of 2 and the solution of Ed Morton.

awk 'BEGIN{FS=OFS=","} {sub(/ *\r?/,"")}(NR>1) && ($4!=""){$3=$4} 1' file
kvantour
  • 25,269
  • 4
  • 47
  • 72
0

Here's code that matches your results:

awk -F, -v OFS=, '
  NR == 1 
  NR >  1 { 
    if ( $4 == "" ) 
      print $1,$2,$3,$4 
    else 
      print $1,$2,$4,$4 } 
' $*

I've run into trouble in the past with expressions like $3 = $4, so I just print out all of the fields.

Edit: I got shamed by Ed Morton for avoiding the $3 = $4 without troubleshooting. I gave it another shot here below:

awk -F, -v OFS=, '
  NR == 1 
  NR >  1 { 
    if ( $4 != "" ) 
      $3 = $4
    print 
  }
' $*

The above achieves the same results.

Mark
  • 4,249
  • 1
  • 18
  • 27
0

tried on gnu awk

awk -F, -vOFS=, '/[0-9.]+/{if($4)$3=$4} {print}' file
  • That would fail if `$4` was populated with value `0` or `0.00` or similar. Also `/.*[0-9.]+/` is equivalent to just `/[0-9.]/` in that context (which btw means it'd match a line with no digits but a `.` which you probably aren't trying for). – Ed Morton Apr 23 '19 at 13:38