Checking Delimited Files for the Correct Number of Fields

A customer received a comma-delimited file from an outside source. He had to edit some of the fields to make sure they would not get rejected during the import operation. He also wanted to make sure that he had the exact number of fields, no more, no less.

Let us assume that the file is supposed to contain six fields separate by commas and without quotes. And that the fields do not contain commas. In a normal comma-delimited file, we expect each record to contain five commas. If records contained data fields with commas, however, the field count may not be accurate.

     This,line,has,exactly,six,fields.
     This,line,has,more,than,six,fields.
     This,line,has,only five,fields.

We need to use the Pattern option and the QeditCount JCW to make sure there are no more than five commas.

     /list "@,@,@,@,@,@,@" (pattern)
     if qeditcount > 0 then
        echo ***** !qeditcount lines with too many commas *****
     endif

Similarly, we can use the following commands to make sure the records have at least five commas. Notice that we are also using the Nomatch option on the List command.

     /list "@,@,@,@,@,@" (pattern nomatch)
     if qeditcount > 0 then
        echo ***** !qeditcount line(s) with too few commas *****
     endif

You might want to put all these commands into a single command file and call it Chkcomma. Running Chkcomma from Qedit would give us the following results:

     /chkcomma
         2     This,line,has,more,than,six,fields.
     1 line found
     ***** 1 line(s) with too many commas *****
         3     This,line,has,only five,fields.
     1 line found
     ***** 1 line(s) with too few commas *****

....Back to the Qedit Q&A Page