4 Replies Latest reply on Nov 1, 2016 7:32 PM by kupietz

    Scripting question - (import Excel files)

    lovelandj

      Hello,

       

      I have a folder on my desktop called files.  There are 30 excel spreadsheets in the folder.

      I am trying to import each spreadsheet (some contain 4 workbooks and some contain 2).

       

      I am going to make two scripts one for the spreadsheets that have 4 workbooks and one for the 2 workbook one. (Unless there is another way to do that?)

       

      I have the following script for the 4 workbook one.

       

      Go to Layout ["Written Data" (Written Data)]

      Show all records

      Import Records [ With dialog: On ;  Source: "2425 CC Vis Com.xlsx"; Worksheet: "Written Data" ; Add; Mac Roman ]

       

      Go to Layout ["Written Norms Data" (Written Norms Data)]

      Show all records

      Import Records [ With dialog: On ;  Source: "2425 CC Vis Com.xlsx"; Worksheet: "Written Norms Data" ; Add; Mac Roman ]

       

      Go to Layout ["Performance Data" (Performance Data)]

      Show all records

      Import Records [ With dialog: On ;  Source: "2425 CC Vis Com.xlsx"; Worksheet: "Performance Data" ; Add; Mac Roman ]

       

      Go to Layout ["Performance Data" (Performance Norms Data)]

      Show all records

      Import Records [ With dialog: On ;  Source: "2425 CC Vis Com.xlsx"; Worksheet: "Performance Norms Data" ; Add; Mac Roman ]

       

      My question(s) are;

       

      1. Have the user tell Filemaker where the folder is that contains all the files?

      2. Can I modify this script to run all files in the folder no mater what the name of the file is?

       

      Thanks

      Jeremy

        • 1. Re: Scripting question - (import Excel files)
          erolst

          lovelandj wrote:

          1. Have the user tell Filemaker where the folder is that contains all the files?

          You may be able to do that using the Get Directory [] script step.

           

          lovelandj wrote:

          I am going to make two scripts one for the spreadsheets that have 4 workbooks and one for the 2 workbook one. (Unless there is another way to do that?)

           

          2. Can I modify this script to run all files in the folder no mater what the name of the file is?

           

          If you're on a Mac, that should be easily feasible using an AppleScript to open a folder selection dialog and read in a file list; then use a loop where you put each individual path into the variable that you pass to the Import step.

           

          If you're willing to try your hand at AppleScript, I could dig out a sample script for you.

           

          If you're willing to dig deeper and try your hand at the (arcane) Excel AS dictionary, you should also be able to address individual workbooks.

           

          I imagine a similar approach is possible under Windows.

          • 2. Re: Scripting question - (import Excel files)
            kupietz

            Well, I hate to give a short summary of a kind of technical answer, so ask if you need more detail than this, but:

             

            1.) Set up a separate table with a container field
            2.) Have your script import the folder-ful of documents, as a folder, into the container field

            3.) Have your script loop through the imported records
            4.) For each record, export the container contents to the Temporary Path using get(TemporaryPath) and a standard filename defined by your script.
            5.) Now your script knows where the file is, and what it's called. Import it into your data table.
            6.) Delete the record from the container table.
            7.) Repeat for all imported container records.

            • 3. Re: Scripting question - (import Excel files)
              philmodjunk

              I don't think that will work unless they slipped in an upgrade to the folder option for import records. That option previously only worked to import text or image files. Excel files, unless exported as csv, are neither.

              • 4. Re: Scripting question - (import Excel files)
                kupietz

                Right you are. But I actually got this working a different way in FM14. It's slightly involved, but doable. You have to have a designated folder with a set name within your Documents folder, not on your desktop. Let's call this folder "ExcelFiles".

                 

                1. Use Get(DocumentsPathListing) to get a list of everything in the Documents folder. Store this in a variable.

                2. Use a loop (or recursive custom function) to create a list of only the file paths containing "/Documents/ExcelFiles/" and ending in ".xls" or ".xlsx" from that first variable. Store this in a second variable.
                3. Use a loop to successively plug each value from this second variable into a third variable and use that third variable as the file reference in an Import script step.

                 

                I've actually got this live and working on a client site right now. Maybe I'll throw together a little demo.

                 

                In my client's case, they have the 360Works Scribe plugin installed, so I used the plugin function

                ScribePatternMatchAll( $documentsFolderListing, ".*\/Documents/ExcelFiles\/.*.xls.?";"flags=multiLine+caseInsensitive")

                to get the list of only Excel documents within Excel Import Folder from the Documents Folder listing without needing a loop.