1. Export a backup spreadsheet file of all records in the CRM using the CO_AllFields format.
2. find the Libertarian records in the SOS voter file and collect them into a single spreadsheet.
3. match voter file (just libs) to backup using vlookup to locate voter id, retrieve contact id.
4. match remaining names on voter file with no contact id to last name on backup file. manually examine them, copy paste contact id if match.
5. anybody left with no contact id is a New Voter.
6. add columns to voter file for old precinct, status, party using vlookup to get info from backup spreadsheet.
7. check for changed info. changes go into Update spreadsheet.
8. fix New and Update spreadsheets.
9. match registered Libertarians on backup file to voter file using voter id.
10. copy rows with no SOS voter id from backup to a spreadsheet called FormerLibs.
11. fix as required.
12. send to National Affiliate Support for processing.
This process creates 3 import files to be loaded by National’s Affiliate Support. The files are New, Update, and FormerLib.
Using the voter file from the Secretary of State, unzip it to your hard drive. It’s about 1.2 GB in size and comes in about 8 parts. The LBR records are spread throughout the parts and need to be combined into one csv file with a header.
Import the .txt files into Excel. During the import process, change the Last Name, First Name, Middle Name, and Split to a Text field (not General). Open a second blank spreadsheet. Copy the first line of the part file (headings) to the new spreadsheet. Save it as a xlsx file with the name SOS_yyyy_mm, where yyyy is the year and mm is the month.
A very few records may be screwed up because of some weird character in an address. You can find these by sorting the part by Last Name. If there is one, you will get a popup saying that it is trying to sort numbers in the Last Name. You may have to open the part file in Notepad to find it. Fix the record in Notepad, save it, and reopen it in Excel.
For each of the part files, sort it by the Party field and last and first name.
Scroll down through the records until you get to the first line with LBR in the Party field. Use the Find feature and look for LBR with “match entire cell contents” checked.
Select the entire line.
Scroll down through the LBR records until you come to the last one. Or use the Find feature and look for REP with “match entire cell contents” checked. REP comes right after LBR.
Select all the records from the first LBR record through the last.
Copy and paste them into the new spreadsheet after the preceding group.
Do this for each part file. Save the spreadsheet as a xlsx spreadsheet. Close the spreadsheet.
Open the backup file before opening the SOS file.
1. Add column to Backup file at end called Contact Id. Copy 1st column to it.
2. on SOS sheet, Add column before Voter_id called ContactId
3. Add this formula to all rows.
this puts the contact id if it finds a voter id in the backup file. Change 65000 to max rows in backup.
4. sort Backup file by Voter Id, Last name, First Name. save it.
5. sort SOS by ContactID, Last Name, First Name
look up last name on lines with no contact id with
where nnn is first row in backup of names without a voter id and xxx is first line of names with no contact id.
If N/a, clear cell, if last name, validate first and middle names, and maybe phone. if not found, clear.
if found, copy/paste Contact Id from backup to SOS.
6. Sort SOS by Contact Id, Last Name, First Name. Then move everybody with no Contact Id to a new spreadsheet called SOS_yyyy_mm_New. Copy the column headings, too.
6.5 do not delete rows with no contact id from SOS spreadsheet. They are used in Former Libs process.
7. Add column to SOS, look up Old Precinct with this formula. You will have to adjust the backup date and possibly the column on this and the next two formulas.
8. Add column to SOS, look up Old Status with this formula
9. Add column to SOS, look up Old Affiliation with this formula
10. Add test column to compare precinct, status, and affiliation. If you get the #NAME? error for some reason, you may have to retype this formula into the cell. Replicate down through all columns with a Contact Id.
11. Create a new spreadsheet called SOS_yyyy_mm_Update. Copy the SOS column headings to it.
12. Sort SOS spreadsheet by Test, Last Name, First Name. Move everybody with test column not equal “ok” to it.
12.5 do not delete rows with no contact id from SOS spreadsheet. They are used in Former Libs process.
On both the Update and New spreadsheets
1. remove foreign mail addresses. Sort by each of the Mailing columns to find which columns have info in them.
2. remove inactive and undeliverable campus mail addresses (e.g. Bradford Hall)
3. combine mail addr 1 and 2 for active voters.
4. remove “Congressional “, “State Senate “, and “State House ”
5. Change Party from LBR to L.
6. sort by Mailing State & City, review for errors.
7. delete following columns
8. add column at end called SOS Import Date. Enter sos file effective date (2019-03 = 2/28/19)
9.1 on New sheet only, delete the Contact ID column.
10. Save the spreadsheet.
11. Save the spreadsheet as csv file.
To create the FormerLibs file:
1. open both backup and SOS (all libs)
2. on backup, sort by Registered Party Affiliation, Last, first, middle names
3. insert column after voter id (D), call it New voter id (E)
4. in the cells on rows where Registered Party Affiliation = Libertarian,
use this formula:
=VLOOKUP(Dnnn,[SOS_2019_03.xlsx]Sheet1!$A$2:$A$50000,1,FALSE) where nnn is the first row in step 4.
5. sort backup by Registered Party Affiliation, New Voter Id, last, first, middle name.
6. create new spreadsheet
7. copy the column headings from the 1st row of backup to it
8. at the bottom of the Libertarians, there will be 2-3,000 rows with #N/A in the New Voter Id column. These are people
who are no longer registered libertarians.
Copy these rows to the new spreadsheet.
9. on the new spreadsheet, delete the following:
Legacy CRM ID
New voter id
Individual Suffix and all columns following
10. Add SOS Import Date column after Last Name and Enter effective month end date
11. Save file with name SOS_yyyy_mm_FormerLibs.xlsx
12. Save as csv
Upload the 3 csv files to the Data folder under CO Nationbuilder Migration directory.
Send the following message to firstname.lastname@example.org, changing the filenames to the appropriate dates:
“There are 3 SOS Voter files uploaded to the Data folder of the CO Nationbuilder Migration directory.
SOS_2019_03_FormerLibs.csv. Change the Registered Party Affiliation to null or Former Lib, add Party Change Activity. I guess you can use the SOS Import Date as the Activity Date since we don’t know the exact date. The SOS Import Date should update the SOS Import Date, SOS Last Updated, and the Party Affiliation Date.
SOS_2019_03_New.csv contains voters who are completely new to us.
Put the Residence Address in the Voter Address field. The SOS Import Date should update the SOS Import Date, and SOS Last Updated
SOS_2019_03_Update.csv contains voters who are already on our file but their information has changed in 1 or more of several ways.
Their voter info should be treated as new.
Put the Residence Address in the Voter Address field. The SOS Import Date should update the SOS Import Date and SOS Last Updated. Put the Voter Id and Name in the record.
Update ALL of the voter info.
1. they moved.
2. they became inactive.
3. they came to us from an outreach booth (no voter id) and then registered libertarian.
4. they went from libertarian to something else and then changed back to libertarian.
This file contains the CiviCRM Contact Id”
Format for Update and New csv files
ContactId – Update file only, delete from New file
COUNTY – county name – must match Civi County Name
REGISTRATION_DATE – mm/dd/yyyy – date voter first registered to vote
STATUS – Active,Inactive
GENDER – Male,Female
PARTY – L
PARTY_AFFILIATION_DATE – mm/dd/yyyy – date voter last registered with LP
CONGRESSIONAL – number only
STATE_SENATE – number only
STATE_HOUSE – number only
SOS Import Date – mm/dd/yy – date of import
————–End of Format———-