export users csv switch columns of custom fields

[kentlii]

When exporting the users in a csv file, the columns name e-mail etc. are all correct, but the customs columns are switched around from some users. So what should be in one column is in another. For instance we have different contact forms, so in one column it is skype, another msn etc. for some users skype is in column R and for others in column T. This switch around only happens for the first 10 users.

I will take a guess and think this might be caused because we switched the order of the custom fields at the beginning, but we are now looking at adding another custom field and maybe switching the order again, which would then mess up the export file even more. I might be wrong on this one, just a guess.

Any fix for this or suggestion to how I might be able to get around this?

[Jason Caldwell]

Thanks for reporting this important issue.

Yes, I see the issue you're having.
There is no special order required for Custom Fields, because each Custom Field is identified by it's preceding "Custom Field ID" column. In the case of an export file generated with s2Member Pro, the order of these Custom Fields is simply the order in which they were saved, the last the time a User/Member ( or you ) updated their Profile. I do think we can improve upon this though, by applying a sorting routine before the export file is generated; that way it's easier to read in applications like Excel or OpenOffice.

@TODO :: establish an order for Custom Fields exported with s2Member Pro. Make it easier to read.

That being said, please understand that the order of Custom Fields is not vital to the export/import routines provided by s2Member Pro. All that's important is that each Custom Field is immediately preceded by it's Custom Field ID. And of course your Custom Fields should come after all other standardized fields in each line of your export/import files.

For instance, all of these examples would be perfectly OK; no negative effects.

Code: Select all

.. "Custom Field ID #1","Custom Field Value #1","Custom Field ID #2","Custom Field Value #2", ...

Code: Select all

... "Custom Field ID #2","Custom Field Value #2","Custom Field ID #1","Custom Field Value #1", ...

* It can even be a different order for each line in your export/import file.

Code: Select all

... "Custom Field ID #1","Custom Field Value #1","Custom Field ID #2","Custom Field Value #2", ...
... "Custom Field ID #2","Custom Field Value #2","Custom Field ID #1","Custom Field Value #1", ...

---

For the benefit of other readers.
* The examples above are abbreviated for improved clarity. In reality, these Custom Fields would be included as the last columns of your complete export line for each User.

Code: Select all

"ID","Username","Password","First Name","Last Name","Display Name","Email","Website","Level[0-4] or Role ID","Custom Capabilities","Registration Date ( mm/dd/yyyy )","First Payment Date ( mm/dd/yyyy )","Last Payment Date ( mm/dd/yyyy )","Auto-EOT Date ( mm/dd/yyyy )","Custom Value ( starts w/domain )","Paid Subscr. ID","Paid Subscr. Gateway","Custom Field ID #1","Custom Field Value #1","Custom Field ID #2","Custom Field Value #2", ...

[kentlii]

That would be great. The thing is that we have a points system which comes from another program. So if the custom fields are all in order and in the correct columns, we can simply copy paste from the other system into the excel and import it into the s2member again. But if they are not, we have to add the points one by one. We just started up the page and do not have that many members, so it is possible to do manually, but once we get going and get hundreds or maybe thousands of members, having to update manually 3 times per week, is going to be a lot of work.

But for the first update we have done I have done it manually editing the user accounts in wordpress, because I was affraid that since some columns are switched, that it could mess something up in the user accounts when uploading again.
If we change info in the exported csv and import it again, keeping the columns the same, this will add the correct information?

[Jason Caldwell]

Thanks for the follow-up.
I've had this corrected in the coming maintenance release of s2Member Pro v1.5.7.

Changelog excerpt:

* **(s2Member Pro). Bug fix.** s2Member Pro Export Files ( i.e. CSV export of Users/Members ) sometimes contained Custom Registration Fields ( e.g. at the end of each CSV line ) in a different order for each User/Member. This was NOT causing any negative effects with s2Member Pro during import/export. However, it was causing some confusion and extra work for site owners that use s2Member's export files for other operations. This will be corrected in the release of s2Member Pro v1.5.7.

100% complete

If we change info in the exported csv and import it again, keeping the columns the same, this will add the correct information?

Yes, that is correct. The order of Custom Registration Fields, is unimportant to s2Member. All that's important is that each Custom Registration Field is identified by it's preceding "Custom Field ID" column.

That being said, if you are making significant changes to a large number of accounts all at once with the import routine, I would advise you to do a full database backup prior to importing those changes.
Just in case something unexpected occurs.

Also, please note that while s2Member does not care about the order of Custom Registration Fields in your import file, it definitely DOES care about the order of all standardized import fields.

This is the format expected.

Code: Select all

"ID","Username","Password","First Name","Last Name","Display Name","Email","Website","Level[0-4] or Role ID","Custom Capabilities","Registration Date ( mm/dd/yyyy )","First Payment Date ( mm/dd/yyyy )","Last Payment Date ( mm/dd/yyyy )","Auto-EOT Date ( mm/dd/yyyy )","Custom Value ( starts w/domain )","Paid Subscr. ID","Paid Subscr. Gateway","Custom Field ID #1","Custom Field Value #1","Custom Field ID #2","Custom Field Value #2", ...

[kentlii]

most of it was fixed with the update. However now I get a new problem. The ones that are moved up to a higher level (higher than level 0) there is an entry created in the first payment column: a:3:{s:5:\level\";s:10:\"1300880852\";s:6:\"level1\";s:10:\"1300880852\";s:6:\"level2\";s:10:\"1301348869\";}"
and this also pushes the columns one to the right for those members, which creates the same problem as before.

[Jason Caldwell]

Thanks for reporting back on this.
s2Member Pro creates the "First Payment Time" column for all Members during exportation; even if it's blank. So if you're using an application that reads CSV Column Headers, values for a specific entry inside your export file should have no impact on the readability of the remaining data rows.

Or are we missing something?

[kentlii]

The problem was that some columns were still being pushed one to the left and there was one column that only showed up if there was data in the column.
I found a way around it though. We have two points columns one for current month and one for last month and these columns seemed to cause some problems, but everyone who had no points we set to 0 instead of leaving them empty and this seemed to fix the problem.
I thought it was the payment column that was the problem, but it does not seem to be the case, because if was fixed with this little trick.

[Jason Caldwell]

Great to hear that.
Thanks for reporting back on this. I really appreciate it.

[kentlii]

Now I have the problem again.
Changed to a new computer. I have the same version of office but when I export and open the excel document most of the data is in column A and just comma delimited in the same cells, but the accounts that have been upgraded to a higher level than level 0 cause problems. In the rows some of the data is pushed to row B,C,D and E. So if I do "text to column" to separate the data into different columns this meses up the account where there is data in other columns than column A. I do not quite get why only for some accounts it puts data in other columns and for some accounts it only puts data in column A.
So not sure what to do here. It is beginning to be a lot of work to do all the accounts manually.
Not sure if it is possible to export where the data is separated into the different columns when I do the export, this was how it did it before. Maybe some settings in excel that can be changed? Use office 2007.

[Cristián Lávaque]

I'm not familiar with Office, but I use OpenOffice and haven't had this problem. When I import CSV data, I'm asked what the delimiter is and am shown a preview before it opens the file into columns properly. Why don't you try it and see if that solves your problem?

[kentlii]

I will try, but this problem has been there before if you check out the earlier posts.

[kentlii]

Tried openoffice and it creates the same problem

[Cristián Lávaque]

Thanks for trying it and reporting back.

Could you share one row that doesn't have the problem and at least one other that does? xxxx'ing out any personal information, of course.

[kentlii]

Here is an example. I have changed the info of course, but the columns, titles etc. are the same. As you see with the one in row 3 there is only info in column A and in the one in row 2 there is info in several columns and some of the info is pushed into other columns.
Row 2 is at level 1 and row 3 is at level 0 in the s2member levels.

[Cristián Lávaque]

Thank you.

The format of your CSV file is wrong, that's why Office or OpenOffice are confused when parsing it.

You have double double quotes, and not quotes at the beginning, for example. Is that how s2Member exported it or did you have this happen after editing and saving it?

[kentlii]

this is how it was exported.
The places there are double quotes I believe is where the info is not filled out.
But the problem is that for some users it puts all info in column A and for others it puts it in column A-E.

[Cristián Lávaque]

That is really weird.

This is what you have and how it should be (just the first few values of one row):

Code: Select all

"9,""jayer"","""",""Francisco Javier"",""S Ocho"",""Francisco Javier"",""xxx@hotmail.com"","""",""subscriber"","""",""03/18/2011"","""",
"9","jayer","","Francisco Javier","S Ocho","Francisco Javier","xxx@hotmail.com","","subscriber","","03/18/2011","",

What versions of WordPress, s2Member and s2Member Pro are you running?

[kentlii]

version 3.5.8 and pro module v1.5.8
wordpress 3.1.2

[Cristián Lávaque]

Thank you. OK, you're up-to-date with those. I'll notify Jason of this problem then.

[Jason Caldwell]

Thanks for reporting this important issue.
~ and thanks for bringing this to my attention Cristián.

this is how it was exported.
The places there are double quotes I believe is where the info is not filled out.
But the problem is that for some users it puts all info in column A and for others it puts it in column A-E.

The columns are going to be out of whack in that file you attached, because ( as Cristián mentioned ), the file is corrupted with too many double quotes nested into each other. I know you've said, "that's how s2Member exported it", but actually, this is not how s2Member's export routine works.

My guess is that whatever application you used to open the CSV file, added those in there by default, so it appears that it's s2Member's fault, but I suspect it's not.

Please correct me if I've missed something though.

In the mean time, try exporting your Users with s2Member, and open the CSV file with a plain text editor like Notepad. That way you can see what the raw data looks like. Opening a CSV file with an editor like Excel or OpenOffice works fine too, but the application settings must be configured properly, as is the case with any CSV file. In other words, you need to configure delimiters and encapsulation when you open the file with Excel or OpenOffice. s2Member uses comma delimiters and double quote encapsulation.

[kentlii]

I use excel 2007 to open the files.
I was asking in an excel forum about this too, because I suspect that there are some settings that have changed from one computer to another.
I changed the extension to .txt and opened it in excel and I am able to do "text to columns" and it is done correctly, but then when I save it as csv I am not able to import it again.

[kentlii]

Ok, I finally found a solution. I also posted in an excel forum and the solution was pretty simple. I changed the regional settings on my computer to English-United States instead of Spanish-Spain, now it exports perfectly.

[Cristián Lávaque]

Great! Bien hecho.

[kentlii]

New problem.
I updated the s2member and s2member pro. Do not think this is the cause of the problem, but just so you know.
I have added an extra custom field and this seems to be cause some problems. It is the same problem as before with the points fields. If the field does not have any info it does not appear when exporting. So this means that some columns are moved because the custom field is not there.
Also after row 60 none of the custom fields appear unless there is information in them, so this completely messes up the csv.

[Cristián Lávaque]

Could you show me the CSV file please? (x'ing out private info)