Automate Exporting of Freeform data

Question

I've asked this question over at Solspace but thought I'd see if any of the EE community have any insight.

I have urgent need of a solution that can automate the exporting of freeform data. The manual solution is not a viable option at all.

Can anyone advise me of how I could achieve a cron job or JSON feed… anything that can be picked up by a remote server, I'm open to all ideas.

Exporting each "form" group would be mandatory ie. not all form data in one stream/file/URL

Answer 1

This can actually be accomplished quite easily using a combination of Automat:ee and Export It. Automat:ee would handle the scheduling and Export it can be used to export out the Freeform data.

Answer 2

You can create a template to pull Freefom data anyway you need it to be pulled. If you need a json feed, just build that code into the template using Freeform tags. You'll get exactly what you need and how you need it. You can create an XML feed instead of json if that's easier for you.

If you need to automate saving the output to a flat file, just add the appropriate PHP and add a cron job on your server to run the template at the specific intervals you need.

Answer 3

Firstly thankyou all for your replies and knowledge, I'm confident all answers are "the answer" but EE being EE there is rarely a definitive answer, here's the solution I've arrived at.

Nicolas from Solspace suggested using EE as the solution.

It made sense, I recall using EE to output similar types of data for different needs but I had never thought I could use EE for JSON output (OK, so I'm a newbie with JSON).

A google search returned one poignant post by James over at spibey that pretty much dealt with was what I was after.

A liberal dash of Low Replace to remove any nasty characters that may be in the Freeform fields and stuff up the JSON schema.

Combined with Rob Sanchezs' Http Header Plugin to convert the template to proper JSON and I'm well on my way.

The only thing left to do was create a ridiculous username/password and restrict the template to that usergroup only and I now have a series of URL's that are secure and output JSON feeds of all Freeform collected info.

Note, extended the functionality to include a parameter where the client can query a date range rather then "the lot".

Thanks to all who helped.

Answer 4

From what I can tell, the answer is not one that currently exists as part of the Module. For the process to be automated, you would need to create a custom query, with a cron job or an add-on, but the cron job with a custom query would be the easiest approach.

Andrew Weaver has a great little utility add-on, AJW Export, that basically would handle the .csv process for you, so all you'd need to do is create the query, place it on a template, and either set-up a cron job with your ISP or use the ExpressionEngine Cron add-on, which I can't recall, if it's packaged with EE or if it's one of the many items you can get from EllisLab, but you need to grab from Devot-ee or the Ellis Lab GitHub account.

One thing to recall with the custom query process, is that FreeForm Pro 4.x now creates new data tables for each new form, so make sure you grab the right forms data. The query would look like:

{exp:ajw_export 
sql="SELECT entry_id, entry_date, form_field_1, form_field_2 FROM exp_freeform_form_entries_1" 
format="csv" 
delimter=":"
filename="output.csv"
}

The one thing I'm uncertain about is the

anything that can be picked up by a remote server

statement. Do you need another database to grab the reports, or do they need to be stored on a different server? A cron job will automate the running of the report, AJW Export will produce the .csv format for you, but both options either assume it will be downloaded in a web browser or be saved to the server. In fact, I think AJW Export ONLY posts to immediate download. I could be wrong, but I believe that's true.

If you have a second database that needs access to info from the website, then an ODBC query might be more appropriate. I know we've set these up for clients using FileMaker on the desktop and MySQL on the server. The automation is more dependent on what desktop database you are using. But basically, we configure the desktop database to open a connection to MySQL using ssh via the sFTP access, and an IP filter, then grab the data. FileMaker handles pre-existing data by checking if the designated entry_id exists or not.

I've never used it, but if JSON is your other option, then you can also look at the add-on, JSON, by Rob Sanchez/Barrett Newton. You might be able to grab this from a specific url, with some login credentials or an IP filter.