Comma Delimited Files

NOTE - Use your Browsers BACK Button to return to prior page or CLICK here.

Reading Comma-Delimited Files

This page will demonstrate a method of parsing fields from a comma delimited file. All text fields are enclosed within double quotes. Commas may be embedded within text fields..

HALLOWEEN NIGHT... and I'm running out of candy...

It's Halloween night... Since I'm on Trick'Or'Treat duty and my home office is next to the front door I thought this would be a good time to update my web-site (despite the frequent interuptions - uh oh... there goes the door again... I'll be right back!)...

Sample Input File


"E",,,"Costs, Expense, Savings",334,01-JAN-90
"D",01-JAN-90,"Costs",,,,
"X",27-OCT-99,"A,B,C,,,embedded commas,,,","More,,,,,,",999,01-JAN-99

The sample ascii text file above consists of 3 records. Each record contains 6 fields or comma-delimited columns. The text fields are enclosed in double-quotes and may have embedded commas. Now let's post a simple routine to parse each column into a variable and display the variable contents.

Let's assume the the fields represent: code, date, category, description and effective date respectively.

Main Routine - Parse Input Record into individual variables


!**********************************************************************
!*       Process Main                                                 *
!**********************************************************************

begin-procedure Process-Main

let $c = ','    ! Comma
let $q = '"'    ! Double Quotes

create-array name=PARmtx  size=10  field=PARdata:char

while 1 = 1

   read 1 into $rec:500

   if #end-file = 1
      break
   end-if

   clear-array name=PARmtx

   let $rec      = rtrim($rec,' ') || $c

   let #len      = length($rec)

   let #q        = 0
   let #idx      = 0
   let #pos      = 1
   let $data     = ''

   while #pos   <= #len

      let $char  = substr($rec, #pos, 1)

      if  $char  = $q
          let #q = #q + 1
      else
          if  $char      = $c
          and mod(#q, 2) = 0
              let PARmtx.PARdata (#idx) = $data
              let #idx   = #idx + 1
              let $data  = ''
          else
              let $data  = $data || $char
          end-if
      end-if

      let #pos   = #pos + 1

   end-while

   let $I_code   = PARmtx.PARdata (0)
   let $I_date   = PARmtx.PARdata (1)
   let $I_cat    = PARmtx.PARdata (2)
   let $I_desc   = PARmtx.PARdata (3)
   let $I_amt    = PARmtx.PARdata (4)
   let $I_effdt  = PARmtx.PARdata (5)

   !   Process Variables (Convert numerics, etc.)

   display ' '
   display ' Code: '     noline
   display $I_code
   display ' Date: '     noline
   display $I_date
   display '  Cat: '     noline
   display $I_cat
   display ' Desc: '     noline
   display $I_desc
   display '  Amt: '     noline
   display $I_amt
   display 'Effdt: '     noline
   display $I_effdt

end-while

display ' '

end-procedure

!**********************************************************************

Sample Output - Displays


 Code: E
 Date: 
  Cat: 
 Desc: Costs, Expense, Savings
  Amt: 334
Effdt: 01-JAN-90

 Code: D
 Date: 01-JAN-90
  Cat: Costs
 Desc: 
  Amt: 
Effdt: 

 Code: X
 Date: 27-OCT-99
  Cat: A,B,C,,,embedded commas,,,
 Desc: More,,,,,,
  Amt: 999
Effdt: 01-JAN-99

The routine successfully stripped each field into its' own seperate variable. Embedded commas had no effect on the outcome of the operation since they were contained within double-quotes. Take note that after a record is read ($rec) trailing spaces are removed and an additional comma is concatenated to the end of the record. The appended comma provides a definite ending delimiter to the record.

I ran out of Halloween related pictures so I thought I'd post this picture of a mountain goat.

Feedback

I would appreciate any feedback you may have on this site. Send mail to tdelia@erols.com or click on the Octopus.

Technical difficulties?

Please report any technical difficulties you may encounter to the address above OR click on the Octopus. Thanks.

NOTE - Use your Browsers BACK Button to return to prior page.

Tony DeLia - Updated October 31, 1999