Plugin: CSVee

I needed to export a mailing list from one of my clients EE websites as a CSV file for them. The CSVee plugin was a great help. Simply installed the plugin - wrote a one line template with the CSVee tag and job done. Many Thanks for this plugin.

Is there any way to rename the column names into more human readable names? A column name like “d.m_field_id_1” is going to have no meaning to someone as opposed to “Address”.

Sure - “SELECT m_field_id_1 AS Address FROM …”

You can even backtick if you fancy: “SELECT m_field_id_1 AS The Address FROM …”

Thanks!

I found very tricky, weird bug.

We had a working query using AS for some of the statements:

exp:csvee query="SELECT m.username, 
m.email, 
md.m_field_id_10 AS `passport_name`, 
md.m_field_id_11 AS `nickname`, 
m.screen_name AS `screenname`, 
md.m_field_id_12 AS `mailing_street`, 
md.m_field_id_13 AS `mailing_city`, 
md.m_field_id_104 AS `mailing_state`, 
md.m_field_id_15 AS `mailing_zip`, 
md.m_field_id_14 AS `mailing_country`, 
md.m_field_id_20 AS `contact_phone1`,

But if we added the AS statement to all, the plugin would fail.

md.m_field_id_21 AS `alternate_phone`,
  md.m_field_id_18 AS `alternate_email`,...

Upon further investigation, I just discovered that it wasn’t the field values or the AS statement causing anything to fail. The plugin was failing when we tried to name anything ‘alternate’.

.. I did a search in the plugin file for the string ‘alternate’ and didn’t come up with anything, so troubleshooting this mysterious occurence is beyond me. Though it IS posted for anyone with a mysterious failure. If you’re using AS, check your names.

In case someone’s trolling here with the same question posted by Brian M. earlier in this thread regarding multi-select fields in Freeform and export weirdness because each value is on a separate line, I came across that very thing today. I checked in the Solspace forum, and someone did post an answer on how to fix it in Freeform. It’s very easy and it does work! In mod.freeform.php search for this: (about line 850 in version 2.6.5) // If the field is a multi-select field, then handle it as such. if ( is_array( $val ) ) { $val = implode( “\n”, $val ); $data[$key] = $REGX->xss_clean($val); } … The fix? Replace $val = implode( “\n”, $val ); With $val = implode( “,”, $val ); That gives you a comma-separated list of your multiple values, which makes Excel very happy.

I have this exact issue with the User plugin.. is there an existing fix?

I found very tricky, weird bug. We had a working query using AS for some of the statements:

exp:csvee query="SELECT m.username, 
m.email, 
md.m_field_id_10 AS `passport_name`, 
md.m_field_id_11 AS `nickname`, 
m.screen_name AS `screenname`, 
md.m_field_id_12 AS `mailing_street`, 
md.m_field_id_13 AS `mailing_city`, 
md.m_field_id_104 AS `mailing_state`, 
md.m_field_id_15 AS `mailing_zip`, 
md.m_field_id_14 AS `mailing_country`, 
md.m_field_id_20 AS `contact_phone1`,

But if we added the AS statement to all, the plugin would fail.

md.m_field_id_21 AS `alternate_phone`,
  md.m_field_id_18 AS `alternate_email`,...

Upon further investigation, I just discovered that it wasn’t the field values or the AS statement causing anything to fail. The plugin was failing when we tried to name anything ‘alternate’. .. I did a search in the plugin file for the string ‘alternate’ and didn’t come up with anything, so troubleshooting this mysterious occurence is beyond me. Though it IS posted for anyone with a mysterious failure. If you’re using AS, check your names.

This is due to the query containing the string ‘alter’. It is a crude security check, having wanted to keep the plugin as light as possible. If you are confident that user input won’t make it into the query in any way then by all means remove the word, or all the words, from the $danger_will_robinson array. If you are comfortable with php of course you could remove the entire block of code that assesses the query, but the former will suffice as an easy fix.

This plugin is awesome, thank you for your hard work on it. Would you happen to know an easy way to format the dates and times when exporting member info.

Thanks in advance for any help,

• Dustin

EDIT: I think I have found the solution to this in a post in this tread, I am testing now and will update with my results.

I am sure my solution is in post 46 that shos this

SELECT entry_date,
date_format(from_unixtime(entry_date), '%c/%e/%Y') AS formatted_date,
email
FROM exp_freeform_entries
ORDER BY entry_date DESC

I just need to figure out how to intergrat that into my current code to format all the dates in my menber info export.

{exp:csvee query="SELECT member_id, username, screen_name, email, url, location, occupation, interests, bday_d, bday_m, bday_y, aol_im, yahoo_im, msn_im, icq, bio, signature, private_messages, accept_messages, last_view_bulletins, last_bulletin_date, ip_address, join_date, last_visit, last_activity, total_entries, total_comments, total_forum_topics, total_forum_posts, last_entry_date, last_comment_date, last_forum_post_date, last_email_date, in_authorlist, accept_admin_email, accept_user_email, notify_by_default, notify_of_pm, display_avatars, display_signatures, smart_notifications, language, timezone, daylight_savings, localization_is_site_default, time_format, notepad, pmember_id FROM exp_members"}

Hi to all, Newbie question 😉

what kind of format is accepted by the save_path parameter?

I’ve tried server path, full http url, relative path but nothing seems to work.

Warning: failed to open stream, premission denied in … Unable to open o create file.

Anyone? Thanks in advance

PS: sorry for my english

Hi,

I am no expert at sql queries but I have almost got the CSVee plugin spitting out the data that I need.

I need some help with setting the query to pull the data from one table on the condition that it has a certain ‘status’ - which is located in a different table - I have no clue how to set this up.

This is what I have so far … .

{exp:csvee query="SELECT field_id_72, field_id_76, field_id_77, field_id_80, field_id_81, field_id_82, field_id_73, field_id_74 FROM exp_weblog_data WHERE weblog_id =9"}

but I also need to add the condition of ‘status_id=”13” in the table “exp_statuses”

many thanks,

Peter

I figured it out (after some SQL tutorial time):

SELECT d.field_id_72 AS ‘Company Name’, d.field_id_76 AS ‘Suite Number’, d.field_id_77 AS ‘Street Address’, d.field_id_80 AS City, d.field_id_81 as Province, d.field_id_82 AS ‘Postal Code’, d.field_id_73 AS ‘Key Contact - First Name’, d.field_id_74 AS ‘Key Contact - Last Name’ FROM exp_weblog_data AS d JOIN exp_weblog_titles AS t ON d.entry_id = t.entry_id WHERE t.status = ‘Package Needs to be sent’;

The initial trouble came in not knowing that EE splits weblog data between two or more tables.

pz

Hey this worked perfect, thanks for sharing!

Also, wanted to post this for anyone else that might be trying to export a weblog to csv, which is tricky because the titles and custom fields for entries are in two separate tables (and i suck at sql statements). In my case I need the dealer name (title), address (field_id_25), city (field_id_26), state (field_id_27) and so forth…

SELECT ewt.title, ewd.field_id_25, ewd.field_id_26, ewd.field_id_27, ewd.field_id_28, ewd.field_id_29, ewd.field_id_30 
FROM exp_weblog_data ewd 
INNER JOIN exp_weblog_titles ewt 
ON ewd.entry_id = ewt.entry_id
WHERE ewd.weblog_id = 5

To get the field_id_x number, simply go to admin › weblog/section adminstration › custom section fields › click field group associated with weblog your trying to export.

Then when you hover over the custom field, at the bottom of your browser you’ll see the url with field_id_x at the end (or in some cases, users that haven’t customized the field order will see the numbers right to the left of the field label)

Then of course, to get the id # of the weblog (WHERE ewd.weblog_id = 5), go to Admin › Weblog/Section Administration › Weblog/Section Management › then id will be first column on left

hi there, any help would be greatly appreciated!

with this query, i managed to bring down a DB server and multiple websites (including ours) on a shared pro host with enginehosting.com:

{exp:csvee query=”SELECT m_field_id_3 as fullname, email FROM exp_member_data m1, exp_members m2 WHERE m1.member_id = m2.member_id AND email LIKE ‘%domain.com’ ORDER BY fullname ASC”}

according to their tech support and CTO, nothing else on their end indicated an error and believe it was due to this query. i’m new to sql, but this seems very basic and is only pulling data from two tables and outputting two columns of data. total rows is ~5800, but i didn’t think that was excessive either…

which means i’ve (likely) introduced bad syntax in there somewhere, created a loop, etc. but i just don’t know. anything obvious from the above?

thanks, rho

Can’t see that causing a problem myself… perhaps the server was already under significant load?

thx, kingscotty - so i used the same query, but outside of CSVee with the query module in a template, and no problem - it output everything.

client will now have an extra step of copying the data and pasting it into Excel instead of having a preformatted CSV file i guess. either that or pay $500 per month for a virtual private server as was Engine Hosting’s suggestion(!).