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).

 

CRON Jobs - cPanel

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:

 

  1. Putting the new CSV/TSV file in a CRON accessible directory for "EasyTable Pro's Import CRON" (aka /cli/easytablespro_import_cron.php )
  2. Specifying the command for the CRON job
  3. 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:

<? php
$file = 'dataExport.csv';
$remote_file = '/path/to/storage/location/for/dataExport.csv';
$ftp_server = 'ftp.mydomain.com';
$ftp_user_name = 'This email address is being protected from spambots. You need JavaScript enabled to view it.';
$ftp_user_pass = 'secretPassword';
// set up basic connection
$conn_id = ftp_connect($ftp_server);
// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);
// upload a file
if (ftp_put($conn_id, $remote_file, $file, FTP_ASCII)) {
echo "successfully uploaded $file\n";
} else {
echo "There was a problem while uploading $file\n";
}// close the connection
ftp_close($conn_id);
?>

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:

--tableId=9999

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:

--filePath=/path/to/storage/location/for/dataExport.csv

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:

OptionDescriptionExample
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

Trouble Shooting

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. 

You need to login to access the forums.

Member Login

You need to Login to access your Subscription details or the Forums.