Gfrktyl

I have a csv file that has 7 columns. It has empty cells and some spaces between cells. How can I replace the empty cells with NA and remove extra spaces? Thank you very much!

Here is what my file looks like, but here it seems to warp around when I copy and past it.

130070078,PPW0001,1,4,4HW             ,2,15.61943874

120040039,PPW0002,0,0,                ,0,0

120040043,PPW0003,1,3,3WE             ,1,14.43394935

Using sed that will work for repeated empty fields as well:

sed ':l;s/,,/,NA,/;tl; s/[[:blank:]]*//g'

Or using awk:

awk '{i=0;while(i++<2){gsub(/,,/,",na,");gsub(/ /, "")}}1'

Input:

130070078,PPW0001,1,4,4HW         ,2,15.61943874

120040039,PPW0002,0,0,            ,0,0

120040043,PPW0003,1,3, 3WE       ,1,14.43394935

120040043,PPW0003,1 ,3,3WE         ,1,14.43,,,3,,94,,,,9,,,,,35

120040043,PPW0003,0, 2, 3WE       ,1,14.43,,,3,,94,,,,9,,,,,35

Output:

130070078,PPW0001,1,4,4HW,2,15.61943874

120040039,PPW0002,0,0,,0,0

120040043,PPW0003,1,3,3WE,1,14.43394935

120040043,PPW0003,1,3,3WE,1,14.43,NA,NA,3,NA,94,NA,NA,NA,9,NA,NA,NA,NA,35

120040043,PPW0003,0,2,3WE,1,14.43,NA,NA,3,NA,94,NA,NA,NA,9,NA,NA,NA,NA,35

Your answer:

sed 's/ *,/,/g' file1 | sed 's/,,/,NA,/g' > file2

To get 'NA' in the last field if blank:

sed 's/ *,/,/g' file1 | sed 's/,,/,NA,/g' | awk -F, 'OFS="," {if ($NF == "") $NF = "NA"; print}'> file2

You could also use :

sed 's/,,/,NA,/g' file1 | tr -d ' ' | awk -F, 'OFS="," {if ($NF == "") $NF = "NA"; print}'

αғsнιη's answer worked for me, but I'd just like to explain it a bit.

I was trying something like this:

echo "1,,2,,,3,,,4,,,,5,,,,,,,,,,6" | sed 's/,,/,-,/g'

Which outputs

1,-,2,-,,3,-,,4,-,,-,5,-,,-,,-,,-,,-,6

Because of the repeated empty fields the last comma is part of the first replacement and the start of the next desired replacement, so you just get every second empty field replaced.

Now you could do something like:

echo "1,,2,,,3,,,4,,,,5,,,,,,,,,,6" | sed -e 's/,,/,-,/g' -e 's/,,/,-,/g'

or

sed 's/,,/,-,/g;s/,,/,-,/g'

Which will replace all the cells, as the second command will get the ones that are missed, but it's a bit messy.

αғsнιη's command does essentially the same thing, using a label and a jump, which I was not aware you could do.

sed ':MYLABEL; s/,,/,-,/g; t MYLABEL;'

output:

1,-,2,-,-,3,-,-,4,-,-,-,5,-,-,-,-,-,-,-,-,-,6

So the first part of the command creates a label.

Then we have the same substitution.

Then we have the t command which means jump to label if the previous substitution command was successful.

More information: http://www.grymoire.com/Unix/Sed.html#uh-59