EasyTable Pro! from 1.4.0 introduces a feature called "Automatic Imports".
This feature allows you to add new records or replace existing ones with the contents of a CSV/TSV file using a scheduled CRON job (or via your servers command line interface).
You may have used CRON jobs before to do thing like automate the scheduling of backups, or to run one of Joomla's built-in CLI files, like Smart Search's "Finder Indexer" or GarbageCron or Update_cron.
If you have the icon to the left will be familiar to you, especially if you use cPanel on your server to manage aspects of your website. Setting up a CRON job for Automatic Imports to EasyTable Pro! tables is just as easy.
The major aspects of setting up Automatic Uploads are:
- Putting the new CSV/TSV file in a CRON accessible directory for "EasyTable Pro's Import CRON" (aka /cli/easytablespro_import_cron.php )
- Specifying the command for the CRON job
- There is no step 3.
Uploading new records to your server
The "Automatic Imports" feature was sponsored by a client that wanted to export records from an automated monitoring system. That system, was already web enabled (in that it could upload files via FTP) and so all the client needed to do was specify the access details in the monitoring systems web interface. How you upload the data will depend on the systems you have and the source of the data you want to upload.
The most important thing to remember is that your CSV/TSV file, as with normal updates done via the EasyTable Pro! web interface, has the same number of columns/fields and that they are in the same order as the table (i.e. when you first imported a file and created the table).
If for example you have a server on your local network that can run PHP, you could use a simple script like this one:
Depending on your local source/server environment you could setup a repeating job to upload the file to your website using that PHP script.
If you're running Windows 7 you could "Schedule a task" or on a Mac you could setup a Folder Action so that when a new CSV/TSV file appears in a given folder it gets uploaded to your server. On Linux/Unix based systems you also have access to shell scripting and modern replacements for CRON like launchd on Mac, or inetd, upstart etc…
Specifying the import command for CRON
Like all of Joomla's built-in CLI scripts you need to firstly specify the path to PHP in your CRON job. In well setup servers you can simply start with php
In others you may need to specify a specific path like /usr/bin/php
Next, you need to specify the path to the script, and just like the built-in Joomla CLI files you'll find the EasyTable Pro! Import CRON script in the website's /cli directory at /path/to/website/cli/easytablespro_import_cron.php
Obviously you'll need to update the "/path/to/website" section to reflect your servers configuration.
Next up are the various options you can pass into the script, the mandatory ones are the ID of the table the file is for and the path to new data file.
The table ID is simply the number you will see next to each table entry in the EasyTable Pro! Manager view (hint it's the last column on the right). You specify the table ID as follows:
Where 9999 is replaced by the ID of the table you're updating.
Next is the path to the new CSV/TSV file on the webserver, this will of course vary depending on your particular setup, if we use the example path in the PHP script above this will be:
With just those two options set you can run the CLI import script and your table will have it's replaced by the contents of the CSV file and the CSV file will be deleted.
Of course the default behaviour expects your CSV/TSV file will have a first row of headings and that you want to replace the contents of the table, if you want a different behaviour you'll need to set some of the other options.
The optional options, yes English is a wonderful language, are:
|append||If 'append=1' the contents of the file will be appended to the table, otherwise, it defaults to 0 and replaces the contents of the table with the contents of file.||--append=1|
|fileHasHeaders||If 'fileHasHeaders=0' the first line of the file is treated as a record, otherwise, it defaults to 1 and discards the first line (as headings aren't needed).||--fileHasHeaders=0|
|deleteFile||If 'deleteFile=0' the update file will NOT be deleted after processing, otherwise, it defaults to 1 and after a 'Successful' run (i.e. 1 or more records added to table) the datafile will be deleted (unlinked).||--deleteFile=0|
Once you created your CRON command it can be quite long, so it may be best to construct it in a note pad type application first (that way you can keep a record of it). After all a fully setup CRON command could end up looking like this one:
php /path/to/j3/cli/easytablespro_import_cron.php --tableId=243 --filePath=/path/to/uploads/Firefly.tsv --append=1 --deleteFile=0 --fileHasHeaders=0
The output from the CLI script will have some useful details to help you track down any errors/problems with your CRON/CLI command. As you can see in this example, it clearly tells you that both tableId and filePath must be specified:
php /path/to/site/cli/easytablespro_import_cron.php --filePath=./MyData.csv --deleteFile=0 ** EasyTable Pro! CRON Import Starting ** ** EasyTable Pro! models & tables loaded. ** ** Usage: tableId and filePath must be specified. ** ** CRON Update DID NOT add any records in table ID: 0 ** ** CRON found no VALID files to process. ** ** EasyTable Pro! CRON Import Finished **
So, read the output to find out what's going wrong.