As of 2022, this process has significantly changed. The National LP is now handling the extraction, matching, and import of voter files. You should simply extract the .txt files from the SOS zip folder and ship them to National. You may, however, build a spreadsheet with all the LBR records in it because you may find this useful in fulfilling various data requests from party members.
Summary
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.
Procedures
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.6 GB in size. The LBR records are spread throughout the parts and need to be combined into one csv file with a header.
In mid 2023, the Colorado Secretary of State changed the way the voter file is delivered. Instead of being delivered in several parts, it is delivered as a single file, about 1.6gb in size. Microsoft Excel and Notepad cannot open this large a file. You have to use utilities available on Linux or Linux-like systems. Fortunately, Microsoft offers tool called Windows Subsystem for Linux, known as WSL. The default os it installs is Ubuntu. This is available on Windows 10 and later. See https://learn.microsoft.com/en-us/windows/wsl/install. Once you have Ubuntu installed, you’ll also want to install xsv, https://lindevs.com/install-xsv-on-ubuntu/, a tool for slicing and dicing csv files.
This command extracts the Libertarian voters from the SOS file, xsv search -s 34 LBR sosdata.csv > sosdata-lib.csv, and puts them in a file called sosdata-lib.csv. Change it to a txt file.
This command breaks the large voter file into chunks with 500,000 voters each and puts them in a directory called split: xsv split -s 500000 split sosdata.csv. These are typed as csv files, so you need to change them to txt files so you can change the field types of some fields as noted in the next step.
Import the .txt files into Excel. During the import process, change the Voter Id, Last Name, First Name, Middle Name, Split, and both Zip and Zip+4 fields to a Text field (not General). This is necessary because Excel treats some content as different data types. 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 and use the Voter Id 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.
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.
I. Backup
1. Add column to Backup file at end called Contact Id. Copy 1st column to it.
2. Add column between VoterId (D) and External Id(E) called NewVoterId. Add this formula to all rows:
=IFERROR(IF(LEFT(D2,3)=”CO_”,RIGHT(D2,LEN(D2)-3),D2),””)
3. Sort by NewVoterId and remove all entries that are “0” (zero).
4. save the Backup file.
II. SOS
5. on SOS sheet, Add column before Voter_id called ContactId
6. Add this formula to all rows.
=vlookup(b2,[CW_Backup_211128.xlsx]All!$e$2:$bd$90000,50,false)
this puts the contact id if it finds a voter id in the backup file. Change 90000 to max rows in backup.
7. copy the ContactId column and paste the Values back into it (ctrl-alt-v)
8. sort Backup file by New Voter Id, Last name, First Name. save it.
9. sort SOS by ContactID, Last Name, First Name
III. Backup
10. on Backup file, before Formal Title, add column called MatchAddr.
11. Put this formula in MatchAddr to concatenate Last Name, First Name(3), Zip, Address (house number) : =CONCAT(k2,LEFT(i2,3),t2,LEFT(p2,FIND(” “,p2)-1))
12. copy the MatchAddr column and paste the Values back into it (ctrl-alt-v).
IV. SOS
13. on SOS file, before County Code, add column called MatchAddr.
14. use this formula to concatenate Last Name, First Name (3), Zip, Address (house number) :
=CONCAT(f2,LEFT(g2,3),y2,LEFT(v2,FIND(” “,v2)-1))
15. if the value in the ContactId field is #N/A, use this formula:
=vlookup(cnnn,’CW_Backup_211128.xlsx’!g$2:bd$90000,49,false)
16. copy the ContactId column and paste the Values back into it (ctrl-alt-v).
17. clear the MatchAddr column.
18. In the rows from nnn (see formula in 8) to the end, put the letter ‘x’ in the MatchAddr column.
19. sort by MatchAddr, Contact Id, Last Name, First Name.
20. sort Backup file by ContactId, Last Name, First Name.
21. on the rows with an ‘x’ and with a Contact Id, manually look up the Contact Id on the Backup file. Check to see that the person is the right person and not a close match. Check birth date, middle name, etc. If not a match, replace ContactId with “#N/A”.
22. delete the MatchAddr column.
23. Sort SOS by Contact Id, Last Name, First Name.
24. 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.
=VLOOKUP(a2,[CW_Backup_211128.xlsx]All!$a$2:$bd$90000,50,FALSE)
25. Add column to SOS, look up Old Status with this formula
=VLOOKUP(a2,[CW_Backup_211128.xlsx]All!$a$2:$bd$90000,32,FALSE)
26. Add column to SOS, look up Old Party with this formula
=VLOOKUP(a2,[CW_Backup_211128.xlsx]All!$a$2:$bd$90000,25,FALSE)
27. Add Changes 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.
=IF(BC2<>”Libertarian”,”rereg”,IF(AF2=BA2,IF(AB2=BB2,”ok”,”sbad”),”pbad”))
28. remove “Congressional “, “State Senate “, and “State House ”
29. Change Party from LBR to L. Put L in all Preference rows.
30. remove foreign mail addresses. Sort by each of the Mailing columns to find which columns have info in them.
31. sort by MailAddr3, Combine/remove data from this column into MailAddr1.
32. sort by MailAddr2, Combine/remove data from this column into MailAddr1. This formula will concatenate MailAddr2 and the first piece of MailAddr1: =CONCAT(AN2,” #”,LEFT(AM2,FIND(” “,AM2))). Some addresses will combine 1 and 2, and others will combine 2 and 1. You just have to work through this by hand.
33. delete following columns
ID_REQUIRED
PERMANENT_MAIL_IN_VOTER
SPL_ID
MAILING_COUNTRY
MAIL_ADDR2
MAIL_ADDR3
VOTER_STATUS_ID
SPLIT
STATUS_REASON
EFFECTIVE_DATE
UNIT_NUM
UNIT_TYPE
POST_DIR
STREET_TYPE
STREET_NAME
PRE_DIR
HOUSE_SUFFIX
HOUSE_NUM
ADDRESS_LIBRARY_ID
PRECINCT_NAME
STATUS_CODE
VOTER_NAME
COUNTY_CODE
34. add column at end called SOS Import Date. Enter sos file effective date (2019-03 = 2/28/19)
——————
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
Add County Commissioner, City Council, and School Districts
1. Open CountyPrecinctSplit.xlsx
2. for counties that you have data
3. Sort SOS spreadsheet (New/Update) by County, Last Name, First Name.
4. Copy County Commissioner City Council/Ward School
to new columns on SOS from CountyPrecinctSplit.xlsx.
=VLOOKUP($AF2&””,[CountyPrecinctSplit.xlsx]Sheet1!$B$2:$H$14000,4,FALSE); * county commissioner
=VLOOKUP($AF2&””,[CountyPrecinctSplit.xlsx]Sheet1!$B$2:$H$14000,5,FALSE); * city council
=VLOOKUP($AF2&””,[CountyPrecinctSplit.xlsx]Sheet1!$B$2:$H$14000,6,FALSE); * school district
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 (E), call it SOS Voter Id (F)
4. In the SOS Voter Id on rows where Registered Party Affiliation = Libertarian,
use this formula:
=VLOOKUP(Ennnn,’SOS_2021-10_LBR.xlsx’!$b$2:$b$50000,1,FALSE) where nnn is the first row in step 4.
4a. copy the SOS Voter Id column and paste the Values back into it (ctrl-alt-v).
5. sort backup by Registered Party Affiliation, SOS 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 called SOS_2021-10_FormerLibs.
On the new spreadsheet, delete the SOS Voter Id column.
For each of the SOS Voter Part files.
1. open the Part file.
2. In the Registered Party Affiliation column on the FormerLibs spreadsheet, look up the new Party with this formula:
=vlookup(e2,’Registered_Voters_List_Part1.xlsx’!a$2:am$500001,34,false)
3. In the Party Affiliation Date, look up the new Date with this formula:
=vlookup(e2,’Registered_Voters_List_Part1.xlsx’!a$2:am$500001,36,false)
4. copy and paste the values back into the Registered Party Affiliation and Party Affiliation Date.
5. sort the FormerLibs spreadsheet by Registered Party Affiliation, Last Name, First Name, Middle Name.
6. repeat steps 1-5 for each part file.
7. In the Registered Party Affiliation column, replace #N/A with F(ormer Libertarian); in the Party Affiliation Date, replace #N/A with the Month End Date of the voter file.
8. In the Registered Party Affiliation column, replace
DEM with D
REP with R
UAF with U
ACP with C
GRN with G
Other Parties with O
9. on the new spreadsheet, delete the following:
Legacy CRM ID
New voter id
External Identifier
Individual Prefix
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 affiliates@lp.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
VOTER_ID
COUNTY – county name – must match Civi County Name
LAST_NAME
FIRST_NAME
MIDDLE_NAME
NAME_SUFFIX
RESIDENTIAL_ADDRESS
RESIDENTIAL_CITY
RESIDENTIAL_STATE
RESIDENTIAL_ZIP_CODE
RESIDENTIAL_ZIP_PLUS
REGISTRATION_DATE – mm/dd/yyyy – date voter first registered to vote
STATUS – Active,Inactive
BIRTH_YEAR
GENDER – Male,Female
PRECINCT
PARTY – L
PARTY_AFFILIATION_DATE – mm/dd/yyyy – date voter last registered with LP
PHONE_NUM
MAIL_ADDR1
MAILING_CITY
MAILING_STATE
MAILING_ZIP_CODE
MAILING_ZIP_PLUS
CONGRESSIONAL – number only
STATE_SENATE – number only
STATE_HOUSE – number only
SOS Import Date – mm/dd/yy – date of import
————–End of Format———-
