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,
- computes the file path to the next two files csv files, storing the paths in a pair of variables
- Uses Import records with the Filepath variables used to specify the files to be imported to import the data
- 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 )]
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.
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.
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.