QTP, QUIZ Files with commas.

[Jeff Hoffman]

At 03:49 PM 10/17/01 +0200, Seamus BROWNE wrote: 

<excerpt><fontfamily><param>Arial</param><color><param>3333,3333,3333</param><smaller> 

</smaller></color><smaller><bold><color><param>3333,3333,9999</param>Now
if I may widen the debate.

</color></bold></smaller></fontfamily>

<fontfamily><param>Arial</param><color><param>3333,3333,3333</param><smaller>We
are having the same kind of comma problem here.  Or soon will be, when

</smaller></color></fontfamily>

<fontfamily><param>Arial</param><color><param>3333,3333,3333</param><smaller>we
start receiving big files from Internet cgis. (Common gateway
interfaces)

</smaller></color></fontfamily>

<fontfamily><param>Arial</param><color><param>3333,3333,3333</param><smaller>Fields
in these files are separated by "|" or commas or semi-colons and the

</smaller></color></fontfamily>

<fontfamily><param>Arial</param><color><param>3333,3333,3333</param><smaller>object
of the game is to get them into the (Image) database.

</smaller></color></fontfamily>

</excerpt><<<<<<<<

One solution is to create a file in the data dictionary sequential
(tempfile) with a record size bigger than any file you expect to
receive.


When you get data file pad it out to the size of this file (tempfile)
using you favorite utility.


Equate the file to the file in the dictionary.


Then use QTP :



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  no_data       char*2    =  delim + delim


sel tempfile if tempfile[1:2] <<> no_data          and &

                tempfile[1:2] <<> "  "             and &

                tempfile[1:8] <<> "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



>>>>


Good luck.




+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

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

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+