The following bash script can be used to fill empty cells within a CSV file. We assume that your file is evenly distributed so that it contains same number of columns in each row separated by comma. If your file is TABseparated use a following linux command to convert it to comma separated value file before you proceed.
Example:
$ cat test 1 2 4 4 2 3 3 3 $ sed 's/\t/,/g' test 1,2,4,4 2,3,3,3 OR $ cat test | tr '\t' ',' 1,2,4,4 2,3,3,3
Here is a content of our CSV file which contains empty value cells.
$ cat myfile.csv 1,2,3,4,5,6,7 ,,,,,, 1,,,4,5,, ,2,3,4,5,,
To fill all empty values within the above CSV file we can use the following bash script:
#!/bin/bash for i in $( seq 1 2); do sed -e "s/^,/$2,/" -e "s/,,/,$2,/g" -e "s/,$/,$2/" -i $1 done
Copy the above lines into a file eg. fill-empty-values.sh. The script accepts two arguments. The first argument it takes is the file you wish to replace all empty values in and the second argument is a string or number you wish the empty values to be replaced with. To following linux command will replace all empty cells within the above comma separated value file myfile.csv.
The replacing character to put into each empty cell will be 0:
$ bash fill-empty-values.sh myfile.csv 0
myfile.csv file has been modified and all empty values have been replaced with 0:
$ cat myfile.csv 1,2,3,4,5,6,7 0,0,0,0,0,0,0 1,0,0,4,5,0,0 0,2,3,4,5,0,0
