3 Replies Latest reply on Oct 27, 2011 8:57 AM by SwagathNavinManohar

    updating database automatically

    SwagathNavinManohar

      Title

      updating database automatically

      Post

      Hello all,

      I recently started learning filemaker pro 10.0v3 (2 hours!!) on Mac OS X 10.5.8.

      Here s my scenario :)

      I get mails every 4 hours from a remote measurement site with measurement values. The files are in *.csv format and the filenames are XX-2011-00001.csv and YY-2011-00001.csv. These are data of two instruments continuously running with different sampling intervals. The files are stored in local folders.

      I want to develop a script that would read a file (example: XX-2011-00001.csv) and uploads the data in the database and after updating the database the .csv file should be moved to another folder for backup purposes.  After 4 hours the script should run again and now read the new file XX-2011-00002.csv and append this data in the database. I want to make this script run in an infinite loop, such that the script checks for new file and adds it to the database.

      The file contains ‘Date’ ‘Time’ ‘value’ fields.

      Can you please help me in telling the commands that i should look into for writing this script and if you have any examples I would be really thankful.
      Thanks a lot for your help.
      Navin

        • 1. Re: updating database automatically
          philmodjunk

          How much experience with databases, and/or programming do you have? This can be done, but requires some scripting to set up.

          Look up the following script steps in FileMaker Help or any training materials you have:

          Install OnTimer (can be used to perform a script on regular intervals)
          Import Records (can be used to import your data from the csv file into your database)
          Set Variable (can be used to compute the file path to your file in a way that increments with each import.

          The basic out line is to write a script that,

          1. computes the file path to the next two files csv files, storing the paths in a pair of variables
          2. Uses Import records with the Filepath variables used to specify the files to be imported to import the data
          3. Updates a "log" file with the most recent "count" so that your script can start with the next number in the series even if you need to quit and restart your database.

           

          For example purposes, I'm going to assume that the csv files appear in a folder named "Measurements" located on your desktop. You'll need to modify that calculation to compute the actual file path for these files.

          Go To layout [Sequence]
          Set Variable [$XXPath ; Value: "file:" & Get (DesktopPath) & "Measurements/XX-" & Year ( Get (Currentdate ) ) & "-" & Sequence::XXSeq & ".csv"]
          Set Variable [$YYPath ; Value: "file:" & Get (DesktopPath) & "Measurements/YY-" & Year ( Get (Currentdate ) ) & "-" & Sequence::YYSeq & ".csv"]
          Import Records [no dialog ; $XXPath ; Add ; Windows ANSI ]
          Import Records [no dialog ; $YYPath ; Add ; Windows ANSI ]
          Set Field [Sequence::XXSeq ; SerialIncrement ( Sequence::XXSeq ; 1 )]
          Set Field [Sequence::YYSeq ; SerialIncrement ( Sequence::YYSeq ; 1 )]

           Notes:

          When Setting up the Import Records steps, specify an existing copy of the csv file so that you can map columns of data in the csv file to specific fields in your table. Leave this file reference in place, but put the $XXPath or $YYPath variable in its own line above the other in the file reference dialog. This way your import mapping options will be retained, but the file path in the variable will be retained.

          This is the script that you use a controlling script with Install OnTimer to perform on your 4 hour interval. This script can also be controlled from a robot file that is opened every four hours from an operating system task scheduler, but it cannot be run from a FileMaker Server Schedule.

          • 2. Re: updating database automatically
            philmodjunk

            I forgot to mention that there's a way to move your files from one directory to another by using a container field to copy the file from one location to another and Export Field Contents can be used to delete the file from it's original location.

            Define a field of type container and put it on your layout. We'll call it: ContCSVFile.

            InsertFile [Reference; YourTable::ContCSVFile ; $XXPath]
            Set Variable [$NewPath ; //put expression here to calculate path to new location]
            Export field Contents [ YourTable::ContCSVFile ; $NewPath]
            Export Field Contents [ $XXPath ] //this steps deletes the file from its original directory.

            • 3. Re: updating database automatically
              SwagathNavinManohar

              Hi,

              Thanks a lot for your prompt reply. I have little programming experience with python. I would look into the things you mentioned. Looks like a exciting learning exercise.

              Thanks,

              Navin