How To Split File in PowerBuilder

Question

I have the script here which works for the splitting of file. For example the total lds_trandetail.rowcount( ) is 56, i have to chopped that 56 in 3 files with maximum of full 20 rows and the last file will handle the remaining rows which is 16. I was able to generate the first file with 20 rows which is good. But I can't figure it out on how to get the remaining rows. Anybody to help please...

The expected output should be:
20 Full Rows in a File  = Header as First Row(1) + Detail(18) + Footer as Last Row(1)

1st File  : 18 rows (lds_trandetail rows from 1 to 18)
2nd File : 18 rows (lds_trandetail rows from 19 to 36)
3rd File : 18 rows (lds_trandetail rows from 37 to 54)
4th File : 2 (lds_trandetail rows from 55 to 56)


//Variable Desclarations Here.....
IF NOT ISVALID (lds_TranHeader) THEN 
    lds_TranHeader = CREATE DataStore
    lds_TranHeader.DataObject = 'd_export_inventoryheader'
    lds_TranHeader.SetTransObject(SQLCA)
END IF

IF NOT ISVALID (lds_TranDetail) THEN 
    lds_TranDetail = CREATE DataStore
    lds_TranDetail.DataObject = 'd_export_inventorydetail'
    lds_TranDetail.SetTransObject(SQLCA)
END IF


lds_TranHeader.Retrieve( )
lds_TranDetail.Retrieve( )
ll_detailrow = lds_TranDetail.RowCount()

IF lds_TranDetail.RowCount( ) > 0 THEN  
    //Initialize the total rowcount of inventory.
    li_fileint = (ll_detailrow/18)
    ldec_filecount = Dec(ll_detailrow/18)
    ls_remainder = String(ldec_filecount,'####.000')
    li_pos = POS(ls_remainder,'.')
    ls_remainder = Mid(ls_remainder, li_pos)
    ldec_remainder = Dec(ls_remainder)
    IF ldec_remainder > 0 THEN 
        li_fileint = li_fileint + 1
    ELSEIF ldec_remainder < 0 THEN
        li_fileint = (ll_detailrow/18)
    END IF

    ls_seqnum = gf_control_num('EDI_HW4C1FOOTER',10)

    //Perform Iteration to the inventory rowcount.
    FOR ll_detail = 1 TO lds_TranDetail.RowCount()
        IF lds_TranHeader.RowCount() = 1 THEN
            ls_recordidh    = lds_TranHeader.getItemString(1,'RECORDIDH')
            ls_schcontactname = lds_TranHeader.getItemString(1,'SCHCONTACTNAME')
            ls_schcontactemail = lds_TranHeader.getItemString(1,'schcontactemail')
            ls_schcontactphone = lds_TranHeader.getItemString(1,'schcontactphone')
            ls_schdunsnum = lds_TranHeader.getItemString(1,'schdunsnum')
            ldt_createddt = lds_TranHeader.getItemDateTime(1,'createddt')
            ls_hwdnunsnum = lds_TranHeader.getItemString(1,'hwdunsnum')
            ls_createddt = String(ldt_createddt,'YYYYMMDDHHMMSS')       
            ls_header = ls_recordidh + '~t'
            ls_header += ls_schcontactname + '~t'
            ls_header += ls_schcontactemail +  '~t'
            ls_header += ls_schcontactphone + '~t'
            ls_header += ls_schdunsnum + '~t'
            ls_header += ls_createddt + '~t'
            ls_header += ls_hwdnunsnum + '~t' + '~t' + '~t' + '~t'      
        END IF

        ls_recordidd = lds_TranDetail.getItemString(ll_detail,'recordidd')
        ls_clnum =  lds_TranDetail.getItemString(ll_detail,'clnum')
        ls_schdunsnum =  lds_TranDetail.getItemString(ll_detail,'schdunsnum')
        ls_schlsname =  lds_TranDetail.getItemString(ll_detail,'schlsname')
        ls_company =  lds_TranDetail.getItemString(ll_detail,'company')
        ls_hwwhsecode =  lds_TranDetail.getItemString(ll_detail,'hwwhsecode')
        ls_hwdunsnum =  lds_TranDetail.getItemString(ll_detail,'hwdunsnum')
        ls_hwsubcompany =  lds_TranDetail.getItemString(ll_detail,'hwsubcompany')
        ls_boxname =  lds_TranDetail.getItemString(ll_detail,'boxname')
        ls_boxnumb =  lds_TranDetail.getItemString(ll_detail,'boxnumb')
        ls_contractnum =  lds_TranDetail.getItemString(ll_detail,'contractnum')
        //ldec_ItemQty =  Dec(lds_TranDetail.getItemNumber(ll_detail,'itemqty'))
        ls_itemqty = String(lds_TranDetail.getItemNumber(ll_detail,'itemqty'))
        ls_itemcode =  lds_TranDetail.getItemString(ll_detail,'itemcode')
        ls_boxstatus =  lds_TranDetail.getItemString(ll_detail,'boxstatus')

        ls_detail += ls_recordidd + '~t'
        ls_detail += ls_clnum + '~t'
        ls_detail += ls_schdunsnum + '~t'
        ls_detail += ls_schlsname + '~t'
        ls_detail += ls_company + '~t'
        ls_detail += ls_hwwhsecode + '~t'
        ls_detail += ls_hwdunsnum + '~t'
        ls_detail += ls_hwsubcompany + '~t'
        ls_detail += Trim(ls_boxname) + '~t'
        ls_detail += ls_boxnumb + '~t'
        ls_detail += ls_contractnum + '~t'
        ls_detail += ls_itemqty + '~t'
        ls_detail += Trim(ls_itemcode) + '~t'
        ls_detail += ls_boxstatus + '~t' + '~t' + '~t' + '~t' + '~r~n'      

        Long ll_insert
        Long ll_getrow, ll_counter, ll_totalrow, ll_loop = 0
        String ls_trandate, ls_generateddt

        IF ll_detail = 18  AND (li_fileint <> li_filecount) THEN                
            ldt_trandate = gnv_app.of_getServerDate( )
            ls_trandate = String(ldt_TranDate,'YYYYMMDDHHMMSS')

            li_pagefile++ //Increment                           
            ls_Footer = 'F' + '~t' + String(li_pagefile) + '~t' + '.' + '~t'  
            ls_Footer += ls_seqnum + '~t' + '/' + '~t' + 'E'  + '~t' + '~t' + '~t' + '~t'

            ls_Message = ls_Header + '~r~n' + ls_Detail + ls_Footer + '~r~n'            
            FilePath = String(dw_path.Object.outboundfilepath[1])
            FileName = ls_FilePrefix + '_' +String(li_pagefile)+'_'+String(ldt_TranDate,'YYYYMMDDHHMMSS') + '.txt'
            FileDir = FilePath + '/' + FileName
            li_FileNum = FileOpen(FileDir,  TextMode!, Write!, LockWrite!, Append!) 

            ll_status = FileWriteEx(li_FileNum, ls_Message)
            IF ll_Status >= 1 THEN 
                FileClose(li_FileNum)
            END IF
            IF Trim(ls_Message) <> "" OR Len(Trim(ls_Message)) > 0 THEN ls_Message = ""
            IF Trim(ls_Header) <> "" OR Len(Trim(ls_Header)) > 0 THEN ls_Header = ""
            IF Trim(ls_Detail) <> "" OR Len(Trim(ls_Detail)) > 0 THEN ls_Detail = ""
            ll_getrow = lds_trandetail.RowCount()
            li_filecount = li_filecount + 1
            ll_loop = ll_detail + 18
            ll_totalrow = ll_detailrow - ll_detail
            ll_detail = 18 * li_filecount
        END IF

    NEXT


END IF

RETURN ib_RETURN = TRUE

DARKinVADER DARKinVADER · Accepted Answer · 2013-07-06T07:17:11

You talk about 20 rows per file, but even with the header row you put 18-19 rows in each file as I see. I do not fully understand your code, but i would write a for loop and if the counter is divideable by 20 i would close the actual file and open a new one.

ADDED:

I think the problem is with this condition:

 IF ll_detail = 18  AND (li_fileint <> li_filecount) THEN

This is false, because you check only the 18 rows only for the first time, that's why you get only one file.

You should change this similar to this:

IF (Mod(ll_detail, 18) = 0 or ll_detail = lds_TranDetail.RowCount())  AND (li_fileint <> li_filecount) THEN

The Mod(ll_detail, 18) = 0 condition checks whether you are the end of another 18 row OR ll_detail = lds_TranDetail.RowCount() you are at the end of all row!

I think this could work.

Br. Gábor

How To Split File in PowerBuilder

1 Answers