delimited File
Hoppe, Eric EP
Eric.Hoppe@steelscape-inc.com
Mon, 19 Aug 2002 16:43:22 -0700
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_000_01C247DA.34815910
Content-Type: text/plain
Anthony-
Here is how we do it on an Alpha (OpenVMS, RMS)
We have a record called RMSUTILITY with one element DS-INSTR.
RMSUTILITY is a sequential, RMS, variable file
DS-INSTR is a char 500 field
We ftp the file to the Alpha, define the logical RMSUTILITY to the ftp'ed
file, and run a quick screen. We use quick screen because it is very easy
to debug and step through during testing. It is also extremely easy to
modify from one file to another.
We had one programmer who insisted on creating a fixed width file, dumping
it onto the Alpha and working with a 'proper' fixed width file. This meant
we had to maintain programs on multiple platforms every time we got a new
file. I realize ExcelXP finally may have it right because I have been
successfully saving fixed width files from it lately for import into our DB.
For the sake of easiness, I will just attach one of the programs that reads
through the .csv file. Basically, it reads the data out to the next comma,
processes it, saves the test string on top of itself sans the last data
element and reads the data out to the next comma... So on and so forth. It
doesn't matter how large or small your fields are.
This example is a simple one chosen to illustrate how the process works.
You may use it as a template if you like. I wrote it, so I guess I own it
and legally, I can decide to share it as long as it doesn't hurt my
employer. The meat and potatoes starts in the initialization procedure. If
you have any questions, please let me know.
---ehops
============================================================================
================
Message: 2
From: "Arnone, Anthony" <aarnone@grey.com>
To: "'powerh-l@lists.swau.edu'" <powerh-l@lists.swau.edu>
Subject: delimited File
Date: Sat, 17 Aug 2002 15:46:53 -0400
charset="iso-8859-1"
I have a comma Delimited file and I need to read it in Quiz and break out
each individual field. The Record looks like this:
099000,00172,02,07,01,800
I am not exactly sure what is the best and easiest way to accomplish this.
Anthony
------_=_NextPart_000_01C247DA.34815910
Content-Type: application/octet-stream;
name="sthbaq03_email.qks"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="sthbaq03_email.qks"
;=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
; STHBAQ03.QKS
;
; Description: Ghost screen for one time import of coil data.
; This program will load the Paint details
; into the coil comments.
;
; Modification history:
;------+---------+-----+------------------------------------------------=
-------
; Vers | Date | Who | Reason
;------+---------+-----+------------------------------------------------=
-------
; 1.0 |03-jun-02| eph | original based on STHBAQ02.QKS
; | | |
; | | |
;=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
cancel clear
set default
set verify
screen PHRUN:STHBAQ03 nomode &
action at 2,1 &
message on line 7 &
activities entry &
autoreturn &
autoupdate
file RMSUTILITY designer
file STKITEM designer
file STKITEMCM designer open 0
file STKITEMCM designer alias STKITEMCM-ALIAS open 1
file STKITEMPAINT designer open 0
file STKITEMPAINT designer alias STKITEMPAINT-ALIAS open 1
file PAINT designer
;***********************************************************************=
****
;* Temps and Defines
;***********************************************************************=
****
temp T-TEXT char size 1000
;
temp T-CTR num*2
;
temp T-ERR-MSG char size 100
temp T-COMMAND char size 250
;
temp T-CM-SEQ zoned*5 unsigned
temp T-PNT-SEQ char*2
temp T-KY-STKITEMCM char*32
temp T-PREFIX char*10 initial ""
temp T-PP-PNT-NBR char*20
temp T-SS-PNT-NBR char*4
temp T-DS-PNT char*25
;***********************************************************************=
****
;* procedures
;***********************************************************************=
****
procedure internal MAIL-ERROR-MESSAGE ; generic mail message sender
begin
let T-COMMAND =3D "submit/que=3Dwarm/log=3DOP/noprint/noidentify " =
+ &
'SM:MAILFILE /param=3D("' + "PHDAT:NULLFILE.DAT" + =
'",' + &
'"@phdat:ehops.dis",' + '"' + =
&
truncate(T-ERR-MSG) + '")'
run command T-COMMAND
end
procedure internal GET-HIGHEST-COMMENT-SEQ
begin
;
;see if there are any existing comments for the coil to correctly
;sequence the comment
;
while ret STKITEMCM-ALIAS via KY-STKITEM using KY-STKITEM of =
STKITEM
begin
let T-CM-SEQ =3D NO-SEQ of STKITEMCM-ALIAS
end
end
procedure internal POPULATE-STKITEMPAINT
begin
let KY-STKITEM of STKITEMPAINT =3D KY-STKITEM of STKITEMCM
if T-PNT-SEQ =3D "10"
then begin
let CD-TOP-REV of STKITEMPAINT =3D 1
let NO-APPL-PAINT of STKITEMPAINT =3D 2
let TH-PAINT of STKITEMPAINT =3D TH-PNT-FIN-TOP of STKITEM
let CD-COATER of STKITEMPAINT =3D "F"
end
else if T-PNT-SEQ =3D "20"
then begin
let CD-TOP-REV of STKITEMPAINT =3D 2
let NO-APPL-PAINT of STKITEMPAINT =3D 2
let TH-PAINT of STKITEMPAINT =3D TH-PNT-FIN-REV of STKITEM
let CD-COATER of STKITEMPAINT =3D "F"
end
else if T-PNT-SEQ =3D "30"
then begin
let CD-TOP-REV of STKITEMPAINT =3D 1
let NO-APPL-PAINT of STKITEMPAINT =3D 1
let TH-PAINT of STKITEMPAINT =3D TH-CLR-TOP of STKITEM
let CD-COATER of STKITEMPAINT =3D "P"
end
else if T-PNT-SEQ =3D "40"
then begin
let CD-TOP-REV of STKITEMPAINT =3D 2
let NO-APPL-PAINT of STKITEMPAINT =3D 1
let TH-PAINT of STKITEMPAINT =3D TH-CLR-REV of STKITEM
let CD-COATER of STKITEMPAINT =3D "P"
end
let NO-PAINT of STKITEMPAINT =3D nconv(T-SS-PNT-NBR)
let NO-PASS of STKITEMPAINT =3D 1
get PAINT via NO-PAINT using nconv(T-SS-PNT-NBR) opt
if accessok
then let DS-PAINT of STKITEMPAINT =3D DS-PAINT of PAINT
else let DS-PAINT of STKITEMPAINT =3D "PP: " + T-DS-PNT
get STKITEMPAINT-ALIAS via KY-STKITEMPAINT using KY-STKITEMPAINT of =
STKITEMPAINT opt
if accessok
then begin
let T-ERR-MSG =3D "Paint Info: " + trunc(KY-STKITEMPAINT of =
STKITEMPAINT) &
+ " already exists!"
do internal MAIL-ERROR-MESSAGE
;
;initialize data fields
;
let ky-stkitempaint of stkitempaint =3D ""
let no-paint of stkitempaint =3D 0
let ds-paint of stkitempaint =3D ""
let th-paint of stkitempaint =3D 0
let fl-th-paint-nom of stkitempaint =3D ""
let no-pass of stkitempaint =3D 0
let cd-coater of stkitempaint =3D ""
end
else put STKITEMPAINT reset
;
;fill the empty fields of STKITEMPAINT
;
let KY-STKITEM of STKITEMPAINT =3D KY-STKITEM of STKITEMCM
if T-PNT-SEQ =3D "10"
then begin
let CD-TOP-REV of STKITEMPAINT =3D 1
let NO-APPL-PAINT of STKITEMPAINT =3D 4
end
else if T-PNT-SEQ =3D "20"
then begin
let CD-TOP-REV of STKITEMPAINT =3D 2
let NO-APPL-PAINT of STKITEMPAINT =3D 4
end
else if T-PNT-SEQ =3D "30"
then begin
let CD-TOP-REV of STKITEMPAINT =3D 1
let NO-APPL-PAINT of STKITEMPAINT =3D 3
end
else if T-PNT-SEQ =3D "40"
then begin
let CD-TOP-REV of STKITEMPAINT =3D 2
let NO-APPL-PAINT of STKITEMPAINT =3D 3
end
get STKITEMPAINT-ALIAS via KY-STKITEMPAINT using KY-STKITEMPAINT of =
STKITEMPAINT opt
if accessok
then begin
let T-ERR-MSG =3D "Paint Info: " + trunc(KY-STKITEMPAINT of =
STKITEMPAINT) &
+ " already exists!"
do internal MAIL-ERROR-MESSAGE
end
else put STKITEMPAINT reset
end
procedure initialize
begin
while retrieving RMSUTILITY sequential
begin
let T-TEXT =3D DS-INSTR of RMSUTILITY
;
;get the coil ID
;
let T-CTR =3D index(T-TEXT,",")
let CD-STK-PREFIX of STKITEMCM =3D T-TEXT[1:T-CTR - 1]
let T-TEXT =3D TRUNC(T-TEXT[T-CTR + 1:1000])
if T-PREFIX <> CD-STK-PREFIX of STKITEMCM
then let T-CM-SEQ =3D 0
;
;make sure this coil exists in SIMS
;
get STKITEM via CD-STK-PREFIX using CD-STK-PREFIX of STKITEMCM =
opt
if not accessok or CD-POP-OWNER of STKITEM <> "B"
then begin
if T-PREFIX <> CD-STK-PREFIX of STKITEMCM
then begin
let T-ERR-MSG =3D "JBA COIL (" + trunc(ID-STK of =
STKITEMCM) &
+ ") DOES NOT EXIST IN SIMS!"
do internal MAIL-ERROR-MESSAGE
let T-PREFIX =3D CD-STK-PREFIX of STKITEMCM
end
end
else begin
do internal GET-HIGHEST-COMMENT-SEQ
let KY-STKITEM of STKITEMCM =3D KY-STKITEM of STKITEM
;
;get the paint sequence number.
;
let T-CTR =3D index(T-TEXT,",")
let T-PNT-SEQ =3D T-TEXT[1:T-CTR - 1]
let T-TEXT =3D TRUNC(T-TEXT[T-CTR + 1:1000])
let CD-TY-COMMENT of STKITEMCM =3D "BYP"
let NO-SEQ of STKITEMCM =3D T-CM-SEQ + 100
let CD-LOGON of STKITEMCM =3D "JBA UPLOAD"
;
;get the paint part number
;
let T-CTR =3D index(T-TEXT,",")
let T-PP-PNT-NBR =3D T-TEXT[1:T-CTR - 1]
;let DS-COMMENT of STKITEMCM =3D T-TEXT[1:T-CTR - 1]
let T-TEXT =3D TRUNC(T-TEXT[T-CTR + 1:1000])
;
;try to match their paint number with our supplier code
;
get PAINT via NO-PAINT-SUPPL using trunc(T-PP-PNT-NBR) opt
if accessok
then let T-SS-PNT-NBR =3D ascii(NO-PAINT of PAINT)
else begin
;
;if no match was found between the JBA paint part =
number and the
;steelscape supplier code, assign the default standard =
steelscape
;paint numbers GaryP set up for the JBA paints
;
if T-PNT-SEQ =3D "10"
then let T-SS-PNT-NBR =3D "7512"
else if T-PNT-SEQ =3D "20"
then let T-SS-PNT-NBR =3D "7513"
else if T-PNT-SEQ =3D "30" or T-PNT-SEQ =3D "40"
then let T-SS-PNT-NBR =3D "7511"
end
;
;get the paint description
;
let T-CTR =3D index(T-TEXT,",")
let T-DS-PNT =3D T-TEXT[1:T-CTR - 1]
let T-TEXT =3D TRUNC(T-TEXT[T-CTR + 1:1000])
let DS-COMMENT of STKITEMCM &
=3D trunc(T-PNT-SEQ) + " : " &
+ trunc(T-SS-PNT-NBR) + " : " &
+ trunc(T-PP-PNT-NBR) + " : " &
+ trunc(T-DS-PNT)
let DT-COMMENT of STKITEMCM =3D sysdate
let TI-COMMENT of STKITEMCM =3D systime
let FL-SCHEDULE of STKITEMCM =3D "Y"
let T-KY-STKITEMCM =3D KY-STKITEMCM of STKITEMCM
do internal POPULATE-STKITEMPAINT
put STKITEMCM reset
info =3D T-KY-STKITEMCM + " put successful!" now
end
end
return
end
build detail list
------_=_NextPart_000_01C247DA.34815910--