4 Replies Latest reply on Apr 20, 2017 2:52 PM by philmodjunk

    Capture a filename during an import

    ejolesch_1

      Title

      Capture a filename during an import

      Post

      I am trying to automate (as much as possible) an import process and need some guidance/assistance.

      I am importing an Excel spreadsheet where I need to capture the filename too.  My goal is to strip part of the filename out and use it as an event name for the records I have just imported.

      Currently I am importing the Excel file, verifying the import structure, and then manually adding the event name to the names I have just imported.  

      If you can offer guidance or point me to an example I would appreciate it.

       

      Thanks

      Elliot

       

        • 1. Re: Capture a filename during an import
          philmodjunk

          This can be auto-mated, but you will lose the ability to "verify the import structure" so you will want to use this method only if you can be confident that the source file will always have the same structure in terms of rows and columns of data.

          Import Records can use a $Path variable in its reference to the file to be imported. So if you can capture a file path to the source file and set a variable to it, such a $Path variable can be made to work for you in such a script.

          Set up a container field (can be a global container field) with an Insert File script step such that the user get's an open file dialog. They select a file and the insert file step inserts the file "by reference" into the container field. You can then use a set variable script step to extract the file path from the inserted file reference and set a $path variable to it for use by your import records script step.

          There are a number of nuances to this, both the calculation needed to extract a valid file path from the container field and how to set up the file reference inside the Import Records script step that properly retains your import settings. See the following thread and exploration file for more on these details: Exploring the use of a $Path Variable in Scripts

          • 2. Re: Capture a filename during an import
            ejolesch_1

            PhilModJunk -

            Thank you for your reply and insight.  I will review the thread you pointed me too.

            Unfortunately, this year, my import structure is not stable so I will have to perform this manually.  My plan is to work with my client and create a consistent, verifiable structure that can be used in the years going forward.

            Elliot

            • 3. Re: Capture a filename during an import
              canoevalley

              Do you have an update to this Phil? When my user imports the Excel sheet, I want to drop the name of that file in a field. That way I know what file was imported.

               

              Currently, we call the excel file something like DataFile.04.20.17. This way I know we have the latest version of the file if the user updates the data on their excel file.

               

              Thanks.

              • 4. Re: Capture a filename during an import
                philmodjunk

                Since the $Path variable contains the file name as the last part of the file name, you can extract that value from the file path to enter into a field.

                 

                I've been using a method that is now on the "deprecated list" according to the FileMaker road map of future versions:

                 

                I use Insert File to insert a reference to the file into a container field. I can then extract the filepath to the file from the container field, assign it to a $Path variable and import. That works in 15, but it is unknown if it will work in 16 due to that entry so caveat emptor.

                 

                I have added a product Idea that when they remove "store a reference" as an option for container fields, they add some new feature that gives us this same ability to open a dialog and select a file for import in order to get the file path to the selected file.

                 

                If/when you remove "store a reference"--please add a new feature for accessing file paths