Export db data to CSV from CF v4

Hi ChronoEngine team-

I just installed CF v4 rc1.8 on a new Joomla 1.5.22 site. Works great and I really like the direction you are going with the interface. I had a form built in minutes and didn't have to read anything. Piece of cake.

My question: my clients need to be able to export the database entries into a CSV file from the ChronoForms dashboard. They can do that through the older version of CF, but I didn't see how in v4. Am I missing something?

thanks again for a great product!

Hi vashanka,

It's not in CFv4 yet but I expect that it is something that Max will add before too long.

Bob

Thank you! Please add my vote to having it included soon; it's super important for my clients so they can easily export contacts into their other databases.

Hi vashanka,

I've attached a new ChronoForms action to export records from a table to a CSV file.

To install the action download and unzip the attachment and then copy the contents to the administrator/components/com_chronoforms/form_actions folder on your site.

To test:
[list=a][*]Create a new form with the form wizard, give the form a name and publish it then add just a Submit Button to the Preview box; [/*][*]On the Events tab add a Show HTML action to the On Load event[/*]
[*]Look in the DB Operations tab for the CSV Export [GH] and drag that to the On Submit Event; followed by a Show Thanks Message and Debugger actions from the Core Actions group[/*]
[*]Click the centre 'spanner' icon on the the CSV Export [GH] action and select a table from the first drop down (I used jos_categories). Save and close the action configuration.[/*]
[*]Click the centre 'spanner' icon on the the Show Thanks Message action and add the following code to the message box:

You may download your file from <a href='{csv_link}'> here </a>.

{csv_count} records were exported.
The file size is {csv_size} kb.

[/*]
[*]Save & Close the form and view it in the Front End.[/*]
[*]Click the Submit button and you should see a message like this:

You may download your file from here. 17 records were exported. The file size is 0.3 kb.

with more detailed information from the debugger.[/*][/list:o]

That is the simplest configuration and will export all of the data in the table.

You can configure the plug-in to be much more selective in terms of the data exported. See the action Help Tab for more information.

Main features:

[*]Select any table from the Joomla! database[/*]
[*]Specify the export folder and file name including form results and new variables (table name, random number, datetime string)[/*]
[*]Specify MySQL WHERE and ORDER BY clauses to filter and sort the data[/*]
[*]Specify lists of Included / Excluded columns for simple selection[/*]
[*]OR Specify a list of Column Titles and column names to build a custom set of columns; this can include MySQL Functions.[/*]
[*]You can use Form inputs to specify the table, folder, file name and the MySQL WHERE and ORDER BY clause to make the export interactive. This feature is only available to registered users and should be used with care as the results may be used in MySQL queries.[/*]

The action was written in Joomla! 1.6.3 and has been tested in Joomla! 1.5.23 It should be treated as beta code and used with care. Please report any bugs you find here for fixing.

Bob

The action is now available for download here

I fixed a couple of small bugs: it's now correctly packaged in a folder; there is only now an On Fail event (see comments later in this thread).

Thanks Bob!

I will give this a try in the next couple of days and let you know how it goes- appreciate your time and hard work!

Vashanka

Hi, I was looking for the same solution and I tried what you posted here and it works great, except when I specify certain columns I want exported. I receive this error on export: Warning: in_array() [function.in-array]: Wrong datatype for second argument.

My list is as such: input_text_1, input_text_2, input_text_7.

Am I not entering the information correctly or is this a table problem?

Hi xlittle,

Sounds like a bug. I'll take a look.

Bob

Hi xlittle,

Hmmm . . . I can't get that error to appear.

By all means email or PM me the site URL and a SuperAdmin login and I'll take a quick look and see if I can spot where the problem is.

Bob

Grayhead

While this export action is a great addition to ChronoForms, I've discovered what appears to be a minor bug:

There is no $this->events['success'

=1 ever defined in this action, thus it never returns you to the success event.

I was able to finally successfully display the Thank You action as you described in your instructions upon changing /administrator/components/com_chronoforms/form_actions/csv_export_gh/csv_export_gh.php around line 220 from:

    $form->debug['CSV Export']

= 'File size is: '.$form->data['csv_size'

.' kb';
  }

to:

    $form->debug['CSV Export']

= 'File size is: '.$form->data['csv_size'].' kb';
    if(!$this->events['fail']) $this->events['success']=1;
  }

I've observed this in other actions, too. It might be something worth adding to the core as a class for success/fail situations as a function rather than a variable. That, or configuring the system such that in the event 'fail' is ever true, even if 'success' is true (which would be the default), it ignores 'success' and considers it a failure. Either way, then, there is never a possibility of ending in purgatory where it's neither a success nor a failure like that which happened to me.

Hi bizzynate,

Thanks for that. I haven't really go my head around the Success values yet so tend only to use the Failure ones. I also find it a little odd that there isn't a default to 'Success'.

I'll keep this tab open and update my CSV action - just have to reinstall my test sites on a new server first :-(

Bob

Hi,

You can place any success actions AFTER the main action instead of inside its success event, and an "event loop" or "show stopper" in the on fail, this ensures that your actions will be executed when there is no fail, regardless of "success" reported or not, makes the events tab less complicated as well.

Regards,
Max

Hi bizzynate,

I got a better idea of how the On Success and On Fail events work and have modified the CSV Export [GH] action so that it only has an On Fail event. I couldn't immediately think of any circumstances when I'd want any special action On Success other than to continue the normal flow. (There's a new download link in my previous post.)

Action sub-events are not required and are not limited to On Success and On Fail. In practice they are a series of event triggers that can be specified in the action as values in an array. For example

$events = array('success' => true; 'fail' => false);

or equally

$events = array('apples' => 0; 'oranges' => 0, 'bananas' => 0);

Each of these events can be linked to an Event Box that displays on the action. The name, color (set in the CSS), and sequence are entirely arbitrary.

The event values are boolean and can be changed in the action code when it executes.

After the action has executed ChronoForms checks the events array and if there are any events set to 'true' it will run any actions that have been put into that events box. The sequence of events is decided by the order of the keys in the $events array.

Once any events have been executed control returns to the parent Event and the next action there will be executed.

Bob

Hi,

I followed your excellent instructions on how to do this, it worked fine for a test form I had set up already with an associated table. The message appeared click here for 30 records size etc. All well and good.

What I then did was set up a private page (for the site user) on that page I created a form as per your instructions, all looked ok, but when I navigate to the page with the form with the one SUBMIT button on it, I see "You may download your file from here {csv_count} records were exported. The file size is {csv_size} kb."


I do not see the record count figure or the file size in kb, just the code placeholders, and when I click the HERE link I get a 404 not found.

Am I missing something?
Thanks. McP

Hi McP,

Please take a Form Backup using the icon in the Forms Manager and post it here (as a zipped file) or PM or email it to me and I'll take a closer look.

Bob

"GreyHead":
To install the action download and unzip the attachment and then copy the contents to the administrator/components/com_chronoforms/form_actions folder on your site.

There is no /form_actions folder on my site, do I create it?

Also when downloading the zip file I get an error when unzipping it on a Mac. I still got a result though, is there just one file to load in called csv_export_gh.ctp ?

Please advise, thanks.

Hi almchad,

If you have ChronoForms v4 installed and working then there is an administrator/components/com_chronoforms/form_actions folder Note the 'administrator' bit of the path.

Mac's seem to have problemn with zip files :-(

There should be three files inside csv_export_gh.ctp, csv_export_gh.php and index.html. They all need to go into a form_actions/csv_export_gh folder (The zip contains the folder but unfortunately it doesn't appear when you unzip on a Mac.)

Bob

"xlittle":
Hi, I was looking for the same solution and I tried what you posted here and it works great, except when I specify certain columns I want exported. I receive this error on export: Warning: in_array() [function.in-array]: Wrong datatype for second argument.

To fix this, declare $exclude to be an empty array before inarray(). I added this around line 112:

if (!$form->data['exclude']){
    $exclude = array();
}

Bob probably wasn't seeing the error due to different error reporting setting in php.ini.

Chris

Hi Chris,

Thanks for that I've added a fix to the file in the download.

      if ( $exclude ) {
        $exclude = explode(',', $exclude);
        foreach ( $exclude as $k => $v ) {
          $exclude[$k] = trim($v);
        }
                        // add the next two lines
      } else {
        $exclude = array();
      }

Bob

Warning: fopen(/home/d***y/domains/d***y-outdoors.com/public_html/components/com_chronoforms/exports/export/csv_export_pop_chronoforms_data_registration_201108251600.csv) [function.fopen]: failed to open stream: Permission denied in /home/d***y/domains/d***y-outdoors.com/public_html/administrator/components/com_chronoforms/form_actions/csv_export_gh/csv_export_gh.php on line 197

Sorry the *** is the section above are just to stop it coming up in google