2 Replies Latest reply on Mar 29, 2014 10:08 AM by hjgunn@mmedia.is

    Serious flaw importing data from Excel files?

    hjgunn@mmedia.is

      I have three folders that contain up to 100 Excel documents each.

      All documents in one folder are the same type with the same number of workbooks named the same in each document

       

      I have three scripts, one for each folder.

       

      In each script I let the user select the folder to import from.

      I then populate a $ variable with the folder path

      Another $ variable is then populated with a list of the folder's contents

      I then open a loop and import data from each of the douments into up to five tables in FM.

       

      Works like a charm except that it just get's the data from each documents first workbook.

      It seems FM has forgotten to add an entry method for the Workbook Parameter.

      It is being displayed as Workbook = "" in the script step but no entry method seems to be available

       

      ===

       

      Manually I can tell FM to get data from the correct workbooks but in script it never invites me to enter a $Var_Workbook parameter.

       

      Is this really so or am I missing something here?

       

      ===

       

      A very clumsy workaround would be to:

      First populate the folders with all the files needed.

      Then add one Import script step for every file in that folder.

      Then import

       

      Next time I need to import the files I would then have to make sure that the files are named in the same way as before and if I add any new files I would need to add more import script steps to my scripts.

      Besides if the client new that I implemented something like this he would probably think me nuts and go look for a developer that knew what he was doing. :-(

       

      ===

       

      It's just impossible that a company like FM oversaw the need for a vital option like this.

      So please tell me what I'm overseeing here...

        • 1. Re: Serious flaw importing data from Excel files?
          hjgunn@mmedia.is

          I found an arcaine way in the archives to solve my problem.

           

          It was posted by John Morina from Pueblo systems in the year 2007.

           

          ===

           

          In the loop where I import the data I add a command that changes the name of the file on disk to some generic name like "importfile.xlsx".

          I then import the Excel data using "importfile.xlsx" in a fixed import script step

          I then rename the file to it's original name before repeating the process with the next file.

           

          This does of course require a plugin, but at least it provides a dynamic way to import the Excel data into FM.

           

          ===

           

          What makes me wonder though is the fact that this flaw / limitation in Filemaker has been around since before 2007.

          Which I feel is pretty amazing in it self.

           

          I therefore wonder what it might take to get FMI to fix some of the older code in FM before adding more features?

           

          ===

           

          In addition to the above I do of course need to add a test for .xls versus .xlsx before importing the data.

          • 2. Re: Serious flaw importing data from Excel files?
            hjgunn@mmedia.is

            How do I mark this subject as closed?