6 Replies Latest reply on Sep 27, 2012 11:07 AM by Matty_1

    Extracting data from a text file

    Matty_1

      Title

      Extracting data from a text file

      Post

           Hello,

           We've reently started paying our vendors by electronic fund transfers via file upload.  Our EFT program outputs a simple text file contains all the apprpriate infomration for the banks to process the transfers.  Unfortunatly our accounting program does now have an email notification function so I'm looking to make one via filemaker pro.

           My question is: How can I tell FMP to find a particular text file, open it and start looking in the appropriate places for the required information.  I use a mac and I was playing around with the apple scripting which is completely new to me and was able to open the text program but I've had no luck with open a articular text file.

           Could someone shed some light?  I think if I can get FMP to open the file for me and have search within the document I should be ok after that.

           Thank you!

        • 1. Re: Extracting data from a text file
          philmodjunk

               Once you have found the text file, you can import it into a table and then you can parse the data from the text you have just imported.

               Finding that file could be an issue. FileMaker scripts can't--except for one exception list the names of the files in a given folder, but you can acquire plugins with this capability.

               On the other hand, it is possible for you to set up a system where you click a button and an open file dialog appears where you can find and select the file. The script then continues by inserting the file into a container field so that it can use the file path in the container field in conjunction with import records to import the text file into a table.

               So the details of how you set this up will depend on your workflow and how you want to design the user interface. Do you want to manually select the file for import or do you want the system to check a given folder for a new file and import any that it finds there?

          • 2. Re: Extracting data from a text file
            Matty_1

                 I'd be fine with simply haveing a promtp where I type in the file name which is something along the lines of EFT-0001.DAT and goes up in increaments of 1 from there onwards.  The file currently resides on a server but remiains in a static location.

            • 3. Re: Extracting data from a text file
              philmodjunk

                   That incrementing file name suggests that you may need only to specify the file name once and FileMaker can simply update it for each import.

                   For starters, look up import records in Filemaker Help--there are a lot of options to read up on for this tool, and then try manually importing the text file into a FileMaker table. I recommend using the "new table" option for the targe table the first time around as this will make accessing the data in that text file much easier.

                   How the data is delimitted in that text file will be important. There are csv and tab options you can experiment with to see which works best for your text file.

                   Once you know how to do this manually, we can take a look at scripting it so that it happens automatically when you click a button or on a schedule that you specify.

              • 4. Re: Extracting data from a text file
                Matty_1

                     Where can I find the csv and tabbing controls?  I'm able to import the document's information but naturally it's cutting it up incorrectly. 

                • 5. Re: Extracting data from a text file
                  philmodjunk

                       It's the file type you select in the first dialog where so select from "Files of Type:"

                  • 6. Re: Extracting data from a text file
                    Matty_1

                         Ah yes, my bad, I thought that was to simply display either type of file.  Thank you Phil!