Plugin: CSVee
Development and Programming
Had this code lying around for a bit, and fancied turning it into a plugin for all to use.
Merely add to a blank template like so:
{exp:csvee query="SELECT member_id, username, screen_name, email FROM exp_members"}Navigate to your template and you should be presented with a downloadable CSV file.
Parameters:
query - The query from which the CSV output is determined.
fixed_width - If you want each column (field) to be a fixed width, supply an integer value in this parameter. By default it will pad those fields that are shorter with spaces. You can change this character with the pad_character parameter.
pad_character - Character used to pad fields that do not meet the fixed width column setting.
seperator - Custom seperator to use between fields. This defaults to a comma.
delimiter - Custom delimiter to encase each field. Defaults to a double quotation. e.g., “My field”
show_headings - Whether to show column headings. possible values are yes or no. Defaults to yes
filename - Desired filename of output file, defaults to “CSVee_Output.csv”
save_path - If you would rather save the output file to disk, use this parameter. You can use this in conjunction with the redirect parameter if you also want to download.
redirect - Where to send the user after visiting the template holding this plugin.
group_id - Pipe delimited ( 1|2|3 ) set of group ID’s if you want to restrict the download to certain member groups. (this does not affect the query, only those member groups able to download the output file)
user_id - Same as above but by user ID.
Important Note:
To use whitespace characters in either of the pad_character/seperator/delimiter parameters just use their literal names, e.g., pad_char=”space” or pad_char=”tab”
Additional:
delete, drop, truncate, update, insert, alter, create
The use of any of these MySql commands will cause the plugin to quit out before issuing the query. This was added as a basic security feature.
Current Version: 1.1
Hey.
I’ve just used this plugin for the first time and it worked really nicely.
Thanks a lot for putting it out there.
Edit to add…
I used the below query
{exp:csvee query="SELECT url_title FROM exp_weblog_titles WHERE weblog_id =18"}Probably not much use to anyone else.
I’ve got a question. I’m using your plug-in to let the client export data from the FreeForm module (the built-in txt export chokes on a field that allows multiple entries and he doesn’t seem to have any desire to fix it).
CSVee does better, but there’s a small glitch and I’m wondering if it’s possible to fix it, or if it’s just a symptom of opening this file in excel. If you have multiple entries (a field name like this[]) instead of putting line breaks between each value, when you open it in Excel it just has that weird square character it displays for characters it doesn’t understand. Is it possible to have the line breaks work when opening in Excel?
Thanks (and thanks for sharing the plug-in) 😊
Hi Brian,
Not quite sure I understand what is happening, can you send me an example .csv file where this is happening?
Hi Brian, Not quite sure I understand what is happening, can you send me an example .csv file where this is happening?
Here’s an example - check out the ‘category’ column:
EDIT - it’s not a huge deal, just figured if you knew of an easy fix I’d ask… 😊
I do hope those card details are not real :|
It looks like a character set issue that excel can’t deal with. I’ve opened the file in OpenOffice and the values show up fine, each on a new line.
Edit add: Are you using excel mac?
I am on a Mac yes - an old version of Excel (think it’s v.X).
It’s definitely an encoding issue you’re right. I’m not really sure how to get around it though. If I change the encoding option in the EE general system prefs it doesn’t make a difference (tried ISO-8859-1). Is there a way to force the CSV to use a charset when it is being created?
I’m thinking of using CSVee in another part of the system as well (a conference registration system that allows multiple registrations at once), but it would actually have to honor these line breaks or formatting would get too screwed up to use the CSV…
EDIT - don’t worry - everything in the CSV is fake data - random stuff…
I guess i could make it search for for a CRLF in each column, and replace with just an LF. Brian, i’ll PM you a newer version.
I guess i could make it search for for a CRLF in each column, and replace with just an LF. Brian, i’ll PM you a newer version.
Hey that would be fantastic - thanks a bunch!
Mmm, is there a version of csvee I can use with EE Core (since Core doesn’t have query module)?
Nevermind, I’ve succesfully export to CSV using PHP.
Csvee doesn’t rely on the query module so you should be able to use this in EE core.
Thanx for the reply. I understand now; i thought that query was using the query module.
Any idea if it would be possible to have a parameter to set the delimiter type? One of the fields that I would like to export has a bunch of quote marks in it, and it’s throwing off the importing process.
Thanks, Jesse
Sure, will add that in soon… would three extra parameters like:
fixed_width = “x”: where x is number of chars to rpad/cut to seperator = “tab” / “comma” / “semicolon” / “space” / “custom”: where custom is anything you like text_delimiter = “single” / “double”: this refers to quotes
suffice?
Edit: I can’t count :D