Add additional fields based on field count

user_5283489 0
Viewed 176

I have data in below format in a file

"123","XYZ","M","N","P,Q"
"345",
"987","MNO","A,B,C"

I always want to have 5 entries in the row , so if the count of fields in 2 then 3 extra ("") needs to be added.

"123","XYZ","M","N","P,Q" 
"345","","","",""  
"987","MNO","A,B,C","",""

I looked upto the solution on the page

Add Extra Strings Based on count of fields- Sed/Awk

which has very similar requirement but when I try it fails as I have comma (,) within the field also.

Thanks.

sed awk
6 Answers

In GNU awk with your shown samples, please try following code.

awk -v s1="\"" -v FPAT='[^,]*|"[^"]+"' '
BEGIN{ OFS="," }
FNR==NR{
  nof=(NF>nof?NF:nof)
  next
}
NF<nof{
  val=""
  i=($0~/,$/?NF:NF+1)
  for(;i<=nof;i++){
    val=(val?val OFS:"")s1 s1
  }
  sub(/,$/,"")
  $0=$0 OFS val
}
1
'  Input_file  Input_file

Explanation: Adding detailed explanation for above.

awk -v s1="\"" -v FPAT='[^,]*|"[^"]+"' ' ##Starting awk program from here setting FPAT to csv file parsing here.
BEGIN{ OFS="," }                         ##Starting BEGIN section of this program setting OFS to comma here.
FNR==NR{                                 ##Checking condition FNR==NR here, which will be true for first time file reading.
  nof=(NF>nof?NF:nof)                    ##Create nof to get highest NF value here.
  next                                   ##next will skip all further statements from here.
}
NF<nof{                                  ##checking if NF is lesser than nof then do following.
  val=""                                 ##Nullify val here.
  i=($0~/,$/?NF:NF+1)                    ##Setting value of i as per condition here.
  for(;i<=nof;i++){                      ##Running loop till value of nof matches i here.
    val=(val?val OFS:"")s1 s1            ##Creating val which has value of "" in it.
  }
  sub(/,$/,"")                           ##Removing ending , here.
  $0=$0 OFS val                          ##Concatinate val here.
}
1                                        ##Printing current line here.
'  Input_file  Input_file                ##Mentioning Input_file names here.

EDIT: Adding this code here, where keeping a variable named nof where we can give our number of fields value which should be added minimum in all missing lines, in case any line is having more than minimum field values then it will take that value to add those many number of fields in missing field line.

awk -v s1="\"" -v nof="5" -v FPAT='[^,]*|"[^"]+"' '
BEGIN{ OFS="," }
FNR==NR{
  nof=(NF>nof?NF:nof)
  next
}
NF<nof{
  val=""
  i=($0~/,$/?NF:NF+1)
  for(;i<=nof;i++){
    val=(val?val OFS:"")s1 s1
  }
  sub(/,$/,"")
  $0=$0 OFS val
}
1
'  Input_file  Input_file
user_58665800

Here is one for GNU awk using FPAT when [you] always want to have 5 entries in the row :

$ awk '
BEGIN {
    FPAT="([^,]*)|(\"[^\"]+\")"
    OFS=","
}
{
NF=5                              # set NF to limit too long records
for(i=1;i<=NF;i++)                # iterate to NF and set empties to ""
    if($i=="")
        $i="\"\""
}1' file

Output:

"123","XYZ","M","N","P,Q"
"345","","","",""
"987","MNO","A,B,C","",""
user_41623560

Here is a an awk command that would work with any version of awk:

awk -v n=5 -v ef=',""' -F '","' '
{
   sub(/,+$/, "")
   for (i=NF; i<n; ++i)
      $0 = $0 ef
} 1' file

"123","XYZ","M","N","P,Q"
"345","","","",""
"987","MNO","A,B,C","",""
user_5482250

With perl, assuming every field is double quoted:

$ perl -pe 's/,$//; s/$/q(,"") x (4 - s|","|$&|g)/e' ip.txt
"123","XYZ","M","N","P,Q"
"345","","","",""
"987","MNO","A,B,C","",""

# if the , at the end of line isn't present
$ perl -pe 's/$/q(,"") x (4 - s|","|$&|g)/e' ip.txt
"123","XYZ","M","N","P,Q"
"345","","","",""
"987","MNO","A,B,C","",""

s|","|$&|g will search for "," and replace it back. The return value is number of replacements, which is then used to determine how many fields have to be appended.

The e flag allows you to use Perl code in the replacement section.

q operator helps to use different delimiter for single quoted string.


Here's an alternate solution that creates an array and then adds empty fields if necessary.

perl -lne '@f = /"[^"]+"|[^,]+/g; print join ",", @f, qw("") x (4 - $#f)'

/"[^"]+"|[^,]+/g defines fields as double quoted strings (with no double quote inside, so escaped quotes won't work with this solution) or non , characters (at least one, so , at end of line will be ignored).

qw("") x (4 - $#f) determines the extra fields to be appended. qw("") creates an array with single element of value "" which is then multiplied using the x operator.

user_40820520

Another perl way using -a for autosplit and -F to set the separator:

perl -lanF'/"*,*"/' -e 'print join ",", map "\"$_\"", @F[1..5]'
  • -F'/"*,*"/' - this uses an autosplit separator of double quote optionally preceeded by commas and quotes
  • -a uses that separator to autosplit into @F
  • -l adds linebreaks to print and -n will process input in stream mode w/o printing unless explicitly told to
  • map "\"$_\"", @F[1..5] takes exactly 5 fields, even undefined ones, and adds double quotes
  • print join ",", map ... takes the results of the map above, joins into a string with commas, and prints

(Note: because each line starts with a field delimiter, I'm ignoring the empty $F[0] element)

user_31208840

This might work for you (GNU sed):

sed ':a;s/"[^"]*"/&/5;t;s/$/,""/;ta' file

If there are 5 fields, bail out.

Otherwise, append an empty field and repeat.

user_9674920
Related