Export to CSV file

aslsw66 · 2010-11-01 04:47:43

Is there a way to export a list to a CSV file?

I’m using ign_password_protect to create a list of users (or members); I have extended the database table to capture more information on each user, like email address, postal address etc.

I have a form to display summary data on members in a (web) table form to people with the proper privileges. I would like to show a link so that the person could export the list of members to a CSV file ie. click the link and prompted to open/save the file.

There are a couple of uses for this:

keep a periodic hardcopy record of members and their details at a specified date, and
allow the secretary to create bulk letters using mail merge functionality.

I know this comes down to changing the headers, but I have no idea how to achieve it in practice.

Thanks,

Bloke · 2010-11-01 09:57:33

aslsw66 wrote:

Is there a way to export a list to a CSV file?

Yes. There are a number of ways I know of, using some or all of the following tools:

rah_external_output
Roll your own section and use mg_setheader to control its content type
a smattering of PHP
probably others

I’ve done it before by making a section called export, setting the headers using:

<txp:php>
header('Content-type: text/csv; charset=utf-8');
header("Content-Transfer-Encoding: Binary");
header("Content-Disposition: attachment; filename="data.csv");
</txp:php>

Then using smd_query (or a few lines of PHP) to output the relevant data and iterate over it, outputting each item with commas between them and a relevant newline at the end of each row.

If you are filtering data into a visual table and want to output “what you see”, it’s a bit more tricky because you might not be able to re-run the query in your export section. In that case you can probably either pass the data or used query params to the export section when the user clicks the Export button and recreate the query or iterate (or output) the data directly. Alternatively you can maybe use javascript to grab the client side data currently on display and send it to your admin side for output. Not sure if you can set hedaers and things in JS: perhaps it’s possible with a bit of AJAX, I’ve never tried.

Couple of things to be aware of if your target application is Micro$oft Excel:

it sucks at multi-lingual CSV
it works differently in Mac to Windows and you often need to format the file accordingly

I found this all out the hard way trying to output some text with umlauts in it. Excel doesn’t (or at least didn’t) support UTF-8 so it just rendered dumbed down characters where the umlauts should have been. Turns out it uses UTF-16LE so I had to put the data into an array, make sure it was all UTF-8 and then run this line on each row:

mb_convert_encoding($row, 'UTF-16LE', 'UTF-8');

to convert it. You can’t convert the entire array in one go once it’s been created, otherwise the line endings get converted as well and it just opens on one line in Excel!

Further, once it’s saved to your desktop, nothing except Excel could open the file. Everything else displayed utter crap or just refused to open it. And it didn’t open at all on a Mac but, bizarrely, the UTF-8 version did. So I had to put in a mechanism to allow you to choose whether you wanted the ‘mac’ (UTF-8) or ‘windows’ (UTF-16LE) version. It was an utter minefield and left me feeling dirty and in need of a good wash afterwards.

Upshot: don’t use non-ASCII characters in your Excel CSV files unless you want a world of pain and frustration.

I have extended the database table to capture more information on each user, like email address, postal address etc.

btw, was smd_bio not of any use here? If not, is there anything I could do to help make it useful for you?

aslsw66 · 2010-11-01 13:49:06

Nearly there, but for some reason the file is recognised as filename.csv.htm – so when it opens in Excel it doesn’t separate the list into cells properly. Not sure what’s going on there, but I’m sure I’ll figure it out.

Haven’t really looked at smd_bio but I supposed I should, everything else you produce works so well. It would certainly make it easier to set this up in real life (I’ve got this all running of my own domain for now) rather than having to remember which fields to create.

[EDIT] I looked at rah_external_output but I have no idea how it works. mg_setheader looks like it’s easier to understand.

Last edited by aslsw66 (2010-11-01 13:50:13)

Bloke · 2010-11-01 13:56:10

aslsw66 wrote:

Nearly there, but for some reason the file is recognised as filename.csv.htm

Weird. If you send the Content-Disposition header it should use the filename you specify. Very strange.

Haven’t really looked at smd_bio but I supposed I should, everything else you produce works so well.

Hehe, smd_bio might well be the exception :-) In fact, I’m not sure if I’ve allowed multiple select lists so perhaps it won’t be much use to you given that you’re selecting more than one role per person. I probably should add mutli-selects to the plugin one day…

Last edited by Bloke (2010-11-01 13:56:25)

aslsw66 · 2010-11-02 09:34:50

OK, there were two issues to sort out…

Firstly, in:

header('Content-type: text/x-csv; charset=utf-8');

… for some reason I had to change it to x-csv. Originally, it worked in IE but not in FF but this change seems to make it work in both browsers.

Secondly, I had to add some quotes around the filename in:

header("Content-Disposition: attachment; filename=\"data.csv\"");

Also, I had to figure out some spacing issues in my smd_query. Too many new lines caused there to be blank rows in my CSV file, too few seemed to cause it try to put things in the same cell on the same line. But now it works.

Will be making this a bit more reusable now so that I can call different content through the URL.

PS. And yet to check out the power of smd_bio.

drudog · 2012-04-26 21:27:58

Hey there, just came across this forum post and it seems aslsw66 has created a system much the same as I need to implement on a site I am managing.

I need to let users self_register and also collect other user data from them such as address, phone, etc. Having all that tasty info able to be exported as a CSV is the goal. I’ve been looking into several solutions but this method seems pretty seamless.

Could I get a shove in the right direction? Any help would be appreciated!

simoin · 2012-05-04 10:07:24

I think a detailed guide on how to do this would be of great benefit, I could certainly do with it!

aslsw66 · 2012-05-06 13:35:08

Here is a summary of what I have achieved so far…

Firstly, this approach doesn’t use some of the newer plugins such as smd_bio, smd_user_manager and cbe_front_auth. I started trying to convert my approach to using them but I found a couple of pieces of functionality that either weren’t mature enough or, more likely, I didn’t really understand them.

Secondly, my ‘paradigm’ has been to build a completely customised front-end admin area called the ‘Organiser’. I know it is possible to do lots of things to customise the Textpattern admin area but I really wanted to make administering my site as easy as possible for the technically-challenged people who I know will be using it. The only person with access to the real Textpattern back-end is the only person I trust – me!

Another part of my design paradigm is that people can’t self-register. As the members of our community organisation all need to go through an offline induction process, I already know who they are.

Database changes

The hardest bit about what I have done is to change the ign_users table in the database to include some additional fields. For example, I have added in fields for surname, address, phone numbers, date of birth, date of joining, etc – anything where there is a one-to-one relationship with the user. This is because mem_profile makes it really easy to pull out the contents of any field in the ign_users table.

Managing access

I use ign_password_protect to manage site access. Non-members see nothing but the login page. Members see the normal front end. Using privileges, the admins see the whole ‘Organiser’ area (although I’m toying with the idea of locking down certain parts to specific roles).

Here’s the login screen:

After logging in, this is what people see:

Note in this case the red ‘Organiser’ link in the navigation bar

Managing users

I’ve found mem_self_register to be quite powerful. Firstly, I use it to allow the site admins to create new users. With a bit of jQuery, the username defaults to a combination of the firstname and surname.
Here’s the form to add a new member:

The second functionality it provides is that it allows for users to reset their own passwords, and to generate a new password if they’ve forgotten their original one.

Updating details

Using mem_profile and mem_simple_form, I can create a form that allows either the user or the administrator to update their personal details.

User lists
Using smd_query (the most powerful plugin I use), I can easily generate lists of members.

You can see inside the first shaded box a disk icon – this is the link to generate a CSV file of the members.

Using some jQuery, it’s possible to make a sortable list of users (eg. sort on surname).

Using a separate page, it’s pretty easy to create a list that is exported as a CSV file. I have even used adi_gps to be able to pass in URL parameters to change the list (eg. list of only active members). Where I was coming unstuck before was in using the correct headers to create a CSV file – I found the right way after a lot of trial and error.

Final thoughts
This is all a work in progress. I have got the basics of user access and management sorted. But all of this functionality is using the plugins pretty much ‘out of the box’. Looking around the forums, I’m pretty sure there are more elegant ways of achieving this that better integrates with the Textpattern back-end but I’m comfortable with what I’ve achieved so far.

Let me know if you want the exact headers I have used for creating the CSV files.

simoin · 2012-05-06 23:09:53

Thanks for the detailed writeup aslsw66, it’s a bit beyond what I need to do at the moment, but great to read your process.

Textpattern CMS

Textpattern CMS support forum

#1 2010-11-01 04:47:43

Export to CSV file

#2 2010-11-01 09:57:33

Re: Export to CSV file

#3 2010-11-01 13:49:06

Re: Export to CSV file

#4 2010-11-01 13:56:10

Re: Export to CSV file

#5 2010-11-02 09:34:50

Re: Export to CSV file

#6 2012-04-26 21:27:58

Re: Export to CSV file

#7 2012-05-04 10:07:24

Re: Export to CSV file

#8 2012-05-06 13:35:08

Re: Export to CSV file

#9 2012-05-06 23:09:53

Re: Export to CSV file

Board footer