Exporting data to csv or xls file

I'd like to use either the CSV Export or the XLS export actions but I need to export the results of a complex MySQL multiple-table query.

I have tried playing around with the code provided in another post ( http://www.chronoengine.com/forums.html?cont=posts&f=5&t=12071 ) but it doesn't work (it might be outdated since the first post is from 2008). I even tried the original code by just changing the table name but it does nothing.

I guess I could use the XLS export action but I am not sure how to save the results of the query in the $form->data array in the right way so it can be associated with a data path. I have tried setting the data path to lid and saving the results from my query placed in a custom code box with the following line:

$form->data['liq'] = $db->loadAssocList();

but I only get one single item from the results in the xls file. What is the right way to do it?

David

Hi David,

It sounds as though you are on the right track.

Add a Debugger action to see exactly what is being output and how the array is named. (Note you may get a lot of output!)

Bob

PS I've had some success using a MySQL view to define a complex query and then exporting from the View.

Thanks for your reply, Bob. I thought adding the Debugger action would be the easy part. I guess not. For some reason, I get the file downloaded but no page actually opens, so I can't see the debugging data. I have my actions on the Load event in the following order: Debugger, show html, custom code (the query) and XLS export. I have tried other combinations, too, but nothing seems to work to show me the data.

What am I doing wrong?

Hi dmontpe,

You probably have to turn off the immediate download to see the debugger output as the immediate download effectively takes over from the form processing.

Bob

But that option belongs to your csv export action. I am using the xls export. That one has no inmediate download choice. It does that by default.

I had figured I could not use your csv action since it only allows to query one single table. When you said you had succes with a mysql view, was that along with your csv action? I could try that.

Hi dmontpe,

Yes, sorry. The debugger won't show you anything with the XLS export from the toolbar.

If I remember correctly I used the View with a CC listing but it should also work with the CSV Export as it appears like a table in the Tables list once it has been created.

Bob

Bummer! MySQL views don't allow subqueries in the FROM clause. I guess I will end up recording the results as a new table so I can use your CSV export action. Good thing is that will do away with the figuring out part. Bad thing is it will end up increasing workload but it a once-a-week operation so it shouldn't get too bad.

I won't close this thread until I've got it up and running, though, just in case I need more help.

What a waste of time! I hadn't realized that your csv export action had an option for data that is already in the form. I just tried it with the same code I posted above and it works perfectly! Problem solved.

P.S. That might mean there is a bug in the xls export action.

Hi dmontpe,

Well done for finding that - I had forgotten that I'd included it :-(

Bob

Hi, Bob.

The csv file is being created and downloaded alright but I've got one last issue: I have the 'inmediate download' box unchecked but I can't get the the thanks message to show. I have it placed inside the OnSuccess event of the CSV export action. That is the right place, isn't it? What could be wrong?

David

Hi David,

I avoid using the On Success events unless absolutely necessary so I'd put it after the CSV Export action. But either should work.

If you add a Debugger action do you see the output from that?

Bob