2 Replies Latest reply: May 15, 2014 10:00 AM by Gareth Horton RSS

    Help With VB Script to search Input File

    ipickwoad _

      I have visited this site many times and found the information exceedingly helpful. This is my first time posting.

       

      I have been using Monarch Pro and Datapump for about 9 months now and I love it. I work in an environment with hundreds of users on a in-house developed system written in dL4 working on a SCO UNIX platform. The popularity of “PUMPING” files is growing very quickly and I need to find a solution that will help input file confusion. We have added an option that a user can enter “PUMP” when asked for a printer and the report is then FTP’ed to a directory which is monitored by Monarch and will run the appropriate project. Here is the rub … many users want the same report and I need a way to discern who is sending it and create the appropriate email distribution. I have asked all users to provide me with a 3 digit code which I have attached to their login, so when user XYZ runs stock status report the output file becomes “xyzstockstatus.txt”. I have been able to create scripts that will email according to filters etc…

       

      if (item.recordcount>1) and (item.filter = "Jefferson") then log.AddDistribution_Email(item.location,namesjefferson,"automatedreports@novamerican.com","","Ready To Ship NSPC","for file " + item.location,True)

       

      But I am unable to find the right “variable to scan” to find the 3 letters. In a perfect world I would search the export file and associate the file with a user.

       

      Hope this is not too confusing. Thank You

        • Help With VB Script to search Input File
          Steve Caiels

          Hi,

           

          Welcome to the new forum. I can’t believe I’ve beaten Grant to the first reply . . . Sorry Grant

           

          This script writes the full path of the input file(s) to c:\datapump\check.txt. You can just set a variable rather and chop it up rather than write it out if you like. But this was the easiest way I could think of testing it!

           

          If you use StreamWriter, you’ll need to add system.io to the Imports section of the References and Imports tab.

           

          Dim itemList As JobLogItemList

          Dim item As JobLogItem

          Dim n As Integer

          Dim check as New Streamwriter("c:\datapump\check.txt")

          Dim fullpath, fname(), ident as string

          itemList = Log.GetInputItems(ProjectID)

          'check to make sure there are import items (avoids an error)

          If itemList.Count = 0 Then ' No input items exist

          'do nothing as there are no input files

          Else

          n = 0

          'Loop through all input reports

                  For Each item In itemList

          n = n + 1 'update the counter

          'item.location returns full path e.g "c:\datapump\inbox\ABCname.txt"       

                  fullpath = item.location

          'Splits full path into separate components based on "\" and loads an array, so fname(0) = "c:\", fname(3) = "ABCname.txt" etc

                  fname = split(fullpath,"\",-1,0)

          'extracts first 3 characters of 4th part of full path.  NB Change fname(X) to required array element.

                  ident = left(fname(3),3)

          check.writeline(fullpath & " " & fname(3) & " " & ident)

          Next

          End If

          check.close

          /code

           

          I'm sure there's a smart way to work out the last part of the fname array, but I couldn't put my finger in it.

           

          Regards,

          Steve

          • Help With VB Script to search Input File
            Gareth Horton

            Hi,

             

            Note that I have copied this thread to the Data Pump Forum.

             

            If you could continue there, I would be grateful.

             

            Thanks

             

            Gareth