| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Get control of your email attachments. Connect all your Gmail accounts and in less than 2 minutes, Dokkio will automatically organize your file attachments. You can also connect Dokkio to Drive, Dropbox, and Slack. Sign up for free.

View
 

decoding-neo-members-list

Page history last edited by David Andrews 3 years, 11 months ago

 

Table of Contents

 

You can help by writing or correcting items, or just by commenting. Please read About YahooGroupedia.

 


 

 

Background

 

Since the roll-out of the "Neo" version of Yahoo Groups the export of a group's Members List yields a page which contains records in an undocumented and disorganized form. This is a description of how to interpret the data and how to re-align it so that it is useful. Much of this information comes from a topic in the GroupManagersForum.

 

Hopefully this is a document that will become obsolete before it is finished. That is, hopefully Yahoo will fix the bugs in the export before too much more time has passed. But as of the current date (2014-03-15) it has been seven months already.

 

Limitation: the techniques here will not overcome the 1000 record export limit currently imposed by Yahoo.

 

Alternative: rather than try to work with Yahoo's broken export, one can sign up for ModTools - a collection of tools useful for Yahoo Group moderators. The cost is $5 per year per group (all moderators of the group can use ModTools for one subscription). ModTools is able to export the entire members list and in a useful format.

 

Also have a look at the front page.

 

Export from Yahoo

 

Export it in TSV format, as that avoids an ugly bug in the handling of CSV output when a user's name has a comma in it.

 

Import into Excel

 

This description is written in terms of Microsoft Excel, but a similar procedure will likely be possible in other spreadsheet products.

 

Start by inserting a blank row at the top for column headings. Use the "Freeze Panes" feature to prevent Row 1 from scrolling off screen as you work. Here are column headings you can copy and paste:

 

Status     Delivery     Format     Bounce     Privilege     Hidden     File     Join     B.Date     B.Status     Image     user#     Name     email     ID     Alias     Index

 

Column Descriptions

 

The column names and some of the field content below were obtained by examining the JSON format data from which the export is made. The field content described here is "as exported", as seen in exports from several groups.

 

Void: note that when the content is described as "void" that means that Yahoo failed to export that field, and all the remaining fields in that row are shifted left as a result. See the description below of a realignment procedure to put the fields back into their proper columns.

 

A: moderatorStatus

"OWNER", "MODERATOR", or void.

B: deliveryType

"SINGLE", "DIGEST", "ANNOUNCEMENT", or "NONE".

C: emailPreference

"PLAIN", "HTML", or "NONE".

D: memberStatus

"CONFIRMED" or "BOUNCED".

E: postingStatus

"DEFAULT", "MODERATED", "UNMODERATED", or "PROHIBITED".

F: isEmailHidden

Blank for false, the digit "1" for true.

G: fileAccess

Blank for false, the digit "1" for true. Indicates whether the member is allowed to upload files.

H: date (Join Date)

See Dave's instructions for converting from Unix time to Excel.

 

In JavaScript, the date can be multiplied by 1000 then a JavaScript Date object can be created from that. For example the following will convert 1381282427 (as a string such as what we could have here) into a Date object then format that using the ISO standard.

 

function ConvertDate(s) {
var d = new Date(parseInt(s)*1000);
return d.toISOString();
}

 

Result: 2013-10-09T01:33:47.000Z

 

I: bounceDate

Zero for non-bouncing, otherwise Unix time.

J: bounceStatus

"SOFTBOUNCING", "HARDBOUNCING", or void for non-bouncing.

K: image

"Array" if the member has a thumbnail image, otherwise void.

 

L: userId (User #)

A sequence number (serial number) representing this Email address.

M: name (Display Name)

If exported in CSV form this datum occupies zero, one or more fields because Yahoo did not delimit the field, and a user name may contain commas. For this reason it is advisable to use TSV format for the export (it isn't known if it is possible to have a tab character in a name).


In most cases the content of this field looks kind of display name-ish, but it isn't necessarily the Display name of the member's profile, so I'm a bit puzzled as to where this is coming from.

N: email

Nothing odd about this one.

O: yid

The member's account ID, or void for email-only members.

P: yalias

Often equal to the account ID, sometimes not, and often void. Always void if the ID is void (thank goodness).

Q: index

Always contains the digit "0" (so far as is known)

 

Re-aligning the fields

 

The general approach is to work left to right, inserting cells to fill in for void content.

 

Column A

It is just as well that the whole things starts with a field that is mostly void, but one which (in groups under 1000 members) will always contain at least one non-void. That sort of sets the stage for straightening out the rest of the columns.


Click the upper left corner to select the whole sheet, then Data menu, Sort. Enable the column header (so it doesn't get included in the sort range) and sort by Status (having enabled the column header, the column selector will show you the header rather than the column letter). Skim down through the rows until you find the first "MODERATOR" entry, select all the cells in column A above that row, and Insert cells (move to the right) to replace the void with a blank. Do the same for the rows between "MODERATOR" and "OWNER", and the same for those afterward.

 

Congratulations, now columns A through I are all properly aligned.

 

Columns J and K

Columns J (Bounce Status) and K (Array) are the next troublemakers. But each yields to the same treatment as column A, and you get L (User #) in the bargain. Specifically:

 

Select the entire sheet then use Sort on column J (Bounce Status). The rows containing "HARDBOUNCING" and "SOFTBOUNCING" are correct, the rest were void and need to have a cell inserted into column J.

 

Select the entire sheet then use Sort on column K (Bounce Status). The rows containing "Array" are correct, the rest were void and need to have a cell inserted into column K.

 

Column L is never void so it is now aligned properly as well.

 

Column M

Column M (Display Name) has a two tricks up its sleeves. Not only is it often void, but its data might have a comma in it and no delimiting characters were used.

 

If you exported in CSV format Excel may have spread it across multiple columns, and absorbed the commas (which you'll have to put back). For this I can only offer the observation that column N (Email) always contains an "@" character. I've used this to add-hoc realign that column and thereby disambiguate column M. But it is likely to be a hellish job with a large export, and God help you if someone's display name has an "@" in it.

 

If you exported in TSV format your only challenge is to insert cells for rows were the name was void. Sorting doesn't help you to group them unless you find a way to sort on whether the cell contains an @ or not. Which I suppose could be done by inserting a temporary column with a string function that detects the @ for you.

 

Column N is never void so it is aligned once M is.

 

Columns O and P

Columns O (ID) and P (Alias) will both yield to the sort and insert technique. This works only because there can't be an Alias without an ID.

 

Having done those two, you should have the happy confirmation of row Q (Zero) all lined up.

 

Comments (0)

You don't have permission to comment on this page.