A number of people using the amr-users plugin and who are Excel users, seem to need help when it comes to importing the csv file into excel. Open Office and Google Docs users seem to be fine.
As far as I can make out, this seems to be because:
- their hosts might have magic quotes enabled. Ask them to disable it!
- their default excel csv import settings do not match the csv file settings (comma separated, and all fields double quotes delimited – this is a standard csv usage.)
- they do not know how to get to the text import wizard (Office 2007 in particular is non obvious)
CSV Text Import Screen 2
Office 2007
In Office 2007, it seems that the trick is to rename the file to a .txt file, instead of a .csv. Excel 2007 should then automatically load the text import wizard. You may find other benefits to, in being able to define how excel should handle the data in each column.
Note: When Excel opens a .csv file, it uses the current default data format settings to interpret how to import each column of data. If you want more flexibility in converting columns to different data formats, you can use the Import Text Wizard. For example, the format of a data column in the .csv file may be MDY, but Excel’s default data format is YMD, or you want to convert a column of numbers that contains leading zeros to text so you can preserve the leading zeros. To run the Import Text Wizard, you can change the file name extension from .csv to .txt before you open it…
Using the Excel Text Import Wizard:
Get the text import wizard up
- either opening from the menus (Office 2003 – Click Data → Get External Data) or
- by renaming file to a .txt (Offfice 2007) and then double clicking on it
- Choose delimited, Click next
- Choose Delimiter: comma
- Choose text qualifier: “, Click next,
- Other options as you wish
- Click Finish
Other resources:
- Microsoft help is here.
- Help with screen shots of the text import procedure
- If you prefer being able to follow a video, this one may be helpful: http://www.auditexcel.co.za/Import-Data.html
Hi – I just updated your plugin and now when I rebuild the cache I get the following: I also find that do not get the last entry in my cvs file
It is not set:array(108) { ["ID"]=> string(1) “3″ ["user_login"]=> string(22) “mozakdesign_subscriber” ["user_nicename"]=> string(22) “mozakdesign_subscriber” ["user_email"]=> string(38) “mozakdesign_subscriber@mozakdesign.com” ["user_url"]=> string(0) “” ["user_registered"]=> string(19) “2010-02-18 19:58:45″ ["user_status"]=> string(1) “0″ ["display_name"]=> string(22) “mozakdesign_subscriber” ["nickname"]=> string(22) “mozakdesign_subscriber” ["wpm_leveltimestamp-1266372475"]=> int(1266523125) ["wpm_leveltimestamp-1266436247"]=> int(1266523125) ["wpm_leveltimestamp-1266372512"]=> int(1266523125) ["wpm_leveltimestamp-1266647522"]=> int(1267127925) ["wpm_leveltimestamp-1266647544"]=> int(1267732725) ["wpm_leveltimestamp-1266647554"]=> int(1268337480) ["wpm_leveltimestamp-1266647565"]=> int(1268942280) ["wpm_leveltimestamp-1266647575"]=> int(1269547080) ["wpm_leveltimestamp-1266647584"]=> int(1270151880) ["wpm_leveltimestamp-1266647595"]=> int(1270756680) ["wpm_leveltimestamp-1267042167"]=> int(1271361480) ["wpm_leveltimestamp-1267042183"]=> int(1271966280) ["wpm_leveltimestamp-1267042192"]=> int(1272571080) ["wpm_leveltimestamp-1267042202"]=> int(1273175880) ["wpm_login_date"]=> string(10) “1273790440″ ["wpm_login_ip"]=> string(13) “173.50.243.16″ ["street"]=> string(19) “12568 nw milazzo ln” ["years_experience"]=> string(1) “z” ["wlm_sctxns-0"]=> string(0) “” ["wlm_sctxns-1266372475"]=> string(0) “” ["wlm_sctxns-1266372512"]=> string(0) “” ["wlm_sctxns-1266436247"]=> string(0) “” ["wlm_sctxns-1266647522"]=> string(0) “” ["wlm_sctxns-1266647544"]=> string(0) “” ["wlm_sctxns-1266647554"]=> string(0) “” ["wlm_sctxns-1266647565"]=> string(0) “” ["wlm_sctxns-1266647575"]=> string(0) “” ["wlm_sctxns-1266647584"]=> string(0) “” ["wlm_sctxns-1266647595"]=> string(0) “” ["wlm_sctxns-1267042167"]=> string(0) “” ["wlm_sctxns-1267042183"]=> string(0) “” ["wlm_sctxns-1267042192"]=> string(0) “” ["wlm_sctxns-1267042202"]=> string(0) “” ["city"]=> string(8) “portland” ["state"]=> string(2) “or” ["zip"]=> string(5) “97229″ ["country"]=> string(3) “usa” ["phone"]=> string(10) “5035313480″ ["sex"]=> string(7) ” female” ["birthdate"]=> string(7) “6/29/76″ ["occupation"]=> string(8) “engineer” ["rank1"]=> string(1) “a” ["style1"]=> string(1) “b” ["rank2"]=> string(1) “c” ["style2"]=> string(1) “d” ["other_training"]=> string(1) “e” ["competition_experience"]=> string(13) “No Experience” ["competition_experience_other"]=> string(1) “g” ["value_competition_experience"]=> string(2) “10″ ["motivation_other"]=> string(1) “k” ["activities"]=> string(1) “q” ["complaints1"]=> string(1) “v” ["looking_for"]=> string(1) “s” ["topics"]=> string(1) “t” ["default_password_nag"]=> string(1) “1″ ["trained_instructor_associated"]=> string(3) “Yes” ["own_video"]=> string(4) ” yes” ["shaw_video"]=> string(6) “5 to 9″ ["shaw_article"]=> string(8) “10 to 14″ ["reason_tried_website"]=> string(4) “test” ["learned_about"]=> string(1) “k” ["comments"]=> string(4) “test” ["first_name"]=> string(7) “Jocelyn” ["last_name"]=> string(5) “Mozak” ["military_police_security"]=> string(15) “Law Enforcement” ["motivation"]=> string(14) “Fun Recreation” ["online1"]=> string(1) “l” ["online2"]=> string(1) “m” ["online3"]=> string(1) “n” ["online4"]=> string(1) “o” ["online5"]=> string(1) “p” ["activities1"]=> string(1) “q” ["activities2"]=> string(1) “r” ["activities3"]=> string(1) “s” ["activities4"]=> string(1) “t” ["activities5"]=> string(1) “u” ["complaints2"]=> string(1) “w” ["complaints3"]=> string(1) “x” ["looking_for1"]=> string(1) “y” ["looking_for2"]=> string(1) “z” ["looking_for3"]=> string(1) “a” ["topics1"]=> string(1) “b” ["topics2"]=> string(1) “c” ["topics3"]=> string(1) “d” ["favorite1"]=> string(1) “e” ["favorite2"]=> string(1) “f” ["favorite3"]=> string(1) “g” ["favorite4"]=> string(1) “h” ["favorite5"]=> string(1) “i” ["guests1"]=> string(1) “j” ["guests2"]=> string(1) “k” ["guests3"]=> string(1) “l” ["guests4"]=> string(1) “m” ["guests5"]=> string(1) “n” ["private"]=> string(4) ” yes” ["trained_under"]=> string(4) ” yes” ["reason_tried_website1"]=> string(1) “a” ["reason_tried_website2"]=> string(1) “b” ["reason_tried_website3"]=> string(1) “v” }
Hi Jocelyn – that is nasty! That is a debug statement that should not be showing. It is dumping a user record that it thinks may be missing a value.
At first glance probably a bug introduced when checking for unset variables. In this case It should be allowed that a value is unset for a user. I’ll have a better look at the code later today.
If you want try a quick fix, then try replacing line 234,5 in ameta-list.php with:
if (!(isset($u[$is]))) $line[] = '""'; /* there is no value */else $line[] = .....
Re last line: Do you mean that the last user record is not listed ? or the last data column? I’ve got too many in my DB, I’ll have to setup a smaller one to test with.
I’ll get back to you later when I’ve had a good change to check this out.
Note: earlier versions are still at wordpress if you wish to revert. See http://wordpress.org/extend/plugins/amr-users/download/
Jocelyn,
See version 2.2.1 just uploaded – tested on small database with variety of “missing” fields and seems to be behaving itself now!
It claims I have the latest verson w/2.2
WordPress only checks every few hours (maybe once every 12 hours?) for updates.
Either download manually http://wordpress.org/extend/plugins/amr-users/ or if use plugin http://wordpress.org/extend/plugins/amr-impatient/ – goto upgrade checks after activating – it will force an update check earlier.
Much better! thanks
Although – it is still saying there are 4 lines to download and then it downloads the headers & 3 of the entries. There are actually 4 entries
Okay I have found it now. In file ameta-list.php, line 479 should have $total+1, not $total!!. The +1 is the heading line:
$lines = $c->get_cache_report_lines($rptid,1,$total+1);/* we want the heading line (line1), but not the internal names lines (line 0) , plus all the data lines, so need total + 1 */
I’ll put another update up shortly. Thank you for finding that!
Thanks for sharing this plugin with everyone and supporting it!
Hi Anna-Marie!
I’m so hoping you can assist me! I have been using your amr plugin for some months now, and for the past few weeks have been having an issue with it. When I click on “user – details”, the screen is blank – there is absolutely nothing that shows up now! Same thing for “Users – comments.”. I have been reading the posts and comments here, but I’m not very techy so I think some things are going over my head as to what the problem could be. I just updated the plugin though since WordPress alerted me that there was a newer version of the amr plugin. Please tell me if there is something I need to reconfigure – I’m not sure what I need to do. I used to be able to see the list of my users when I clicked on “users – details” and export to an excel spreadsheet.
Hi Roquita, sorry for the delay in replying – your message went into the spam queue.
Try going in to all the setting screens and “saving” once you have checked the configuration.
Jocelyn,
See version 2.2.1 just uploaded – tested on small database with variety of “missing” fields and seems to be behaving itself now!
Hi, Anna-Marie –
We have been having a problem with the AmR user plugin for about the past week or so…. Everytime we click on the “User Details” screen on the backend of our WordPress blog, we receive a message that says this:
“Users: Post and Comment Counts (user-03) No cache exists for this report.”
The “Users: Member Status and Dates” screen reads this way:
“Users: Member status and dates (user-02) No cache exists for this report.”
It’s as if the cache has completely disappeared, and we are not sure how to address this problem. Can you please help us? I really need to be able to access our member database and have been unable to do so since this problem began.
Thanks so much for your help!
Erin
Hi Erin, see reply on tim’s similar comment
http://webdesign.anmari.com/user-lists-or-reports-for-large-membership-sites/#comment-4289
Another thought that occurred to me : if you always go to the report after making a user update or config update, then you may well find that there is a moment when the cache is unavailable while it is being rebuilt. If you have a large number of members it could take a while – the cache log and cache status pages will give you an idea of the runtime too.
Hi Anna-Marie,
thanks for your plugin,
I have a few improvements,
here is a .diff file
http://wordpress.pastebin.com/uxmZtHv1
1st) when dealing with many lists, or with a few but long names, the options such as CSV EXport, CONFIGURE, etc, wont appear as there is no scroll set on the container of the page.
that is why i set a clear:left at the begining of those links.
Also, i think a select list would be a better implementation that just plain text going horizontally.
2nd) I have created a filter, amr_users_csv_line
this is done because some programs such as MS Excel do a very bad job at reading CSV files. and they will not interpret carriage returns, which if included between a pair of double quotes are valid in CSV.
it is nice to have the option to have a filter to hook into.
you can see an example of a filter here:
http://wordpress.pastebin.com/3e7y8rPd
3rd) also, in that pastebin, you will find an action, that will remove the overflow:hidden set by default #wpbody-content, which causes wide lists to be viewed partially.
include all the code as its GPL,
let me know if you include some of my improvements,
thanks for your time, and effort,
- wjm
Hi wjm,
Thank you kindly for your attempt to actually provide the code to improve a situation, rather than just noting a problem.
1) It took me awhile to figure out what you were talking about, until I tried looking at the admin side in other browers. Firefox was fine, but some others not so.
I attempt to use wordpress admin css selectors where possible to get a consistent look. In this case the “ul.subsubsub” which is at the top of other wp pages with similar navigation. Problem is wp had set that not to wrap (I guess they have fixed number of items, so they can do that).
Allowing a variable number of lists caused some of them to veer off the page ! I have added a whitespace: normal so that we can keep the rest of the styling without recreating it. That seems to have sorted that out.
Aside: RE: li’s rather than select /dropdown. I prefer not to add keyclicks and was not expecting that people would have huge numbers of reports, or very long names. (Long names look bad in the side menu). So for now will leave it as is (with css correction that is).
2) Hmm – maybe it’s your version of excel and/or whether you are doing a .txt or .csv import?. As you say, the plugin puts double quotes around all fields and this is the recommended action to cope with things like CRLF in the fields anyway. I am hesitant to force modify content for all just because excel does not cope correctly. I tried googling this a bit but as far as I can tell, it does work for some in Excel. I’d suggest to anyone with a version of excel where this does not work (or where they do actually want to totally remove the CRLF), rather do a search and replace in notepad+ first before going to excel? Or they could add your filter to their theme?
3. I could not find where the wordpress css has the “overflow:hidden set by default #wpbody-content”. Is this in admin wp? which version? In front end ? the old theme ? I did find this: http://core.trac.wordpress.org/ticket/8092, but it’s old.
In safari, chrome, IE and firefox, in admin side I have scrollbars showing.
Of course on the front-end in the new twenty-ten default theme, a wide table looks awful – overlaps the sidebar. What to do about that I think needs to be left to the web designer. Either do NOT have wide reports showing to the public, and/or add css to the theme or in html surrounding the shortcode to cope with it. (eg: a surrounding div with overflow: scroll will add scrollbars, – looks awful.)
Thanks once again for your input.
sorry, there is an error in
http://wordpress.pastebin.com/3e7y8rPd
please, use
http://wordpress.pastebin.com/qazj4jjP
Hi Anna-Marie
thanks for your fast response.
All the situations described happened in the back-end, using Google Chrome.
1) long names display fine in the side bar. I think you should not consider people having only three lists with short names. But its your software, so thats your call.
2) i agree with you. it´s MS problem, but.. when you are designing software that wont work with whatever your client is running, telling them its M$’s fault and not yours doesn’t solve a thing.
That is why a filter is a good option to solve this situation. (still not a solution as you are sacrificing content for compatibility).
3) that is changed via jQuery.
check postbox.dev.js
$(‘#wpbody-content’).css(‘overflow’,'hidden’);
it is the backend and Chrome does not show the scrollbars.
i didnt know you could use your plugin for the front-end.
(sorry i just tried it yesterday for the first time).
I have found another important flaw (not a security one) that took me something like 1 1/2 hour to figure out. let me know if i should do it here or over the email.
thank you.
- wjm
Hi wjm,
that’s okay – nice to know about problems as it is very hard to test all types and scenario’s. Re: flaw found, please send whichever way is easier for you, with as much info and/or screenshot if relevant. here or email anmari@anmari.com.
Re 3) with Chrome – did you try the recently updated version 2.2.3 – are the scrollbars still not showing with new version?
re 2) which version of office/excel? Maybe I’ll make that optional for next update.
ReRe2) MS Office 2007.
rere3) i have installed the latest version in a fresh wp3.0 install , and the scroll-bars seem to work.
I cannot update to the latest changes due the the many changes i have applied.
the main issue i have found so far is a software design problem.
1st, you assigned capability requirements based on the new way of doing it, using ‘manage_options’ and ‘edit_users’
i changed that to whatever i needed and that was still not working.
It took me an hour or more to understand why it will never work.
you used the level numbers (7 and 8) to add the submenu pages.
What i recommmend you to do is do it the way Contact Form 7 does it.
you defined constants,
`
if ( ! defined( ‘AUSERS_READ_CAPABILITY’ ) )
define( ‘AUSERS_READ_CAPABILITY’, ‘edit_users’ );
if ( ! defined( ‘AUSERS_EDIT_CAPABILITY’ ) )
define( ‘AUSERS_EDIT_CAPABILITY’, ‘manage_options’ );
`
but these constants can be defined earlier in the wp-config.php file. letting users customize it to their needs.
but the main problem here is,
that you are mixing these capabilities, (the read one with the edit one).
you ask user to have read capabilities ( edit_users ) to view the results table. but if the user wants to export that very same table that is viewing to CSV needs edit capabilities (manage_options).
I want the editor of my blog to be able to read and export resports, but i dont want her/him to mess around with arm-configuration. right now that is not possible to do.
read actions you users users.php and edit/configure actions should users options-general.php
I will email you what i have now, so you can find what i say with real code.
thanks. sorry for so many reports in just the second day of using your code, but that is the least i can do when using free software.
- wjm