[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]
Re: Comma seperated value file processing

Subject: Re: Comma seperated value file processing
From: Ray Ontko <rayo@ONTKO.COM>
Date: Tue, 1 May 2001 08:35:44 -0500
In-Reply-To: <9957CF1634B1D411AB7B00508B6FF5140177EBBB@kscgrndexc3> from "Fromen, Paul V" at "May 1, 2001 09:09:15 am"
As Paul hints, UNSTRING doesn't allow you to handle commas
within strings, or double-quotes withing a double-quoted string.
I too have a set of CSV support routines:

   http://www.ontko.com/sqr/csv.html

In addition my set provides procedure for WRITING csv files as
well as READING them.

Ray

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Prior to knowing about unstring, I wrote my own procedure to handle this. It
> may not be perfect, but it has worked well for me so far.  It handles more
> of the pitfalls that CSV files contain like the example mentioned.
>
> Paul
>
> !************************************************************************
> begin-procedure Parse-Record          ! Parse CSV string for each field *
> !************************************************************************
> !CREATION                                                               *
> !  Date          RFS #     Who         What                             *
> !----------   -----------  ----------- -------------------------------- *
> !12/09/1998                PVF         Initial Coding                   *
> !06/23/2000                PVF         Updated for new application      *
> !************************************************************************
> !
> ! CSV file is read and field variables are populated one field at a time
> ! each time Next-Field is called.  $Data_Record holds each new input line.
> ! Next field is read one field at a time until the last field is read, then
> ! start over.  Parse-Record is called for each record.
> !
>   let #Spos = 1
>   let #Epos = 1
>   do Next-Field
> !
> !ie:
> !  let $First_Field = $Field
> !  do Next-Field
> !  let $Second_Field = $Field
> !  do Next-Field
> !  let $Third_Field = $Field
> !  do Next-Field
> !  Etc, etc...
> !
> !  let $Header1 = 'EQNUM,Equip Desc,MANUFACTURER,EQ6,SERIAL,etc'
> !
> !Input File field Order:
> !
> ! EQNUM
>   let $EQNUM = $Field            ! Fields names like input header.
>   do Next-Field
> ! Equip Desc
>   let $Equip_Desc = $Field
>   do Next-Field
> ! MANUFACTURER
>   let $MANUFACTURER = $Field
>   do Next-Field
> ! EQ6
>   let $EQ6 = $Field
>   do Next-Field
> ! SERIAL
>   let $SERIAL = $Field
>   do Next-Field
> ! ETC
>   let $ETC = $Field
> !
> ! do Next-Field
> !
> !===> That's all the input fields! <===
> !
> end-procedure Parse-Record
> !************************************************************************
>
> !************************************************************************
> begin-procedure Next-Field            ! Get next field from a CSV string*
> !************************************************************************
> !CREATION                                                               *
> !  Date          RFS #     Who         What                             *
> !----------   -----------  ----------- -------------------------------- *
> !12/09/1998                PVF         Initial Coding                   *
> !06/15/2000                PVF         Updated to handle quoted strings *
> !09/14/2000                PVF         Issue with single quote in data  *
> !                                      like this  ,".006""",            *
> !                                      Data reads: ,.006",              *
> !09/18/2000                PVF         Issue with single quote in data  *
> !                                      like this  ,"2""DS",             *
> !                                      Data reads: ,2"DS,               *
> !************************************************************************
> !
>   let #Epos = instr($Data_Record,',',#Epos)          !Initial End Pos
>   let #IEP = #Epos                                   !Save IEP
>   let #ISP = #Spos                                   !Save ISP
>   if #Epos = 0
>     let #Epos = length($Data_Record) + 1
>   end-if
> !
>   let #EM1 = 0                                       !Set subtract from EPOS
> value
>   if substr($Data_Record,#Spos,1) = '"'              !If start pos is a
> quote
> !
>     let #EM1 = 1                                     !Correct Epos after
> quoted fields?
>     let #Spos = #Spos + 1                            !Set start pos one past
> first quote
>     let #Epos = instr($Data_Record,'"',#Spos)        !Set end pos to loc of
> next quote
> !
> !"2""DS" is a valid string.
> !"K-H160A-02645 ""DAS""" is a valid string.
>     if substr($Data_Record,#Epos,2) = '""'           !Got two quotes in a
> row!
>
>         if (substr($Data_Record,#Epos,3) <> '"",' and
>             substr($Data_Record,#Epos,3) <> '"""' )  !Double quote found in
> string, not at end.
>                                                      !
>           let #Epos = #Epos + 1                      !Build field up to &
> including 1st quote.
>           let #Len = #Epos - #Spos                           !Field Length
>           let $Field =  substr($Data_Record,#Spos,#Len)      !From Start to
> End
>           let #Epos = #Epos + 1                      !Pos to start looking
> after "" (add one more)
>           let #Spos = #Epos                          !New Start Pos
>           let #Epos = instr($Data_Record,'"',#Spos)  !Find next quote in
> string starting after "".
>                                                      !
>           if substr($Data_Record,#Epos,3) = '"""'    !Got three quotes in a
> row!
>             let #Epos = #Epos + 1                    !Add one space to
> include one quote in data
>           end-if
>                                                      !
>           let #Len = #Epos - #Spos                   !Field Length
>           let $Field = $Field || substr($Data_Record,#Spos,#Len)      !From
> Start to End
>           let #EM1 = 1                               !New correct Epos is
> two past quote in data
>           goto Skip-Build-Field                      !Field already built
> correctly.
>         else                                         !
>           !".006""" is a valid string.
>           if substr($Data_Record,#Epos,3) = '"""'    !Got three quotes in a
> row!
>             let #EM1 = 2                             !New correct Epos is
> two past quote in data
>             let #Epos = #Epos + 1                    !Add one space to
> include one quote in data
>           end-if                                     !
>         end-if                                       !
>     end-if                                           !
>   end-if                                             !
> !
>   let #Len = #Epos - #Spos                           !Field Length
>   let $Field =  substr($Data_Record,#Spos,#Len)      !From Start to End
> Skip-Build-Field:                                    !Jump here if field
> already built.
> !
>   let #Epos = #Epos + 1 + #EM1                       !New End Pos (Correct
> Epos after quoted fields!)
>   let #Spos = #Epos                                  !New Start Pos
> !
> ! SET TO 1 FOR DEBUG:
>   if 1 = 0
> !
>     print 'Record = '          (+2,10)
>     print #record_input        (  ,20)
>
>     print 'Length = '          (+1,10)
>     print #len                 (  ,20)
> !
>     print '#ISP   = '          (+1,10)
>     print #ISP                 (  ,20)
>     print '#IEP   = '          (+1,10)
>     print #IEP                 (  ,20)
> !
>     print '#SPOS  = '          (+1,10)
>     print #Spos                (  ,20)
>     print '#Epos  = '          (+1,10)
>     print #Epos                (  ,20)
>     print '$Field = '          (+1,10)
>     print $field               (  ,20)
>   end-if
> end-procedure Next-Field
> !************************************************************************
>
> -----Original Message-----
> From: ANDERSD2 [mailto:ANDERSD2@OAKWOOD.ORG]
> Sent: Tuesday, May 01, 2001 8:14 AM
> To: SQR-USERS@list.iex.net
> Subject: Re: Comma seperated value file processing
>
>
> Beware of fields holding text that contain commas as the unstring will not
> adjust for this contingency.  For example, the following line would mess up
> the UNSTRING command
>
> one,two,three,"four, and five",six
>
> good luck
>
> -----Original Message-----
> From: Weinstein, Bruce [mailto:bweinste@NRCAN.GC.CA]
> Sent: Monday, April 30, 2001 3:47 PM
> To: SQR-USERS@list.iex.net
> Subject: Comma seperated value file processing
>
>
> I have to read a comma seperated value file.  What would be the OPEN & READ
> commands required to achieve this?
>
> Thanks for your help?
>
>         .B
>
> ~~~~~~~~~~~~~~~~~
> Bruce Weinstein
> 613-995-2209
> bweinste@nrcan.gc.ca
>

----------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/
References:
- Re: Comma seperated value file processing
  - From: "Fromen, Paul V" <paul.v.fromen@USAGO.KSC.NASA.GOV>
Prev by Date: Re: Comma seperated value file processing
Next by Date: can a variable be used for an array's size?
Prev by thread: Re: Comma seperated value file processing
Next by thread: can a variable be used for an array's size?
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page