7 Replies Latest reply on Jan 30, 2017 11:44 AM by frimages

    Automatically importing a large number of mixed files

    frimages

      I have imported an Excel sheet into FMP 15. The fields are labelled with the column headings from the Excel file. The first column/field is a unique record number starting at 00001. At present the last number is 05078 with a few more records being added to the master file each day. The FMP app works fine although some fine tuning to the layout is required.

       

      Each record has a corresponding file (jpeg, doc, docx or PDF) each of which has a file name comprised of letters and numbers. Following the number is a short text description of the contents of the file. For example: ‘chs00001 Old Town Hall pre 1830.jpg’, ‘chs00002 Old Town Hall 1863 sketch.jpg’,’chs00003 Old Town Hall brief history.PDF’. All of the files are held in a legacy Windows directory structure, some 25 master folders and many sub folders.

       

      An added complication is that some files have a – (dash) followed by a number to indicate there are additional files related to the main file e.g. ‘chs00010 Old Fire Station.jpg’ is the main file and ‘chs00010-01 Old Fire Station interior.jpg’ is the additional file. These additional files are listed on a separate sheet in the Excel file. I should add that the Excel sheet does not have a column showing the filename or path.

       

      Thanks to help from philmodjunk and others last year when I was working on a trial version of FM I am able to insert (store as a reference only) jpegs and PDFs manually into a container on a ‘Data entry’ layout which also allows Word docs to be inserted and opened in a temporary folder. However with over 5000 image/document files I need a method of importing the files automatically. I have tried File/Import Folder but it only imports jpegs and does not put them in the correct record. I have attempted to produce scripts/calculations but I’m getting nowhere and I’m hoping that the FileMaker Community might come to my rescue.

       

      Thanks in advance.

      Frank – a struggling newbie

      FileMaker Pro 15.0.3.305 under Windows 10

        • 1. Re: Automatically importing a large number of mixed files
          philmodjunk

          Where exactly is this data?

          Each record has a corresponding file (jpeg, doc, docx or PDF) each of which has a file name comprised of letters and numbers. Following the number is a short text description of the contents of the file. For example: ‘chs00001 Old Town Hall pre 1830.jpg’, ‘chs00002 Old Town Hall 1863 sketch.jpg’,’chs00003 Old Town Hall brief history.PDF’.

          Is that data in FileMaker or Excel? Is it text all in one field/cell or two?

           

          In general, there are plug ins and system scripts/batch files that can list the files found in a given folder so you can set up a script that gets the list of files in a folder--thus determining both the file names and the paths and can then loop through that list to insert the files one at a time into container fields.

           

          If all your files were located inside the computer's documents folder, you can do this without relying on a plug in or system script.

          • 2. Re: Automatically importing a large number of mixed files
            frimages

            Thanks for replying so quickly.

             

            All of the image and document files are stored on the same PC drive as FileMaker, local disc (C)  in

            C:/Data/folder aaa/folder bbb/etc. The filenames are a combination of text and numbers.

            I put them near the top of the tree so as not to have too many levels in the folder structure. The filenames are not in Filemaker or Excel.

            I could move the files to the documents folder if that would help.

            The only common denominator is that the FileMaker record '01234' should contain file 'chs0124 The Cattle Market 1911'

            Thanks

            Frank

            • 3. Re: Automatically importing a large number of mixed files
              frimages

              Correction

               

              The only common denominator is that the FileMaker record '01234' should contain image file 'chs01234 The Cattle Market 1911.jpg'

               

              Frank

              • 4. Re: Automatically importing a large number of mixed files
                philmodjunk

                But none of that answered my question. Please read my last post again. I did not ask for the location of the files.

                 

                It looks to me like you have two issues a small one and a big one. The small one is parsing that data into a better--more readily used format and a big one - inserting each of these files (or a reference to them) into container fields of your database.

                 

                The big problem is going to require getting a list of the files in each such folder. A system script (Such as VB Script to name just one example) or windows batch file can generate a text file listing the folder contents. This file can then be imported into FileMaker to get you the needed info in order to insert the listed files. Such a script or batch file can be created and run from FileMaker via a script. A plug in makes the process much simpler, but you'll need to acquire that plug in which can cost you some money--so you'll need to decide on the approach that you want to pursue.

                • 5. Re: Automatically importing a large number of mixed files
                  frimages

                  I'm sorry if I misunderstood your question.

                   

                  Where exactly is this data?

                  The data from the Excel sheet is in FileMaker.

                  Each record has a corresponding file (jpeg, doc, docx or PDF) each of which has a file name comprised of letters and numbers. Following the number is a short text description of the contents of the file. For example: ‘chs00001 Old Town Hall pre 1830.jpg’, ‘chs00002 Old Town Hall 1863 sketch.jpg’,’chs00003 Old Town Hall brief history.PDF’.

                  Is that data in FileMaker or Excel? Is it text all in one field/cell or two?

                  The other data, the actual jpeg image files etc are not in either FileMaker or Excel. The filenames are also not in the Excel sheet or FileMaker.

                   

                  If all your files were located inside the computer's documents folder, you can do this without relying on a plug in or system script.

                  How?

                   

                  Thanks

                  • 6. Re: Automatically importing a large number of mixed files
                    philmodjunk

                    Ok, I was misinterpreting your original post that you somehow had a text description of each file. Never mind.

                     

                    If your documents are loaded inside the Documents folder, there's a get function that will list every file and folder inside the documents folder that you can than parse to get the paths and filenames of each file you need to insert into a container field.

                     

                    Get ( DocumentsPathListing )

                    • 7. Re: Automatically importing a large number of mixed files
                      frimages

                      Thanks I can see why - I'll try to be clearer next time.

                       

                      I'll look through FM help and investigate Get ( DocumentsPathListing )