Advanced Bank Statement Import (BSI) Preprocessing

Question

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

CristinaFaur · Accepted 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 Fullscreen BSIIMPORTSBSA.txt Download #@(#)0.0.0.0 $Revision$ #@(#)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

Sage X3 UK

Advanced Bank Statement Import (BSI) Preprocessing

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING