FEU CSV Export - 4 problems and 3 fixes
Posted: Fri Sep 05, 2008 9:12 am
Hi guys,
I've been using FEU and SelfReg on a production site and have run into some problems with CSV export.
Specifically:
1) I was getting a *lot* of gaps in the output meaning that when Excel opened the CSV the data was not arranged in nice columns
2) It seemed rather slow
Looking at the code in action.do_admintasks.php...
1) Turned out to be three problems:
1a) Because the table output from the query uses DISTINCT, some rows are removed which shouldn't be. For example, a user joins and fills in two blank fields. The DISTINCT query removes all but one of these rows from the output because the only column in the query which are unique for a particular user is the user data itself.
Solution is either to remove the DISTINCT (I didn't do this because I wasn't sure whether it's protection for some other reason) or add the field name to make these records distinct:
1b) Commas in fields become field delimiters in the CSV and split field values across two cells.
My fix is to add quotes around each field output as so:
1c) Now, this I don't have a solution for. It seems that for checkbox fields an "off" value is not written as a value in the cms_module_feusers_properties. Only "on" values are written. (At least for the optional checkboxes I'm using).
Since the value doesn't exist in the database, it causes similar gaps in the output and messes up the resulting spreadsheet.
IDEAS ANYBODY?
2) The query is executed two rows at a time.
I realise that the comment suggests that the rows per query should be a preference but I believe that the default should be higher. I raised it to 200 on my installation - CSV download now runs noticably and dramatically faster.
I've been using FEU and SelfReg on a production site and have run into some problems with CSV export.
Specifically:
1) I was getting a *lot* of gaps in the output meaning that when Excel opened the CSV the data was not arranged in nice columns
2) It seemed rather slow
Looking at the code in action.do_admintasks.php...
1) Turned out to be three problems:
1a) Because the table output from the query uses DISTINCT, some rows are removed which shouldn't be. For example, a user joins and fills in two blank fields. The DISTINCT query removes all but one of these rows from the output because the only column in the query which are unique for a particular user is the user data itself.
Code: Select all
$query = "SELECT DISTINCT groupname,userid,username,createdate,expires,data FROM ".cms_db_prefix().
"module_feusers_groups A,".cms_db_prefix()."module_feusers_grouppropmap B, ".cms_db_prefix()."module_feusers_properties C,
".cms_db_prefix()."module_feusers_users D where A.id=B.group_id and C.title = B.name and C.userid = D.id
order by groupname,userid,sort_key,name
limit $start,$num";
Code: Select all
$query = "SELECT DISTINCT groupname,userid,username,createdate,expires,data,name FROM ".cms_db_prefix()."module_feusers_groups A,
".cms_db_prefix()."module_feusers_grouppropmap B,
".cms_db_prefix()."module_feusers_properties C,
".cms_db_prefix()."module_feusers_users D where A.id=B.group_id and C.title = B.name and
C.userid = D.id order by groupname,userid,sort_key,name limit $start,$num";
Code: Select all
$output .= ",".$row['data'];
Code: Select all
$output .= ",".'"'.$row['data'].'"';
Since the value doesn't exist in the database, it causes similar gaps in the output and messes up the resulting spreadsheet.
IDEAS ANYBODY?
2) The query is executed two rows at a time.
Code: Select all
$start = 0;
$num = 2; // todo, should be a preference
$done = 0;
Code: Select all
$start = 0;
$num = 200; // todo, should be a preference
$done = 0;
