Format the file by deleting empty field

Shell Programming and Scripting
1

I have the test data with 10 column separated by comma and each column has more than 1000000 rows. Can anyone help me to find empty field in all columns and delete that empty field alone and lift that column up by one row.

Data with empty field:

A74203XYZ,A21718XYZ,A72011XYZ,A41095XYZ,
A11215WXZ,A41001XYZ,A11050XYZ,         ,
        ,A48515XYZ,A72200RWS,A56009WXZ,
A16406RWS,A24515WXZ,A13955RWS,A13102WXZ,
A27440WXZ,A71176WXZ,A16518RWS,A74607WXZ,
L18692WXZ,A71439RWS,         ,A21795WXZ,
A41420XYZ,A11220WXZ,A13049WXZ,A44577XYZ,
A52734WXZ,A24461RWS,A11458WXZ,A21211XYZ,

Updated data by deleting empty field:-

A74203XYZ,A21718XYZ,A72011XYZ,A41095XYZ,
A11215WXZ,A41001XYZ,A11050XYZ,A56009WXZ,
A16406RWS,A48515XYZ,A72200RWS,A13102WXZ,
A27440WXZ,A24515WXZ,A13955RWS,A74607WXZ,
L18692WXZ,A71176WXZ,A16518RWS,A21795WXZ,
A41420XYZ,A71439RWS,A13049WXZ,A44577XYZ,
A52734WXZ,A11220WXZ,A11458WXZ,A21211XYZ,
2

Hi Zooby

Try this code I think this will work :

#!/bin/ksh
cut -d ',' -f 1 YourFile.txt|tr -s '\n' '' > finalData.tmp
for var in  2 3 4 5 6 7 8 9 10
do
cut -d ',' -f ${var} YourFile.txt|tr -s '\n' '' > data.tmp
paste -d ',' finalData.tmp data.tmp > tmp1.tmp
mv tmp1.tmp finalData.tmp
done
rm data.tmp
4

Try this:

awk -F, '{gsub(" ","")}
$1{print $1  > "temp1"}
$2{print $2  > "temp2"}
$3{print $3  > "temp3"}
$4{print $4 FS > "temp4"}
' yourfile

paste temp1 temp2 temp3 temp4 | tr '\t' ',' > newfile
rm temp1 temp2 temp3 temp4
5

Using Perl:-

perl -F, -wlane '
push @col1,$F[0] if ($F[0] =~ /\S+/ ) ;
push @col2,$F[1] if ($F[1] =~ /\S+/ ) ;
push @col3,$F[2] if ($F[2] =~ /\S+/ ) ;
push @col4,$F[3] if ($F[3] =~ /\S+/ ) ;
END{
for ($i=0;$i<=$#col1;$i++) {$,="," ; print $col1[$i],$col2[$i],$col3[$i],$col4[$i] ; }
}
' infile.txt
6

If you have enough memory:

awk '{ for(i=1;i<=NF;i++)
         if($i!~/ /)
         {
           N++
           A[N,i]=$i
         }
     }
     END {
       for (i=1;i<=N[1];i++)
       {
         for (j=1;j<NF;j++)
           printf A[i,j]FS
         print A[i,NF]
       }
     }' FS=, infile
7

Scrutinizer it is not working for me , it gives empty spaces.

===============
sorry man; it worked after I used /usr/xpg4/bin/awk only and it failed with nawk!!!!
BR

8

This variation should use less memory. It prints rows once they are full and it discards array elements that are no longer required:

awk '{ p=1
       for(i=1;i<=NF;i++)
       {
         if($i!~/ /)
         {
           N++
           A[N,i]=$i
         }
         if(N<=n)
           p=0
         fi
       }
       if(p)n++
       for (i=m+1;i<=n;i++)
         for (j=1;j<=NF;j++)
         {
           s=(j==NF)?RS:FS
           printf A[i,j]s
           delete A[i,j]
         }
       m=n
     }' FS=, infile
9

for the general case (many columns) and using Perl:-

perl -F, -lane '
$i=0 ;
foreach  (@F) {
push @{$a[$i]},$_ if ( $_ =~ /\S+/ );
$i++ ;
}
END {
        for ($i=0;$i<=scalar(@{$a[0]});$i++) {
              $,="," ;
              print map { $a[$_]->[$i] } (0..scalar(@a)-1) ;
                       }
}'  infile.txt

:D:D:D