This works on RSForm Pro.

I needed a really cool .csv export, and I found the Joomla back-end one was not as slick as I wanted.

I have copies of the same form in different languages, so to get the data in spreadsheet format I had to go into Joomla Admin, find each form and export it manually.. too much hassle. I want an admin clerk to collect this data, not a Joomla expert!

So here is how to get just the data you need into an excel spreadsheet with FIVE clicks!

1. Format your administrator e-mail as plain text, and have just one line of text in the e-mail. You're just going to put the field values you want in the form, separated by \"quotes \" and a character that Excel recognises as a delimiter. I chose ~ but a comma would work. The first two fields are extra, you will find out why at step 3.

Your admin e-mail should look something like this:
2. This creates an e-mail whose body is a .csv file, but it would be a real pain of course to cut and paste the contents of e-mails... Here's the cool bit: There's an e-mail editor called Eudora which has a free to use version here: You can download it and install it where you like, and when you do, make sure you know where the data is going to be stored. If you do not want to use Eudora for your regular e-mail, have a unique \"to\" address for the admin e-mails so you can log into the mailserver from eudora without downloading your regular mails.

If you already use Eudora, create a new mailbox and filter just for these admin e-mails.

Eudora mailboxes are huge text files with the format of header - body - next header - next body etc.

3. Fill in your form and post a few entries, then start eudora and collect your mail - JUST the form contents of course. Have a look to make sure the mail has arrived, then close Eudora to avoid file sharing errors.

Here's the trick: Open Excel and open, as a text file, the eudora mailbox containing the messages. Probably called In.mbx

Choose '~' (in this example) as a delimiting character during the import, and you will find a very tall spreadsheet with a few useful lines of information, separated into columns. All the e-mail headers are in the first column (or first two columns if you left Excel's default tab as a delimiter).

4. To get rid of the unwanted data, you need to delete the first two columns (remember \"column A for deletion \"~\"column B for deletion \") and get rid of the resulting empty rows. The best way is to create a macro (tools - Macros - Record Macro...) and then delete columns A and B. A second macro to delete the empty rows is a bit harder but fortunately there's a perfect piece of code available from http://www.cpearson.com/excel/deleting.htm . I have used this and it works perfectly. Add this to your Excel macro (how to do that is beyond the scope of this post).

You now have a spreadsheet with ordered information, and without things like labels etc. Remember, you control the ordering and content of columns with the ordering and content of the admin mails. Using the example above, after columns A and B are deleted, the first column is firstname, second is lastname etc.

Do you want a header row? Record another macro and put in the headers on each row, and save the macro.

Finally, make one big macro that run all the little ones, so that in one click you import the .mbx file, delete the first column(s), delete the empty rows and add the headers.

IMPORTANT! Save the excel file as an XLS file somewhere else! (At least, do not overwrite in.mbx )

You do not need to go into the Joomla backend ever again to get form data for your spreadsheet.

Just five clicks? Open eudora, collect mail, close eudora, open excel, run macro.

If you have similar forms flavoured for different users, but where the underlying data is for the same purpose, you can set them up so they all go to the same mailbox and into the same spreadsheet... Possibilities are endless!

Have fun

Adrian<br><br>Post edited by: adrian_m, at: 2008/07/05 18:25