FW: Nice QTP solution (was Delimited... Help!)
Jeff Hoffman
wonicon@bigpond.net.au
Wed, 14 Nov 2001 07:34:12 +1100
John
Here it is, I did not post it to the list when I sent it to Karen as it was
posted previously on 18 Oct 2001.
>>>>>>>
You have to create a large (I use 2000 chars) sequential file in you data
dictionary then use file equates to access your data files. On MPE you have
to pad your work files out to 2000 chars or whatever (if you use Reflection
terminal emulator you can pad out on the upload), if using VMS you can
specify variable length records on you temp file.
run prbscom:PRHT01T
set process limit 99999
REQUEST format_data
acc tempfile
def file_len = 2000 ; record length of tempfile
; This assumes you know the field delimiter is a TAB,
;def D-I-TAB INT = 9
;def D-C-TAB CHAR*2 = CHARACTER(D-I-TAB)
;def delim char*1 = D-C-TAB[1:1] ; Tab HT character (VMS, UNIX)
;def delim char*1 = D-C-TAB[2:1] ; Tab HT character (MPE)
def delim char*1 = ","
; do not read blank or header records
; this is specific to this application and used as an example only.
def no_data char*2 = delim + delim
sel tempfile if tempfile[1:2] <> no_data and &
tempfile[1:2] <> " " and &
tempfile[1:8] <> "Old Code" and &
tempfile[1:9] <> '"Old Code'
temp prosth_code char*10
temp prosth_item char*270
temp cost1 char*20
temp prosth_cost
temp supplier_id char*10
temp strt
temp endd
temp syze
temp sepr
; Col A Old Code
item strt = 1
item endd = index(tempfile[strt:file_len -strt],delim)
item prosth_code = upshift(lj(tempfile[strt:endd -1]))
; Remove possible quotes around the field
item syze = size(trunc(prosth_code))
item prosth_code = prosth_code[2: syze -2] &
if prosth_code[1:1] = '"' else &
prosth_old_code
; Col B Product/Item
item strt = strt + endd
item endd = index(tempfile[strt:file_len -strt],delim)
item prosth_item = upshift(lj(tempfile[strt:endd -1]))
; Remove possible quotes around the field
item syze = size(trunc(prosth_item))
item prosth_item = prosth_item[2: syze -2] &
if prosth_item[1:1] = '"' else &
prosth_item
; Col C Item Cost
item prosth_cost = 0
item strt = strt + endd
item endd = index(prosth_rec[strt:file_len -strt],delim)
item cost1 = upshift(lj(prosth_rec[strt:endd -1]))
; Remove possible quotes around the field
item syze = size(trunc(prosth_item))
item cost1 = cost1[2: syze -2] &
if cost1[1:1] = '"' else &
cost1
; Strip out possible formatting characters
item syze = size(trunc(cost1))
item cost1 = cost1[2: syze -2] if cost1[1:1] = '"' else &
cost1
item syze = size(trunc(cost1))
item cost1 = cost1[2: syze -1] if cost1[1:1] = '$' else &
cost1
item syze = size(trunc(cost1))
item sepr = index(cost1,",")
item cost1 = cost1[1: sepr -1] + cost1[sepr +1:syze -sepr] &
if sepr > 0 else &
cost1
item syze = size(trunc(cost1))
item sepr = index(cost1,",")
item cost1 = cost1[1: sepr -1] + cost1[sepr +1:syze -sepr] &
if sepr > 0 else &
cost1
item syze = size(trunc(cost1))
item sepr = index(cost1,".")
item cost1 = cost1[1: sepr -1] + cost1[sepr +1:syze -sepr] &
if sepr > 0 else &
cost1
item prosth_cost = ncon(cost1)
; Col D Sponsor/Facility ID
item strt = strt + endd
item endd = index(tempfile[strt:file_len -strt],delim)
; The following is put in, in case the last field is not
; terminated with a field delimiter
item endd = file_len - strt &
if endd < 1 else &
endd
item supplier_id = upshift(lj(tempfile[strt:endd -1]))
; Remove possible quotes around the field
item syze = size(trunc(supplier_id))
item supplier_id = supplier_id[2: syze -2] &
if supplier_id[1:1] = '"' else &
supplier_id
subfile prht01t keep include &
prosth_code, prosth_item, prosth_cost, supplier_id
>>>>
At 01:46 PM 13-11-01 -0500, you wrote:
>Jeff
>
>Can I see your "particularly nice example" please?
>
>Regards,
>JWP
>
>> -----Original Message-----
>> From: Karen Barrett [SMTP:kbarrett@denkor.com]
>> Sent: Monday, November 12, 2001 4:56 PM
>> To: PowerhouseList
>> Subject: Nice QTP solution (was Delimited... Help!)
>>
>> Thank you all (John Pickering, Neil Harvey, Richard Witkopp, Joe Boyle,
>> Terrence Burke, and Jeff Hoffman). The primary culprit was my use of
>> define
>> statements instead of temps/items. The index function was being used to
>> parse out the pieces. Jeff Hoffman (wonicon@bigpond.net.au) sent me a
>> particularly nice example dealing with tabs, undesired quotes, etc. From
>> that I was able to add on empty field and early record termination logic.
>> Now running in 1 minute on a slow development box. I'm very happy to have
>> a
>> Powerhouse solution. Well done list!
>>
>> Karen Barrett
>> Senior Program Analyst
>> Denkor Dental Management Corp.
>>
>>
>
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ Jeff Hoffman + e-mail: wonicon@bigpond.net.au +
+ Director + +
+ WoniCon Pty Ltd (ABN 93 083 502 630)+ ,-_|\ +
+ 21 Prices Circuit + voice: +61 2 9542 1527 / \ +
+ Woronora NSW 2232 + \_,-._* +
+ AUSTRALIA + mobile: 040 790 3929 v +
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
+