[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
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