7 Replies Latest reply on Apr 11, 2014 11:44 AM by philmodjunk

    FileMaker import XLSM data

    jasongan

      Title

      FileMaker import XLSM data

      Post

           Hi, FM Experts:

           Is there a way that FileMaker importing can detect which file extension before import?

           FM can't import data properly from XLSM file. Even the sheet I am importing only has 1 row, but because the file has macro and is saved as XLSM file, when FileMaker imports this XLSM file (forgot to save it as XLSX file), it imports forever and it has created 114000 garbage records already, and I couldn't stop the process.

           So if I can detect the selected file extension, I can then stop the importing and give a warning message.

           Please advise.

            

           thanks,

           Jason

            

            

        • 1. Re: FileMaker import XLSM data
          philmodjunk

               set up a script that uses Insert File to insert the file by reference into a container field. (it can be a global field.)

               The user gets a dialog box where they select the file, but then your script can extract the file path from this container field to use with Import Records to import the data. Since this file path will contain the file extension, your script can check the file extension. You can also use the options for Insert File to limit what file extensions can even be seen in the dialog to insert in the first place.

               Note: This method requires two file references inside the Import Records script step. The upper reference must be the name of your $path variable. The second reference should be a reference to a copy of the typical file from which you will import that is valid at the time you set up the script and specify your import options. This file is used to specify the column to field mapping for the import so that it will be retained in the script step.

          • 2. Re: FileMaker import XLSM data
            jasongan

                 Hi Phil,

                 how to extract the file path from the container field?

                 I found the following your old posting , but it doesn't work. I imported a test.xlsm file into the container, and try to extract the filepath.

                  Are you sure GetValue (containerfield;2) can get filepath of the container file?

                  

                 Phil's old post: 

                 Define calculation fields that returns text and use these expressions to return the file path of a store by reference file:

                 cFilePath: GetValue ( containerField ; 2 )
                 cFileName: Middle ( cFilePath ; Position ( cFilePath ; "/" ; Length ( cFilePath ) ; - 1 ) + 1 ; 999 )

                  

                 --

                 Thanks,

                 Jason

            • 3. Re: FileMaker import XLSM data
              philmodjunk

                   Yes, GetValue ( ContainerField ; 2 )

                   will return the file path, If the following is true:

                   Insert File, not Insert Picture was used to insert the file.

                   The "Store a reference" option was specified for the insert.

                   For more on $Path variables, container fields and script steps that can use a $Path variable, see: Exploring the use of a $Path Variable in Scripts

              • 4. Re: FileMaker import XLSM data
                jasongan

                     Thank you Phil.  It works. I didn't select "store a reference" for Insert File. that was why I did't see the calculation work.

                     Can you please explain a little more about "You can also use the options for Insert File to limit what file extensions can even be seen in the dialog to insert in the first place." ?

                      

                Thanks again,

                Jason

                      

                • 5. Re: FileMaker import XLSM data
                  philmodjunk

                       With FileMaker 12 and newer, you have a check box "Dialog Options", that opens the dialog shown in the attached screen shot. The first part of this dialog is where you can specify what file extensions are permitted to appear in the insert file dialog.

                       And there is also a place to specify that only "store a reference" be specified for the insert so that a user can't use the wrong insert method.

                  • 6. Re: FileMaker import XLSM data
                    jasongan

                         I know why I didn't get it. I am using FM 11. I am too behind. I need to upgrade.

                         Thanks

                         Jason

                    • 7. Re: FileMaker import XLSM data
                      philmodjunk

                           My finding that option in Insert File was one of the more pleasant surprises when I started using FileMaker 12.