Advanced Bank Statement Import (BSI) Preprocessing

SOLVED

Hi,

Does anyone have an example of a 4GL script for preprocessing the input file on Advanced Bank Statement Import (BSI)?

I am trying to work with a CSV file downloaded from HSBCNet and I need to create a header line with the opening and closing balances. 

I know I can tick the 'Preprocessing' box and specify the script name in the Bank Import settings:

and it seems that I need a $PREPROCESS label in the script, but that is as far as I have got.

Any ideas would be very welcome!

Many thanks,

Martin

  • +1
    verified answer

    Hi Martin,

    Here is an example of Script done to convert a SAP Multicash Bank File (BSIIMPORTSBSA.txt) to a MT940 Bank format used within X3.

    This Script has to be declared on BSIIMPPAR as it is explained on the following document : Préscript_BSI.docx

    Please note that we do not have yet more technical documentation about this preprocessing Script.

    If you require additional Information, please raise a support case on the portal.

    I hope this helps.

    Kind regards
    Cristina

    Préscript_BSI.docx

    #<AdxTL>@(#)0.0.0.0 $Revision$
    #<AdxTL>@(#)0.0.0.0 $Revision$
    $PREPROCESS
    Local Char DOSSIER(80) : # Folder name (TMP, TRT, etc*)
    Local Char FILNAM(80)  : # File name
    Local Char EXT(5)      : # Extention (txt, src, csv, etc*)
    Local Char WTEXT(255)
    Local Integer MAXACC : MAXACC = 99
    Local Clbfile WFILE(1)(1..MAXACC)
    Local Char ACC(10)(1..MAXACC)
    Local Char ACCR(10)
    Local Integer CNT, CNT2, COUNT(1..MAXACC)
    #JOJAC - X3-197015 - 2020-05-27 - START
    Local Integer DETCNT(1..MAXACC)
    #JOJAC - X3-197015 - 2020-05-27 - END
    Local Char WRFILPATH(250)
    
    Gosub COMBINE_MULTIPLE_FILES
    Openi FILNAMTMP Using [RL]
    
    If !clalev([F:BAN]) :  Local File BANK [F:BAN] : Endif
    Local Char BNKSTMID
    Local Integer LENID
    Local Integer STRINGCHARCODE, STRPOS1, STRPOS2, ACCLEN
    
    
    Repeat
    Raz CNT
      Rdseq WTEXT Using [RL]
      #JOJAC - X3-194058 - 2020-05-05 - START
      #ACCR=mid$(WTEXT,8,9)
      [L]LENID = len(WTEXT)
      Raz [L]STRPOS1
      For I = 1 To [L]LENID
        STRINGCHARCODE = ascii(mid$(WTEXT,I,1))
        If STRINGCHARCODE = 59
          If ![L]STRPOS1
            [L]STRPOS1 = I+1
          Else
            [L]STRPOS2 = I-1
            Raz [L]ACCLEN
            [L]ACCLEN =   [L]STRPOS2 - [L]STRPOS1
            [L]ACCR=mid$(WTEXT,STRPOS1,ACCLEN)
            Break
          Endif
        Endif
        I+=1
      Next
      #JOJAC - X3-194058 - 2020-05-05 - END
    
      CNT=find(ACCR,ACC)
      If CNT=0 and WTEXT <> ""
        If val(right$(WTEXT,len(WTEXT)-5)) > 0
          #JOJAC - X3-194058 - 2020-05-05 - START
          If CNT2 = MAXACC
            #The maximum number of accounts of '$1$' allowed for import has been reached. Please contact your administrator.
            ERRMESS1 = func AFNC.MES1( mess(321,7703,1),MAXACC)
            Gosub ERROR_IMPORT From BSIIMPORT
            [L]STAT = 1
            Return
          Endif
          #JOJAC - X3-194058 - 2020-05-05 - END
          CNT2+=1
          ACC(CNT2)=ACCR
          CNT=CNT2
          Append WFILE(CNT), "H;"+WTEXT + chr$(10)
    
        Endif
      Elsif  CNT > 0 and WTEXT <> ""
        Append WFILE(CNT), "D;"+WTEXT + chr$(10)
        #JOJAC - X3-197015 - 2020-05-27 - START
        DETCNT(CNT) =+ 1
        #JOJAC - X3-197015 - 2020-05-27 - END
      Endif
    Until fstat <> 0
    Openi Using [RL]
    
    WRFILPATH=filpath("tmp", PREPROFILE, "txt")
    
    Openo WRFILPATH, 0 Using [WL]
    Iomode adxirs chr$(13) + chr$(10) Using [WL]
    
    For I=1 To dim(WFILE)
      If WFILE(I)<>""
          #JOJAC - X3-197015 - 2020-05-27 - START
          If DETCNT(I) > 0
          #JOJAC - X3-197015 - 2020-05-27 - END
            Wrseq WFILE(I) Using [WL]
          #JOJAC - X3-197015 - 2020-05-27 - START
          Endif
          #JOJAC - X3-197015 - 2020-05-27 - END
      Endif
    Next
    Openo Using [WL]
    
    FILNAMTMP = filpath("tmp", PREPROFILE, "txt")
    
    Return
    
    $COMBINE_MULTIPLE_FILES
    Local Clbfile WFILE_COMBINE(1): Raz WFILE_COMBINE
    Local Char COMBFILPATH(250)  #COMBFILPATH=filpath("tmp", "PREPROCESS_COMB", "txt")
    Local Char COMBFILE(250),PREPROFILE(250)
    
    COMBFILE    = mid$(IMPORTNAME,1,len(IMPORTNAME)-4)
    PREPROFILE  = mid$(IMPORTNAME,1,len(IMPORTNAME)-4)
    
       Tinyint BUFFER(2)
        Openi FILNAMTMP
        Seek Last-2
        Getseq 2, BUFFER
        Openi
        If BUFFER(0) <> 13 and BUFFER(1) <> 10
            Openo FILNAMTMP, -1
            BUFFER(0) = 13 : BUFFER(1) = 10
            Putseq 2, BUFFER
            Openo
        Endif
    
    Openi FILNAMTMP Using [RL1]
    
    Repeat
      Rdseq WTEXT Using [RL1]
      If WTEXT <> ""
        Append WFILE_COMBINE, WTEXT + chr$(10)
      Endif
    Until fstat <> 0
    Openi Using [RL1]
    
    #adxifs = chr$(10)
    
    Gosub FILE_OPEN_INTERACTIVE
    COMBFILE   +=  "_" + mid$(IMPORTNAME,1,len(IMPORTNAME)-4)
    PREPROFILE  += "_" + mid$(IMPORTNAME,1,len(IMPORTNAME)-4)
    
    Local Char FTIME(8)
    Local Integer LENGH1, LENGH2, LENGH3
    Local Char STR1(250), STR2(250)
    
    FTIME = func AFNC.TIME_FROM_DT(datetime$)
    STR1 = "_COMBINE_"+ format$( "D:YYYY[-]MM[-]DD", date$ ) + "_" + left$(FTIME,2) + "-"+ mid$(FTIME,4,2)
    STR2 = "_PREPROCESS_" + format$( "D:YYYY[-]MM[-]DD", date$ ) + "_" + left$(FTIME,2) + "-"+ mid$(FTIME,4,2)
    LENGH1 = len(COMBFILE)
    LENGH2 = len(STR1)
    LENGH3 = len(STR2)
    
    If LENGH1+LENGH2 > 250 or LENGH1+LENGH3 > 250
      Call ECR_TRACE( mess(303,7703,1), 1) From GESECRAN # 'The length of the filename is limited to 250 characters. The combined filename will be truncated to 250 characters'
    Endif
    
    If LENGH1+LENGH2 > 250
      [L]COMBFILE = mid$(COMBFILE,1,250-LENGH2) + STR1
    Else
      [L]COMBFILE += STR1
    Endif
    
    If LENGH1 + LENGH3 > 250
      PREPROFILE = mid$(PREPROFILE,1,250-LENGH3) + STR2
    Else
      PREPROFILE += STR2
    Endif
    
    Tinyint BUFFER(2)
    Openi FILNAMTMP
    Seek Last-2
    Getseq 2, BUFFER
    Openi
    If BUFFER(0) <> 13 and BUFFER(1) <> 10
      Openo FILNAMTMP, -1
      BUFFER(0) = 13 : BUFFER(1) = 10
      Putseq 2, BUFFER
      Openo
    Endif
    
    Openi FILNAMTMP Using [RL2]
    Repeat
      Rdseq WTEXT Using [RL2]
      If WTEXT <> ""
        Append WFILE_COMBINE, WTEXT + chr$(10)
      Endif
    Until fstat <> 0
    Openi Using [RL2]
    
    Local Char COMBFILPATH(250) : COMBFILPATH=filpath("tmp", COMBFILE, "txt")
    
    Openo COMBFILPATH, 0 Using [CBL]
    Wrseq WFILE_COMBINE Using [CBL]
    Openo Using [CBL]
    
    FILNAMTMP = COMBFILPATH
    
    Return
    
    $FILE_OPEN_INTERACTIVE
    Local Char    IMPORTNAME(GDIMFIC) : Raz IMPORTNAME
    Local Integer STAT                : STAT = 0
    Local Integer FLGIGNDUPNAM        : FLGIGNDUPNAM = 0
    
    If [M:BSIIMP]TYPIMP = 1
      Call COPSRV( IMPORTNAME, [M:BSIIMP]FILNAM, STAT ) From ORDSYS
      If STAT >= 1 Then # 1 = canceled / 2 = error
        Return
      Endif
    Else
      Call SETNAM([M:BSIIMP]FILNAM,IMPORTNAME) From ORDSYS
    Endif
    
    # Import file.
    Gosub $FILE_OPEN_COMMON
    
    Return
    
    $FILE_OPEN_COMMON
    Local Integer FILEFOUND           : FILEFOUND = 0
    Local Char FIELDSEP(10)
    Local Integer IMPTYP
    Local Integer TRANSVERSION
    
    Gosub CHECK_FILENAME
    If REP > 1
      STAT = 1
      Return
    Endif
    
    If !GSERVEUR
      Call OUVRE_TRACE( func AFNC.MES1( mess(30,7703,1), [M:BSIIMP]FILNAM) ) From LECFIC # 'Import of bank statement file '$1$''
    Endif
    
    Call EXISTE( [M:BSIIMP]FILNAM, FILEFOUND ) From ORDSYS
    If FILEFOUND <> 1
      Call ECR_TRACE(func AFNC.MES1( mess(11,7703,1), [M:BSIIMP]FILNAM), 1 )  From GESECRAN #  'File '$1$' could not be found on server.'
      STAT = 1
      Return
    Endif
    
    Local Char FILNAMTMP(255) : FILNAMTMP = [M:BSIIMP]FILNAM
    Read [F:BSIFFM]BSIFFM0 = [M:BSIIMP]FILFMT
    If [F:BSIFFM]FLDSEPTYP = 2 # Numerical ASCII value type
      [L]FIELDSEP = chr$( val([F:BSIFFM]FLDSEP) )
    Else
      [L]FIELDSEP = [F:BSIFFM]FLDSEP
    Endif
    
    [L]IMPTYP = [F:BSIFFM]FILTYP
    
    Return
    
    $CHECK_FILENAME
    Local Tinyint REP : Raz REP
    
    If len( IMPORTNAME ) > 250
      Call ECR_TRACE( mess(62,7703,1), 1 ) From GESECRAN # 'The length of the filename is limited to 250 characters. Please shorten the filename.'
      REP = 2
      Return
    Endif
    Filter [F:BSIIMP] Where [F:BSIIMP]FILNAM = IMPORTNAME
    If rowcount([F:BSIIMP]) > 0
      If GSERVEUR
        If FLGIGNDUPNAM
          Call ECR_TRACE( mess(74,7703,1), -1 ) From GESECRAN # 'A file with the same filename has already been imported.'
        Else
          Call ECR_TRACE( mess(74,7703,1),  1 ) From GESECRAN # 'A file with the same filename has already been imported.'
          REP = 2
        Endif
      Else
        Wrnbox mess(63,7703,1) Using REP # 'A file with the same filename has already been imported. Press Cancel to abort import.'
      Endif
    Endif
    Filter [F:BSIIMP]
    
    Return
    
    

  • 0 in reply to CristinaFaur

    Brilliant! Many thanks Cristina. I appreciate your help.