Plugin: CSVee

this can export the passwords too?

Remember that member passwords are encrypted in the EE database, and can’t be decrypted.

Hi Daniel,

Thank you for creating such a great plugin!

I wanted to post a ‘big thank-you’ to you and the others who had posted in this thread as it really made a difference.

I’ve used EE for a while now but was stumped when my client asked for a downloadable CSV file of the members that had registered.

PHP & MySQL are are ‘dark corners’ of my understanding. I know that if it’s in the database then it can be got out - it’s just having the know how.

I found this plugin today and carefully worked through the posts and after a few hours I succeeded.

I’ve added a Quick Link to the template and it downloads as a named CSV. I’ve renamed the column names I’ve even reordered the list.

This is powerful ‘juju’ and I’m sure the client will be suitably impressed.

I add my solution in the hope that it may help others.

{exp:csvee filename="registration.csv" csvee query="SELECT 
m.member_id AS 'Member Id', join_date AS 'Raw UNIX Registered Date', date_format(from_unixtime(join_date), '%e/%c/%Y') AS 'Date Registered', 
m_field_id_6 AS 'First Name', 
m_field_id_5 AS 'Last Name', 
m_field_id_14 AS 'Country', 
m.email AS 'Email', 
m_field_id_15 AS 'Telephone', 
m_field_id_7 AS 'Company', 
m_field_id_8 AS 'Address 1', 
m_field_id_9 AS 'Address 2', 
m_field_id_10 AS 'Address 3', 
m_field_id_11 AS ' Town/City', 
m_field_id_12 AS 'County/State', 
m_field_id_13 AS 'Postcode/Zipcode', 
m_field_id_16 AS 'Fax', 
m_field_id_24 AS 'Questions' 
FROM exp_members AS m LEFT JOIN exp_member_data AS d ON d.member_id = m.member_id ORDER BY m.member_id DESC"}

Note for UK friends: ‘%e/%c/%Y’ gives you ‘dd/mm/yyyy’ format

Thanks for the plugin, saved me tons of work and made my client very happy.

I am have a small issue with it in Safari though. When it generates the file, instead of asking if I want to download it, it simply shows it in the browser. Is there any way to fix that?

There appeared to be a minor bug in the code. Please re-download the archive. Syntax is fairly straightforward - pass the full server path to the directory: save_path=”/home/me/my_website/example/”; (Note: trailing slash!)

Hi Daniel,

Sorry for the delay in coming back to you on this one!

I’ve downloaded both versions of the plugin from the first page of this thread (1.1 and 1.11) but neither will save a file to my ‘uploads’ folder (777 permission).

Here’s the exact code I’m using:

{exp:csvee 
group_id="1" 
filename="export.csv" 
save_path=”/home/my_account_name/public_html/uploads/"
query="SELECT 
exp_weblog_titles.title as my_title,
exp_weblog_data.field_id_24 as my_field1,
exp_weblog_data.field_id_80 as my_field2
FROM exp_weblog_data 
INNER JOIN exp_weblog_titles ON exp_weblog_data.entry_id = exp_weblog_titles.entry_id 
WHERE exp_weblog_data.weblog_id='4' 
ORDER BY exp_weblog_titles.title ASC"}

Do you have any more ideas at all? Or is there anything I should be looking to check in my server config that would prevent this at all?

Thanks.

Hi there,

Just a quick follow-up - as you might have spotted with my code snippet above, I was inadvertently using a dodgy speech mark after the save_path= bit of the code. Now that I’ve fixed this, I’m getting the following error message:

Warning: fopen(/home/my_account/public_html/uploads/export.csv) [function.fopen]: failed to open stream: Permission denied in /home/my_account/public_html/my_system/plugins/pi.csvee.php on line 152 Unable to open or create /home/my_account/public_html/uploads/export.csv

The uploads folder has 777 permissions. I even tried creating a blank file named export.csv and giving that 777 permissions to, but to no avail.

The save_path I’m using is the same value that you can see in the PHP info under: _SERVER[“DOCUMENT_ROOT”], with the addition of /uploads/

Any ideas at all?

Thanks.

Hello, I have just begun to use this plugin and am experiencing one problem that I couldn’t find anywhere on search. I would like my csv to have a comma as the seperator and no delimiter. Here is my code:

{exp:csvee query="SELECT d.email AS 'email'
FROM exp1_members AS d
JOIN exp1_member_data AS t
    ON d.member_id = t.member_id
WHERE t.m_field_id_24 = 'active' AND d.group_id='24'" delimiter="space" seperator="comma"}

Everything else works well except that and it would be very helpful for my client if I could do this. Thanks.

@sm9

Looks odd. The code to create the file is fairly standard php, simply one function (fopen) using the “w+” mode (creates the file if it doesn’t exist). I would check first that your paths are correct, then perhaps try chasing it up with your host.

@ccarter

Did you try passing an empty string as delimiter? e.g., delimiter=”“. As far as I can see this should work just fine.

hi i use this plugin in two different websites (different providers). i did not change anything in the templates.

now i realised, that in ff 3.6 and ie7 for example the browser dont ask for saving a file. the browser just shows the whole export-informations in the browserwindow.

until now the plugin worked perfect.

here an example of a query

{exp:csvee query="SELECT date_format(from_unixtime(d.field_id_21), '%d.%m.%Y') AS Erstellt,
    d.field_id_22 AS von,
    t.title AS Medikamente,
    d.field_id_19 AS Einheit,
    d.field_id_5 AS Mo,
    d.field_id_6 AS Mi,
    d.field_id_7 AS Ab,
    d.field_id_8 AS Na,
    d.field_id_18 AS Verordnet,
    IF(d.field_id_23 > 0, date_format(from_unixtime(d.field_id_23), '%d.%m.%Y'), '') AS Editiert,
    d.field_id_24 AS von2,
    d.field_id_9 AS Stopp,
    d.field_id_20 AS von3 ,
    d.field_id_17 AS ID    
    FROM exp_weblog_data d INNER JOIN exp_weblog_titles t
    ON d.entry_id = t.entry_id
    WHERE d.weblog_id ='3' AND d.field_id_29 ='{segment_3}' AND t.status = 'open'
    ORDER BY ID DESC "
    seperator=";" filename="Medikamenten-Journal(ID-{segment_3}).txt" }

has anybody the same issue or can help with this problem? thanks

I just spent an hour determining that I cannot use an “INNER JOIN”* in the query parameter. Any ideas why that may be the case?

* This is my best guess. The query works fine in different SQL apps but fails in the CSVee parameter.

OK, it wasn’t INNER JOIN, it was a field titled “created” something or other that was getting flagged by $danger_will_robinson. That said, I am really struggling with this plugin today.

I absolutely cannot get this to force any browser to download, so I’ve settled for just outputting to a file - which is not working as expected. Maybe someone can help steer me in the right direction. Here is a simplified version of the template holding CSVee. I’m just trying to output data from the Linklocker table.

I’m passing some date parameters to the CSVee tempalte via an embed and I’m getting there from a simple form that the user submits. The user selects a start and end date for the data they want returned in the form, which posts to /reports/download:

My Simple Form (reports/index, hardcoded for now and posts to reports/download):

<form id="select_dates" method="post" action="/reports/downloads/">
Start: <input type="text" name="start_date" value="2010-03-01 16:21:41" /><br >
End: <input type="text" name="finish_date" value="{current_time format='%Y-%m-%d %H:%i:%s'}" /><br >
<input type="text" name="timemarker" value="{current_time format='%Y%m%d%H%i%s'}" /><br >
<input type="submit" value="Submit" />

“Outer” template (reports/download):

{embed="reports/csv" 
   start="{exp:parameters:post name='start_date'}" 
   finish="{exp:parameters:post name='finish_date'}" 
   csvdate="{current_time format='%Y-%m-%d-%H.%i'}" 
   time="{exp:parameters:post name='timemarker'}"}

“Inner” template (reports/csv):

{exp:csvee filename="Report-{embed:time}.csv" group_id="1|5" seperator="," 
query="SELECT m.member_id, m.screen_name, l.link_group, l.link_label, l.link_url, 
  SUM(g.download_count) as download_count, 
  MAX(g.last_download) as last_download, g.expires
FROM exp_linklocker_link l 
    INNER JOIN exp_linklocker_gen g ON g.link_id = l.link_id 
    INNER JOIN exp_linklocker_user2gen u2g ON u2g.gen_id = g.gen_id 
    INNER JOIN exp_members m on m.member_id = u2g.member_id 
    WHERE g.download_count > 0
    AND g.last_download >= '{embed:start}'
    AND g.last_download <= '{embed:finish}'
    GROUP BY m.member_id, m.screen_name, l.link_group, l.link_label, l.link_url 
    ORDER BY g.last_download DESC"
    redirect="/reports/thanks/{embed:time}"
    save_path="/Users/me/Sites/server.com/client.server.com/assets/uploads/"
    }

This works, to a point. There are two things still going horribly, terribly wrong.

1. The plugin is putting the path in the filename. The generated filename should be something like “Report-20100322200447.csv” but the plugin is putting the whole path in the filename so I’m getting a file titled “/Users/me/Sites/server.com/client.server.com/assets/uploads/CHIP-20100322200447.csv” 2. The plugin is not even saving where I tell it to. It is saving the file to the root folder of the site, or “Users/me/Sites/server.com/client.server.com/” - like it’s is ignoring “/assets/uploads/” altogether.

Anyone have any ideas? This is pretty much the only plugin of its kind, so if this one isn’t working, I’m not sure what to do. Thanks.

Hi Ryan,

Without setting this all up exactly like you have it there’s quite a bit there to take in but I’d have to ask first have you tried hard-coding all values to correct values that work with the plugin and just see if you get it working with hard-coded values first?

Hopefully that will work and if it does then it would tend to mean there’s something else going on here and not a problem with the plugin.

At least that’s what I’d try first seeing if I can get it to work with hard-coded values and if it does work then go through things a bit at a time perhaps replacing one variable at a time and see if I can get them working one by one. If that works then you may find it’s just one part it’s hanging up on and hopefully that will give you some more information to enable you to fix it.

Best wishes,

Mark

P.S What is the parameters plugin? Is that the plugin which allows you to get GET / POST data from a form I guess? I’d definitely try taking that out of the equation first and perhaps just use straight embed variables which are hard-coded to known values. That will at least take the plugin out of the equation for now as you have quite a lot going on there.

Not really sure on this one myself.

However I would be interested to see what you get if you do a var_dump($the_filename); and var_dump($save_path); at about line 48 in the plugin.

It might be worth starting with a hard coded file name, and then switching over to the {embed:time}

For the record, I have already tried this with everything hardcoded, and a vastly simplified SQL statement. I only write in the forums these days after I’ve exhausted a number of options first!

Let’s get rid of all the embed stuff, and hit the CSV-generating template itself directly at /reports/csv:

{exp:csvee filename="Report-fubar.csv" group_id="1|5" seperator="," 
query="SELECT * FROM exp_linklocker_link"
    redirect="/reports/thanks/"
    save_path="/Users/me/Sites/server.com/client.server.com/assets/uploads/"
    }

Fail: That creates a file titled /Users/me/Sites/server.com/client.server.com/assets/uploads/Report-fubar.csv at the root of the site. (Note that the real path in this example is changed to protect my innocent harddrive)

Try Again Not saving to a file - but trying to generate a download (which is what I originally wanted)

{exp:csvee filename="Report-fubar.csv" group_id="1|5" seperator="," 
query="SELECT * FROM exp_linklocker_link" redirect="/reports/thanks/"}

Fail: Nothing happens at all.

Try, Try Again Same as previous, but trying .txt

{exp:csvee filename="Report-fubar.txt" group_id="1|5" seperator="," 
query="SELECT * FROM exp_linklocker_link" redirect="/reports/thanks/"}

Fail: Nothing happens at all. Again.

Try, Try, Try Again Keep the .txt, but get rid of the redirect, kid.

{exp:csvee filename="Report-fubar.txt" group_id="1|5" seperator="," 
query="SELECT * FROM exp_linklocker_link"}

Half Success: Ah! Results were output to the browser.

Try, Try, Try, Try Again Modify previous one to .csv.

{exp:csvee filename="Report-fubar.csv" group_id="1|5" seperator="," 
query="SELECT * FROM exp_linklocker_link"}

Half Success: Same thing; results were output to the browser.

Strip It Good Nothing but the query to a file.

{exp:csvee filename="Report-fubar.csv" query="SELECT * FROM exp_linklocker_link"}

Half Success: Same thing; results were output to the browser.

I feel like I’m missing something obvious. I’ll try dropping a couple $var_dumps to see what’s going on in there.

The $var_dump is showing me what I would expect. I placed this into the plugin at line 48:

echo '<pre>';
var_dump($the_filename);
var_dump($save_path);
echo '</pre>

<p>’;

string(16) "Report-fubar.csv"
string(104) "/Users/me/Sites/server.com/client.server.com/assets/uploads/"