1

I am sorry if my question is more a typographical error, but I have been trying to sort this out for a while now and sadly, I cannot get this to work. Perhaps I should use the sed command, but I haven't figured out how to specify a column in sed and despite being a beginner, I have a bit more experience with the awk command.

So here is the goal; I have a CSV file, file1, that has a column (14) where some of the rows have null (blank) values, while other rows have values. I still want all the other columns in the output, but just to change the blank (empty [null]) columns in column 14 to have a new value of NA.

Example:

Column14
Value1
Value2

Value3

I am trying to use the awk command to locate any blank row in column 14 and if found enter a new text value of NA to the cell.

Here is the command I was trying, but my new file still has blank cells in column 14. I would appreciate any help. Thank you.

Command:

awk -F"," 'BEGIN {OFS=","} $14 == "" { $14 = "NA" }  {print}' file1 > file2


GOAL:

Example:

Column14
Value1
Value2
NA
Value3

Thank you all for taking the time to read and assist.

UPDATE

As requested, here is some sample data.

"employee_number","employee_login","is_active","send_pkg_email","send_na_email","last_name","first_name","department","title","phone_number","employee_type","email","charge_code","area_code","mailstop","roomid"
"103293","[email protected]","Y","","","Smith","Jessica","","","+1 (650) 3530975","Employee","[email protected]","","LOC0028.03","","03.C.01H"
"103295","[email protected]","Y","","","Long","Fred","","","+1 (415) 9449428","Employee","[email protected]","","LOC0025.01","","01.D.04B"
"103297","[email protected]","Y","","","Cheng","Laura","","","+1 (650) 8623342","Contingent","[email protected]","","","",""
"103307","[email protected]","Y","","","Brown","Chris","","","+1 (512) 9644927","Employee","[email protected]","","ATX0607.16","","16.B.10D"
"103310","[email protected]","Y","","","Williams","Stan","","","+1 (650) 8048591","Employee","[email protected]","","LOC0061.03","","03.D.01B"
4
  • 2
    Please edit your question and show some sample data that actually has (at least) 14 columns with both empty and non-empty values and that reproduces your problem. Check if your input file has rows that contain whitespace only in column 14.
    – Bodo
    Commented Sep 25, 2019 at 16:55
  • Your awk script works for me.
    – suspectus
    Commented Sep 25, 2019 at 17:03
  • @Bodo I added the sample data you requested. When I run my command my output file still shows the blank cells as blank "".
    – Jesse
    Commented Sep 25, 2019 at 21:35
  • Unlike a lot of "CSV" questions here, your input file is properly formatted CSV so you should probably use a real CSV parser to work with it (just splitting fields on commas isn't good enough). See Is there a robust command line tool for processing csv files? - personally, i'd use either csvkit or perl's Text::CSV module. miller is pretty good too.
    – cas
    Commented Sep 26, 2019 at 2:41

2 Answers 2

1
$ perl -MText::CSV=csv -e '
  $csv = Text::CSV->new();
  while(my $row = $csv->getline(ARGV)) {
    $row->[13] = "NA" if ($row->[13] eq "");
    $csv->say(STDOUT, $row);
  };' input.csv

Note that perl arrays begin from 0, not 1 - so the 14th field is element 13 of the $row arrrayref.

employee_number,employee_login,is_active,send_pkg_email,send_na_email,last_name,first_name,department,title,phone_number,employee_type,email,charge_code,area_code,mailstop,roomid
103293,[email protected],Y,,,Smith,Jessica,,,"+1 (650) 3530975",Employee,[email protected],,LOC0028.03,,03.C.01H
103295,[email protected],Y,,,Long,Fred,,,"+1 (415) 9449428",Employee,[email protected],,LOC0025.01,,01.D.04B
103297,[email protected],Y,,,Cheng,Laura,,,"+1 (650) 8623342",Contingent,[email protected],,NA,,
103307,[email protected],Y,,,Brown,Chris,,,"+1 (512) 9644927",Employee,[email protected],,ATX0607.16,,16.B.10D
103310,[email protected],Y,,,Williams,Stan,,,"+1 (650) 8048591",Employee,[email protected],,LOC0061.03,,03.D.01B

The line with employee_number 103297 now has NA in the 14th field.

BTW, the output fields here are double-quoted only when necessary (e.g. when they contain a space. or if any of them contained a comma, they'd be quoted too). If you prefer all fields in the output to be quoted as in your input file, change the $csv = Text::CSV->new(); line to:

$csv = Text::CSV->new({always_quote => 1});

Text::CSV has numerous other options. e.g. if you use $csv = Text::CSV->new({always_quote => 1, strict => 1}); it will also trigger an error if any of the input rows have a different number of fields. See man Text::CSV for details.


Alternatively, there's a simple fix to your awk script:

awk -F"," 'BEGIN {OFS=","}; $14 == "\"\"" { $14 = "\"NA\"" };1' input.csv

This highlights a problem with just comma-splitting CSV files. It's impossible to distinguish between " characters as wrappers around field data and " characters being part of the field data...because there is no such distinction with this simple split method.

Field 14 isn't empty when you're just splitting the input line by commas. It contains two quote characters ("").

This awk one-liner will also break if any of the fields contain a comma character. That's another reason why it's better to use a CSV parser.

See Is there a robust command line tool for processing csv files?.

There's also a good awk csv parser at https://github.com/geoffroy-aubry/awk-csv-parser

3
  • Because my reputation is under 15, I cannot up-vote this, but this worked for me. I see your points around using a proper CSV parser and I will look into this as well. I can confirm the AWK works for me and I see my mistake in that I needed with the "" characters. Thank you very much for your assistance with this.
    – Jesse
    Commented Sep 26, 2019 at 18:36
  • @jesse i made a minor fix to the script. the previous version would modify field 14 if it was empty or if it contained 0. now it only modifies it if it is empty.
    – cas
    Commented Sep 27, 2019 at 0:09
  • Okay wonderful. Thank you for revisiting and making the adjustment @cas.
    – Jesse
    Commented Sep 28, 2019 at 1:17
1

Column 14 does not contain an empty string, but "", so you need to check for two quotes (which have to be escaped):

awk -F"," 'BEGIN {OFS=","} $14 == "\"\"" { $14 = "NA" } {print}' file1 > file2
1
  • Thank you for contributing. Between yours and the comments from @cas, I was able to get this working. Very much appreciated.
    – Jesse
    Commented Sep 26, 2019 at 18:36

You must log in to answer this question.

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