Maintaining member lists from CSV downloads

[amc79]

Does anybody have suggestions for maintaining a list of members over time from the CSV downloads from the ward web site. Here's a sample scenario that I'm trying to address:

We're trying to keep track of who has spoken in sacrament meeting. Currently, we have a simple two column spreadsheet with a name and a date they gave a talk. I'd like to be able to identify which members haven't spoken in X-amount of time. The catch is, how to account for move ins and move outs from the ward. We live in a university community, so there's a significant amount of "churn" as members graduate and others move into the area. Ideally, I'd like to be able to do some kind of VLookup to cross check the member list from the unit website with the list of sacrament meeting speakers. The problem is, the LUWS information comes down as households, whereas frequently family members are assigned to talk individually.

Anybody have suggestions for either maintaining an up-to-date list of ward members without completely starting over every time we get a change in membership and download another csv file from the ward website or how to convert the csv household information into a list of individuals in excel?

Thanks.

[WelchTC]

This does not seem like it would be a very hard program to write if someone were to do so. Here are my basic thoughts:

1. Application imports database. While importing, application does a lookup to see if member is already in database. If not, it adds the member. If so, just update the data (phone # changes, family member changes, etc).
2. Any newly added records are assigned a unique ID. Most DB's do this automatically.
3. While importing, an array is kept of those members who were imported. After import, rip through the array and figure out who did not have a record in the import. Those are people you need to "prune" from the database because they are no longer in the ward.
4. Have a separate table that tracks who (by ID) gave the talk. In this table would have to be which member of the family gave the talk since the import is family based.

OK, I know that this is very simplistic and I'm sure that there are a bunch of issues I'm not thinking about but on the surface it looks like a very easy program to build. What am I missing?

Tom

[russellhltn]

If you don't mind keeping that information in MLS, I think there are custom fields that can be used for that purpose. Might be worth looking into.

[Pete]

Anybody have suggestions for either maintaining an up-to-date list of ward members without completely starting over every time we get a change in membership and download another csv file from the ward website or how to convert the csv household information into a list of individuals in excel?

Here is an ugly perl hack to give you the second thing you ask for - one row per person in the ward. It takes as input the vcard export.

Code: Select all

#!/usr/bin/perl
while(<>){
 $last=$1 if(/N:(.*);/); # last name is between N: and ;
 if(/^ /){               # family members start with a space
  s/(=0D=0A=)?\s*$//;    # get rid of the junk after the name
  print "$last,$_\n";    # print name, last name first
 }
}

It has a few problems - the last name will be wrong for kids who have different last names from the head of household (we'll be fixing this shortly by delineating last names.) The second problem is that it doesn't do much for helping manage diffs. The UID may change between runs - so for now the most "unique identifier" may be the name, which isn't always unique...

[grhart-p40]

Sounds like a better job for MLS.

1) Create a "Custom Member Field" in MLS that contains the date the member last spoke.
2) Create a "Custom Report" in MLS containing this custom field and any others you like.
3) If you want it in CSV, select a field in the custom report and select Ctl-Shift-S to export it.

Benifit: move-ins/outs are managed for you; no managing multiple lists/data-bases.

-Greg

[pfnelson]

I use a diff tool called Compare It (http://www.grigsoft.com/wincmp3.htm) to compare changes and merge changes between files.

The merging capability only is active for the trial period and then you need to purchase it. I am sure there are other similar tools.

[WelchTC]

I use a diff tool called Compare It (http://www.grigsoft.com/wincmp3.htm) to compare changes and merge changes between files.

The merging capability only is active for the trial period and then you need to purchase it. I am sure there are other similar tools.

For those Linux users, kdiff3 works handy for the same thing and it is free!

Tom