6 Replies Latest reply on Oct 14, 2016 5:27 PM by philmodjunk

    Scheduled import with dynamic timestamp

    rcmurphy@mac.com

      Hi, I'm hoping there is a simple answer to this question. I have a scheduled script running nightly that imports records from a file.

       

      The filenames are standardized with "Import" then date: Import_20161008

       

      Current script step for the filepath is:

      Set Variable [ $filePath ; Value:"filewin:" & $LandingZone & $region & "Import_" & Year ( Get(CurrentDate) ) & Right ( "00" & Month ( Get ( CurrentDate ) ) ; 2 ) & Right("00" & Day ( Get ( CurrentDate ) ) ; 2) & ".csv"

       

      The new filenames are going to include a timestamp, rather than just the date: Import_20161008_1463.csv

       

      My question is: )other than manually removing the 4 numbers that represent the time) how do I account for the (dynamic) time in the filepath?

       

       

        • 1. Re: Scheduled import with dynamic timestamp
          Malcolm

          If you're able to move the data files into the Server document folder then you can use Get(DocumentPathListing) to obtain a list of file names.

           

          p.s. Using that command outside the server context will tie up your machine while fmp generates a complete list of your documents folder.

          • 2. Re: Scheduled import with dynamic timestamp
            BowdenData

            I agree with Malcolm about getting a listing of the directory where the import files reside. If you are not running this on a FM server or it is not possible to have the files in the server documents folder, then I would suggest using a plugin that can do file listing. There are several out there. An excellent free one is BaseElements from Goya.

             

            I don't know what you do with the files after importing, but these plugins can delete/copy/move the files.

             

            Doug

            • 3. Re: Scheduled import with dynamic timestamp
              rcmurphy@mac.com

              Hi, thank you for your response. I am running this on FM Server, and the files do reside in the documents folder.

              Every day, a new file is placed in the documents folder and a scheduled script grabs the file and imports new records into the solution.

               

              I'm not sure how obtaining a list of file names will allow me to import a file with a dynamic timestamp. I know how to account for a dynamic date in the file name, but not a dynamic time.

               

              (i.e. I know how to set up the file path when there is a date in the file name (such as Import_20161008.csv). But I don't know how to set up the file path when there is a dynamic time in the file name (such as Import_20161008_1463.csv)

              • 4. Re: Scheduled import with dynamic timestamp
                BowdenData

                In order to import the file(s), you need the full name of the file(s). If you use the documents path listing function, it will give a list of the full file names. Now loop through that list looking for file names that contain the current date (that you have determined like in your current calc). When you have a match for containing the date, then grab the entire filename/entry from the list.

                 

                Then just import port with that. I would use a LET statement to set variables like the current date and the list of files in the documental folder and then of course a loop function to process the list.

                 

                Let me know if this doesn't make sense. I am sending this from my phone, so can't paste in some examples.

                 

                Doug

                • 5. Re: Scheduled import with dynamic timestamp
                  rcmurphy@mac.com

                  Thank you so much for your help. I tried to tackle this today and saw that we have hundreds of files in our server documents folder, with some nested in subfolders. I was really hoping for some sort of a 'wild card' substitute or some sort of calculation that would represent a 'range from 0001...9999' for those 4 numbers that hang off the end of the filename (Import_20161008_1463.csv).

                   

                  If this isn't possible, I'll make another attempt at looping through all the filenames with Get(DocumentPathListing) as suggested. Would you happen to have a sample  script that shows this?

                  • 6. Re: Scheduled import with dynamic timestamp
                    philmodjunk

                    Another approach is to use a plug in for listing the files in a folder. That can avoid listing the contents of other subfolders and you can thus have a smaller list to work with.

                     

                    A systemscript can be used to generate a list of the files in a folder as well--VBScritp or a batch file of command lines come to mind.