none
Help with VB script RRS feed

  • Question

  • Hi All I would like assistance with an import script for a financial system I am working on. We have a VB script that reads lines in a .csv file (the file contains customer transactions for a booking system and imports them to a staging table in the financial system. Sometimes the file we receive contains only one line (transaction) and it doesn't contain a grand total beneath.

    I would like to know how I can set it so that if there is no grand total line in the file then it creates one based on the single line transaction that exits already? Any assistance would be greatly appreciated.

    Script is below:


    Imports T1.F1.Public
    Imports T1.F1.GLL
    Imports System.IO
    References System.Web.Services
    References T1.F1.Public
    References T1.F1.GLL
    
    Dim lsServerFolderCode as String = T1.TB.Par.Params("WW_INV_IMP_PATH").Value
    
    Dim loFilePath as New T1.TB.IO.DirectoryCode
    loFilePath.Code = lsServerFolderCode
    loFilePath.DoRead()
    
    if loFilePath.Code.Length = 0 Then
                Utility.SysMessage (ExecSQL.User, "WW Import", "Unable to determine import path from system variable WW_INV_IMP_PATH.  Script Terminated.")
                Results.ReturnValue = False
                Exit Sub
    End If
    
    Dim fiTemp As FileInfo
    Dim File_Path As String = loFilePath.AbsolutePath  
    Dim File_Name As String = "Reconciliation*.csv"
    Dim Log_File_Path As String = String.Concat(File_Path, "Script_Log\")
    Dim Log_File As String = Replace(EXECSQL.TODAY,":","") & ".log"
    Dim Processed_Path As String = String.concat(File_Path, "Processed\")
    Dim Processed_File As String = String.Concat(Replace(EXECSQL.TODAY,":",""), "_", File_Name)
    Dim Failed_Path As String = String.concat(File_Path, "Failed\")
    Dim Failed_File As String = String.Concat(Replace(EXECSQL.TODAY,":",""), "_", File_Name)
    Dim strImportName As String = "ES_WWSGL"
    Dim susp_acc as string = "999-99999-99999"
    
    //Check all file paths and create if necessary
    Dim lbLogFilePathExists as Boolean = System.IO.Directory.Exists(Log_File_Path)
    Dim lbProcessedPathExists as Boolean = System.IO.Directory.Exists(ProcesseD_Path)
    Dim lbFailedPathExists as Boolean = System.IO.Directory.Exists(Failed_Path)
    
    If Not (lbLogFilePathExists) Then
                System.IO.Directory.CreateDirectory(Log_File_Path)
    End If
    
    If Not (lbProcessedPathExists) Then
                System.IO.Directory.CreateDirectory(Processed_Path)
    End If
    
    If Not (lbFailedPathExists) Then
                System.IO.Directory.CreateDirectory(Failed_Path)
    End If
    
    
    //Remove Log File if it already exists
    If (My.Computer.FileSystem.FileExists(Log_File_Path & Log_File) = True) Then
        My.Computer.FileSystem.DeleteFile(Log_File_Path & Log_File)
    End If
    
    //Create New Log File
    StatLog.FileName = Log_File_Path & Log_File
    
    //Graham Oakford 01/12/2009
    //Remove any existing data from GLF_BAT_IMP_TRANS to avoid duplicates & Index violations
    ExecSQL.Begin 
        delete from glf_bat_imp_trans where imp_name = 'ES_WWSGL'
    ExecSQL.End 
    ExecSQL.Commit
    
    
    StatLog.Write("Starting script COM_WW_INV_IMP at " & ExecSQL.Now)
    
    Try
    
                Dim di As New DirectoryInfo(File_Path)
                Dim fi As FileInfo() = di.GetFiles()
                If di.GetFiles.Length > 0 Then
    
                            // Get the names of the files in the import directory.
                            For Each fiTemp In fi
                                        // Load data from import file into Document File
                                        If LoadData(File_Path, fiTemp.Name, strImportName) Then
                                                    // Move successfully imported file to Processed directory
                                                    My.Computer.FileSystem.MoveFile(File_Path & fiTemp.Name, Processed_Path & Replace(EXECSQL.TODAY,":","") & "_" & fiTemp.Name, True)
                                                    StatLog.Write("Import of " & fiTemp.Name & " was successful. File has been moved to " & Processed_Path)
                                        Else
                                                    // Move unsuccessfull import file to Failed directory
                                                    My.Computer.FileSystem.MoveFile(File_Path & fiTemp.Name, Failed_Path & Replace(EXECSQL.TODAY,":","") & fiTemp.Name, True)
                                                    StatLog.Write("Import of " & fiTemp.Name & " was unsuccessful. File has been moved to " & Failed_Path & " as " & Failed_File & ".")
                                        End If
    
                            Next fiTemp
    
                Else
                            StatLog.Write("***There is no file to process. The directory is empty***")                
                End If
    
                Catch ex As Exception
                StatLog.Write(ex.Message.ToString())
    
    End Try
    
    // Finish
    StatLog.Write("Completing script COM_WW_INV_IMP at " & ExecSQL.Now)
    
    Function LoadData(ByVal strDirectoryName As String, ByVal strFileName As String, ByVal strImportName As String) As Boolean
                // Variables
                Dim ImportFile As StreamReader = File.OpenText(strDirectoryName & strFileName)
                Dim strLine as string
                Dim txtArray() As String
                Dim Validate as Boolean
                Dim myAccount As String
                Dim lschartName as String = ""
                Dim lsOldAccountLength as Integer = 0
                Dim intLine as integer = 0
                dim strGSTRateCode as string
                dim myexsql as integer
                dim mydate as string
                myexsql=1
                @ImportName = strImportName
                @bBAT_NARR3 = mid(strFileName,len(strfilename)-39,40)
    StatLog.Write(strFileName)
    StatLog.Write(@bBAT_NARR3) 
                // Remove existing rows from import table
                ExecSQL.Begin
                            Delete GLF_BAT_IMP_TRANS where IMP_NAME = @ImportName and LNE_NARR3 = @bBAT_NARR3
                ExecSQL.End
                ExecSQL.Commit
    
                @laccnbr = ""
                
                Try
                            // Get data from file
                            strLine = ImportFile.ReadLine() //Remove if no header line
                            strLine = ImportFile.ReadLine() // Second line also a header
                            strLine = ImportFile.ReadLine()
    
                            StatLog.Write(vbNewLine & strLine)
                            txtArray = split(strLine,",")
                            txtarray(1)=replace(txtarray(1),chr(34),"")
    
                            StatLog.Write(txtarray(1))
                            StatLog.Write(txtarray(1).length)
                            If UBound(txtArray) > 0 Then
                                        Do
                                                    StatLog.Write(txtArray(1))
                                                    StatLog.Write(txtArray(1).length)
                                                    
                                                    //if txtArray(1).length >0    then // check to see if a GL number exists or the line is the total before processing the line
                                                                intLine = intLine + 1
                                                                @lineid = intLine
    
                                                                StatLog.Write("intLine")
                                                                StatLog.Write(intLine)
    if intline >200 then
                StatLog.Write("ERROR - exit loop due to count value")
                exit do
    end if
                                                                @docid = "1"
                                                                @dref1 = "WWR01"
                                                                //ExecSQL.DefineDate(@ddate1)
                                                                //@ddate1 = EXECSQL.TODAY
                                                                StatLog.Write("date")
                                                                //StatLog.Write(@ddate1)
                                                                StatLog.Write("Date portion of filename")
                                                                mydate=replace(mid(strFileName,instr(strFileName,"(")+1,instr(strFileName,")")-(instr(strFileName,"(")+1)),"_","-")
                                                                StatLog.Write(mydate)
                                                                mydate=mid(mydate,1,len(mydate)-6)
                                                                StatLog.Write(mydate)
                                                                @ddate1=mydate
                                                                @dsource = "WW"
                                                                @dtype = "WWSJNL"
                                                                
                                                                @lldgcode = "GL"
                                                                            /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                                                                            //This section Translates the legacy account codes into Technology One account code
                                                                            /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    StatLog.Write("got to here")
                                                                @OLD_ACCNBR = "000-00000-41003"
    
                                                                if txtArray(1).length=0 then
                                                                            @OLD_ACCNBR = "999-99999-99999"
                                                                else
                                                                            if replace(txtArray(1),chr(34),"") <> "Grand Total" then 
                                                                                        @OLD_ACCNBR = txtArray(1) //Read from Line
                                                                            else
                                                                                        // balance sheet number
                                                                                        @OLD_ACCNBR = "000-00000-41003"
                                                                            end if
                                                                end if
                                                                StatLog.Write("got to here2")
    StatLog.Write(@OLD_ACCNBR)
    
                                                                If @lldgcode = "GL" Then
                                                                            @OLD_ACCNBR = replace(Replace(@OLD_ACCNBR, "-", ""),chr(34),"") //Get rid of spacer characters
    
                                                                            lsOldAccountLength = @OLD_ACCNBR.Length
    StatLog.Write("got to here3")
                                                                            @OLD_ACCNBR = mid(@OLD_ACCNBR,1,lsOldAccountLength)
                                                                            @laccnbr = @OLD_ACCNBR
                                                                            lsChartName = "GLCHT"
                                                                Else
                                                                            @laccnbr = Replace(@OLD_ACCNBR,"TCM","").PadLeft(6,"0").Substring(0,6)
                                                                            lsChartName = "ARCHT"
                                                                End If
    StatLog.Write("got to here4")
                                                                myAccount = @laccnbr
    
                                                                StatLog.Write("myAccount")
                                                                StatLog.Write(myAccount)
                                                                // have to get the final 5 digits of the GL code to determine rate code
                                                                @NAT_ACCNBR = mid(myAccount,myAccount.length-4,5)
                                                                StatLog.Write(@NAT_ACCNBR)
                                                                
                                                                @lnarr1 = replace(txtArray(0),chr(34),"")
    															@lnarr2 = "Newbook"
                                                                @lgsttype = "E"
    
                                                                /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                                                                //This section Translates the legacy Tax Segment into Technology One GST Code
                                                                /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////       
                                                                //Get translated GST code
                                                                ExecSQL.Begin
                                                                            SELECT VAT_RATE_CODE_DEF FROM GLF_CHART_ACCT WHERE CHART_NAME = 'NATACCT' AND ACCNBRI = @NAT_ACCNBR
                                                                ExecSQL.End
                                                                //ExecSQL.Commit
    StatLog.Write("got to here5")
                                                                //Save to variable for validation and use later
                                                                strGSTRateCode = ExecSQL.Value("VAT_RATE_CODE_DEF",1)
                                                                @lgstratecode = strGSTRateCode
                                                                @lamount1 = CDbl(replace(txtArray(3),chr(34),""))
                                                                @lgstamount = CDbl(replace(txtArray(5),chr(34),""))
                                                                @lgstexamount = CDbl(replace(txtArray(4),chr(34),""))
                                                                if replace(txtArray(1),chr(34),"") = "Grand Total" then
                                                                            @lamount1 = @lamount1 * -1
                                                                            @lgstamount = @lgstamount * -1
                                                                            @lgstexamount = @lgstexamount * -1
                                                                end if
                                                                            
    StatLog.Write("got to here6")
    
                                                                // have to get the date information from the filename
                                                                // varchar(8)
                                                                @bimpname = "shtchar"
                                                                
                                                                //varchar(10)
                                                                @bdoctype = "WWSJNL"
    															
    															@
    
                                                                /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                                                                //This section validates the Technology One account code NB: HARD CODED CHART_NAME
                                                                /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                
                                                                Validate = ValidateLedgerAccount(lsChartName,myAccount)
    
                                                                If Validate = False Then
    																		
                                                                            StatLog.write (vbcrlf & "Account '" & myAccount & "' is invalid for Chart " & lsChartName & "'" & vbcrlf)
                                                                Else
                                                                            StatLog.Write (vbcrlf & "Account '" & myAccount & "' is a valid account for the " & lsChartName & " Chart." & vbcrlf)
                                                                End If
                                                                //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////      
                                                                if myexsql=1 then
                                                                ExecSQL.Begin
                                                                            INSERT INTO GLF_BAT_IMP_TRANS (
                                                                                        IMP_NAME,
                                                                                        DOC_ID,
                                                                                        LNE_ID,
                                                                                        DOC_REF1,
                                                                                        DOC_DATEI1,
                                                                                        DOC_SOURCE,
                                                                                        DOC_DOC_TYPE,
                                                                                        LNE_LDG_CODE,
                                                                                        LNE_ACCNBRI,
                                                                                        LNE_AMT1,
                                                                                        LNE_NARR1,
    																					LNE_NARR2,
                                                                                        LNE_VAT_TYPE,
                                                                                        LNE_VAT_RATE_CODE,
                                                                                        LNE_VAT_AMT,
                                                                                        LNE_VAT_EXC_AMT,
                                                                                        BAT_NAME,
                                                                                        BAT_DOC_TYPE,
                                                                                        BAT_NARR3,
    																					LNE_USER_FLD10)
                                                                            VALUES (
                                                                                                                                                                
                                                                                                                @ImportName,
                                                                                                                @docid,
                                                                                                                @lineid,
                                                                                                                @dref1,
                                                                                                                @ddate1,
                                                                                                                @dsource,
                                                                                                                @dtype,
                                                                                                                @lldgcode,
                                                                                                                @laccnbr,
                                                                                                                @lamount1,
                                                                                                                @lnarr1,
    																											@lnarr2,
                                                                                                                @lgsttype,
                                                                                                                @lgstratecode,
                                                                                                                @lgstamount,
                                                                                                                @lgstexamount,
                                                                                                                @bimpname,
                                                                                                                @bdoctype,
                                                                                                                @bBAT_NARR3,
    																											@impErrAcc)
                                                                            
    
                                                                ExecSQL.End
                                                                ExecSQL.Commit
                                                                //StatLog.Write(@ImportName)         
                                                                end if
    
                                                    //else
                                                    //          StatLog.Write("row not processed, Moving on")
                                                    //End if
                                                    
                                                    strLine = ImportFile.ReadLine()
                                                    if len(strline)>0 then
                                                                StatLog.Write(vbNewLine & strLine)
                                                                txtArray = split(strLine,",")
                                                                txtarray(1)=replace(txtarray(1),chr(34),"")
                                                    end if
                                        Loop until StrLine is nothing
    
                            End If
    
                            importfile.Close()
                            return true
                            
                            Catch ex As Exception
                                        StatLog.Write(ex.Message.ToString())   
                                        importfile.close()
                                        return false
                End Try
    End Function
    
    
    //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    Function ValidateLedgerAccount(ChartToValidate as String, AccountToValidate as String) as Boolean
        Dim loF1_Services As New F1_Services
        Dim loRequestChartAccount_DoValidate_Request As New T1.F1.Public.ChartAccount.ChartAccount_DoValidate_Request
    
        With loRequestChartAccount_DoValidate_Request
            .ChartName = ChartToValidate
            .AccountNumber = AccountToValidate
            .Version = New T1.Tb.ApplicationVersion.AppVersion
            With .Version
                .Major = 0
                .Minor = 0
                .Revision = 0
                .Patch = 0
            End With
        End With
    
        Dim loChartAccount_DoValidate_Response As T1.F1.Public.ChartAccount.ChartAccount_DoValidate_Response
        loChartAccount_DoValidate_Response = loF1_Services.ChartAccount_DoValidate(loRequestChartAccount_DoValidate_Request)
        With loChartAccount_DoValidate_Response.Errors
            if .IsError then
                return false
            else
                return true
            end if
        End With
    End Function





    • Edited by thebrittjoe Friday, June 23, 2017 2:59 AM
    • Moved by Bill_Stewart Wednesday, July 26, 2017 7:43 PM Question far outside forum scope
    Friday, June 23, 2017 2:50 AM

All replies

  • This is not a VBScript, it is  VB program that cannot possibly be working anywhere as it has many syntax violations.

    I recommend that you find a VB programmer to help with this.

    This is NOT a VB forum.


    \_(ツ)_/

    Friday, June 23, 2017 4:38 AM