Revisions 2

Overview 3

Export a petitions.csv file 4

Method One (works for everyone) 4

Method Two (shorter) 6

Enable Macros 7

Open the Process Petition List file 8

Import a petitions.csv file 9

Classify cities 10

Check For Errors 11

A Cheat - and the Tradeoffs 12

How to Fix Most Errors – Data Cleaning 13

False Positives (good data highlighted as BAD) 15

The Last One Percent 17

Prepare for Final Publication 18

Archive Highlighted Rows 18

Final Formatting 18

Print at Home 18

Print at Staples, Office Depot, etc. 18

Data Cleaning via the Archive tab 19

Acknowledgements 19

Appendix – Macros 20

Appendix – Edit Print Header 24

Appendix – One-Page Instructions 26

Appendix – Developer Notes 27

The latest Excel program file is always found here:

https://conventionofstates.com/files/process-petition-list

The latest guide is always here (MS Word):

https://conventionofstates.com/files/process-petition-list-guide

The legacy Excel program file for 10-column petitions.csv files is here:

https://conventionofstates.com/files/process-petition-list-legacy

Recording of training:
https://rumble.com/v20zmyc-process-petition-list.html The “meat” starts at 24:30

The Process Petition List macro-enabled Excel file transforms a petitions.csv file exported from the Legislative Management System (LMS) into a nicely-formatted printed list of petition signers suitable for handing to legislators.

This document explains how to:

1. Export a petitions.csv file from the LMS
2. Import petitions.csv file into the Process Petition List program
3. Highlight data problems in the list
4. Fix problems at the source (Citizen Builder, usually)
5. Print a clean list for legislators

Visually,

YOU MUST HAVE EXCEL ON EITHER A PC OR A MAC FOR THIS PROCESS TO WORK!!

If you do not have Excel, you have several options:

1. Buy MS Office (you don’t need Pro to get Excel) from Microsoft or a software reseller. Price varies widely depending on what kind of user you are, but start at $100.
2. Buy a subscription to Microsoft Office 365 aka office.com aka Microsoft 365. The Personal version is ~60/yr for the auto-renewal version or ~140/yr otherwise.
3. Buy MS Office from eBay, but do not consider anything that costs about $10. Expect to pay $50 to $150 for a Pro version you can activate and get updates for. Availability varies.

First gain access to the Legislative Management System (LMS). Your state director can grant you access.

Two processes can export a petitions.csv file. Some will be able to use both, but some may only be able to use the first method, especially if they have access to only one lower house district.

Go to the LMS (coslms.com), choose Legislators near the top of the left navigation pane. You will get this.

• Choose the latest Session
• Choose your chamber (Assembly/House or Senate)
• Select “Show 100 rows” rather than 10 rows
• Sort by district if you can see more than one district
• Find your district

Click on the LEGISLATOR (in Name column) for that district, not the district number. You will then see this:

Click on the gray Petitions button. You will see the following screen.

Click Export button, choose “CSV”

It will take a minute or two to generate the CSV file after clicking “CSV”

If an “Opening” dialog box opens, like this:

Click Save File and save to your local drive. Macros may not work if you merely open it.

Find the saved file (typically in the downloads folder) and Open it in Excel. You MUST open it in Excel. If you have another spreadsheet program as well as Excel, right click the file and “Open with” Excel.

This method may only be available to those who can see multiple districts.

Go to the LMS (coslms.com), choose Tools at the bottom of the left menu

Then choose Reports

Then choose Petitions under the list of reports

Choose your State.

Enter your District (e.g., “CA 29”). Only ONE district!

Note: the program works fine on multiple districts, including whole states. The only downside is that the titles on the printed pages will not be correct. To fix these see Appendix – Edit Print Header

Click the “Export” button on the row of boxes UNDER the District boxes and choose CSV.

It will take a minute or two to export the CSV file after clicking “CSV”. It will be saved to your default download folder.

If an “Opening” dialog box opens, like this:

Click Save File and save to your local drive. Macros may not work if you merely open it.

Find the saved file (typically in the downloads folder) and Open it in Excel. You MUST open it in Excel. If you have another spreadsheet program as well as Excel, right click the file and “Open with” Excel.

If you see a red message bar at the top of the screen, close the file, go into file explorer and right click on the file. Then select properties, and click the box next to “unblock” at the bottom of the dialog. Close the dialog and reopen the file.

Click the “Enable Editing” or “Enable Content” button (sometimes both) in the yellow Message Bar at the top of the window when you open an Excel file, like below:

You may have to click one or both of these buttons for both the petitions.csv file and the Process Petition List file (we open this file in the next section). This is safe because we know the origins of both files.

After enabling content or enabling editing, you will see four buttons at the top of the Table tab (tabs are also called sheets or worksheets) to the right of the data table, in the Process Petition List file. If you do not see four buttons, scroll to the right to make sure they are really missing, then try the techniques below.

The indication that macros have been enabled is seeing four buttons in the top right part of the Table tab in the Process Petition List file!!

If you cannot enable macros - that is if you do not see four buttons - please see the Appendix – Macros

The latest version of the Process Petition List program can always be found here: **https:%%//%%conventionofstates.com/files/process-petition-list**

You MUST open it in Excel on either a PC or a Mac. If you have another spreadsheet program as well as Excel, you might have to right click the file and “Open with” Excel. The macros will not work if you use Google Sheets, LibreOffice, Numbers, or OpenOffice.

If you get an error in a PINK bar at the top of the screen, as opposed to a yellow bar, that says the file has been “blocked”:

<HTML><ol style=“list-style-type: decimal;”></HTML> <HTML><li></HTML» <HTML><p></HTML>Close the file.<HTML></p></HTML> <HTML></li></HTML> <HTML><li></HTML» <HTML><p></HTML>Find the file in File Explorer. Right click the filename.<HTML></p></HTML> <HTML></li></HTML> <HTML><li></HTML» <HTML><p></HTML>Click Properties<HTML></p></HTML> <HTML></li></HTML> <HTML><li></HTML» <HTML><p></HTML>At the bottom of the dialog, check the “Unblock” box.<HTML></p></HTML> <HTML></li></HTML> <HTML><li></HTML» <HTML><p></HTML>Close the dialog, re-open the file.<HTML></p></HTML> <HTML></li></HTML><HTML></ol></HTML>

The file will open showing the Table tab (tabs are also called sheets or worksheets) at the bottom of the window.

This is easy: open both files, click one button.

1. Open the petitions.csv file exported above. Open it and click “Enable Editing” or “Enable Content” wherever you see it. You do not need to manipulate the file; you only need to open it. Only one petitions.csv file can be open at a time.
2. Open the Process Petition List file. Click “Enable Editing” or “Enable Content” if necessary. Make sure you can see four buttons in the upper right corner of the Tables tab (tabs are also called sheets or worksheets).
3. Click the first of the four buttons, named “Copy and Paste CSV Data”

This step does not involve clicking any buttons. Instead, go to the Cities tab. Tabs (tabs are also called sheets or worksheets) are shown at the bottom of the window:

The Cities tab will show all the unique cities, sorted, from the petitions.csv file in column A. There may or may not be left-over cities in column B from the last time the file was used. Delete any left-overs in column B only.

Inspect the cities in column A for:

<HTML><ul></HTML> <HTML><li></HTML» <HTML><p></HTML>Mis-spellings.<HTML></p></HTML> <HTML></li></HTML> <HTML><li></HTML» <HTML><p></HTML>Capitalization. All cities in Column A must have a capitalized first letter. The Check Table macro checks for this condition.<HTML></p></HTML> <HTML></li></HTML> <HTML><li></HTML» <HTML><p></HTML>Out-of-district cities. Sometimes they are out-of-state!<HTML></p></HTML> <HTML></li></HTML><HTML></ul></HTML>

Drag “bad” cities into column B. They can be anywhere in column B. They will persist in column B, no matter how many times a petitions.csv file is imported, until a user deletes them.

If a “bad” city is a variation (mis-spelling) of a “good” city, make sure a “good” city is in Column A.

The decision about what is “bad” can also be about standardization. For example, in CA AD 30, “Carmel By The Sea” is spelled many ways in AD 30 CB profiles:

Carmel By The Sea

Carmel by the sea

Carmel-by-the-sea

Carmel-By-The-Sea

Note that half have bad capitalization, which will be flagged by the Check Table macro.

It is the user’s choice how to standardize. Put the standardized version in column A and non-standard versions in column B. The goal is a professional look.

Check for errors by clicking the “Check Table” button. The macro checks for the following errors:

1. Cells with missing values will be highlighted.
2. Street addresses that don't start with a number or “PO Box ” (case sensitive, note trailing space) will be highlighted.
   1. Street numbers are allowed to start with N,S,E,W, HC or RR followed by an optional space and the number.
3. Street addresses will be checked for repeated items, and for city, state, and zip (which have their own columns).
4. Possible duplicate rows will be highlighted.
5. Names and Addresses will be checked for invalid characters.
6. Leading double caps, all caps, and all lower case in names, street addresses, will be highlighted. Note exceptions mentioned above.
7. Cities will be checked for all caps, all lower case, and lower case first letter.
8. Cities will be checked against cities in the district (see Cities tab column A for list) and will be highlighted if missing.

Don't be alarmed if you see “Not Responding” at the top of the Excel window. Excel is just working hard.

When an error is found

1. Each cell with an error will be highlighted yellow.
2. The first cell (signed date) in any row having an error will be highlighted yellow.
3. A cell with an error will have a hyperlink added that points to the specific Citizen Builder (CB) profile and the specific tab in that profile where the information can be corrected.
4. A cryptic text string hinting at the kind of error will be placed in column N. A legend for these cryptic text strings is in the Interpretation tab, just to the right of the Cities tab at the bottom of the window.

The “Check Table” macro can take as much as a ten seconds per one thousand rows. It depends on the speed of your machine. Newer machines take much less time.

Keep in mind that human-entered data, which is what all the data in Citizen Builder is, has an infinite variety of possible error. No machine or program can possibly have zero false positives (incorrectly identified as errors) or zero false negatives (errors that should have been caught).

The Process Petition List file reduces the work of finding and fixing errors, but ALWAYS CHECK the entire file for anything unexpected. The most common issue may be other kinds of duplicates that the program missed. Please report any issues you find by submitting a help desk ticket with “Process Petition List” in the subject; we may be able to find a way to catch it.

The next step is to clean the data. When you are in a hurry to meet a deadline, such as an imminent meeting with your legislator, it is possible to skip data cleaning. This postpones the data cleaning process.

The shortest path to a pretty-clean list of petition signers for legislators is to:

1. Open the petitions.csv file
2. Click each of the four buttons on the Table tab in order.
3. Print the result

This short path can be improved by:

1. After clicking the first button, move “bad” cities from column A to column B of the Cities tab.
2. After clicking the second button, look over the result for false positives and false negatives, as you would normally do.

There is one-page “cheat sheet” in the appendix - Appendix – One-Page Instructions

Tradeoffs

The typical percentage of rows with errors in a district which has never been cleaned before is about ten percent of all rows. If they are not cleaned up, if they remain highlighted after the “Check Table” button is clicked, and if we do the cheat, that will mean a reduction of total rows we show legislators by ten percent.

The typical percentage of rows with errors that cannot be cleaned, even after using all the techniques described below, is about one percent.

Thus, the cheat will reduce your list of petition signers by about nine percent compared to fixing data errors at the source.

You will still have the same errors to deal with for later legislator visits, for both upper and lower house legislators.

Bottom line: It is better to fix errors in Citizen Builder and process another petitions.csv file.

It is a good idea to filter by color to show just the rows with errors (yellow highlighting), hiding all the “good” rows. Note that because there will be both false positives and false negatives, there is no substitute for reviewing all rows, even if rapidly.

Right click the little button on the right side of cell A1, “Signed Date”, in the Table tab. Select “Filter by color” and choose bright yellow (should be the only color showing). This concentrates all the questionable rows in a smaller space.

The screen shot to the left is taken just before clicking on the yellow rectangle, which will execute the filter.

Undo the filter on cell color by clicking again on the little button on the right side of cell A1, “Signed Date”, in the Table tab. This time click on “Clear Filter From “Signed Date””, which is just above the Filter by Color option in the screenshot to the left.

Expect approximately 10% of the rows to have some kind of error. It is usually possible to reduce the % of rows with errors to about 1%.

Most errors are fixed by editing their text in the Citizen Builder (CB) profile. Duplicates are more complicated.

The user can go directly to the data source in the CB profile by:

1. Signin to COS using default browser
2. Clicking on a highlighted cell will take you to the CB profile tab that has the problem.
3. For duplicates, clicking on the Last Name cell takes the user to the “duplicate” tab of the CB profile.

Specific Error Fixes

Sometimes good data will be highlighted as bad.

There are many variations of street address standards across the fifty states. In some states, for example, all street addresses start with a pure number (or “PO Box”). In some a compass direction letter is allow to be a leading character. In others any character can be a leading character. The current program allows leading characters N, S, E, W, HC:, or RR before the street number. Most other leading letters cause the address to be marked as bad.

Some street addresses have street names that include some element of city, state, or country. For example, “Ohio 25” or “US 10” or “Springvale Rd” (in a town called Springvale). These examples are good addresses. Sometimes the user has typed the city, state, or zip code on to the end of an otherwise valid street address, which isn’t good. The Process Petition program attempts to properly classify street addresses, but human ingenuity often out-foxes it. If a cell is incorrectly highlighted as bad, see next section; you have a false positive.

There is now an easy way to handle false positives.

Select an entire row inside the table, from the first “Signed Date” column to the “Deceased” column (Columns A through L, 12 columns). Merely selecting it makes three things happen:

1. UN-highlights highlighted cells
2. Removes hyperlinks
3. Adds a row in the False Pos sheet. This is persistent and will prevent the row from being highlighted again by the Check Table macro.

Rows can be re-highlighted if necessary. For this to happen, please select more of the row, from the “Signed Date” column to the column with the error codes (Columns A through N, 14 columns). Merely selecting these cells makes three things happen:

1. RE-highlights the exact same cells as the original
2. Adds back the hyperlinks the cells had originally
3. Removes relevant row from the False Pos sheet. If the Check Table macro is run again, this row will now be highlighted again if it hasn’t been edited.

Multiple rows can be selected for un-highlighting (12 columns) and re-highlighting (14 columns).

When multiple cells in a row are highlighted and one of them is a false positive (good data marked bad), FIRST fix the legitimately incorrect cells in CB by following the hyperlinks in the cells, THEN unhighlight the entire row. When the updated petitions.csv file from the LMS is imported again, the Check Table macro will not mark it as bad.

There is a harder way to unhighlight false positives, just in case.

Select ALL the cells in the row. This will include columns A through L for that row. Then, unhighlight the row by first clicking the paint can on the ribbon, then clicking on “No Fill”.

You can select multiple rows by holding down the Ctrl key while selecting each row. Then unhighlight the selected rows by clicking the paint can and “No Fill”.

The method has the disadvantage of not remembering that it is a false positive; the row is not saved to the False Pos sheet automatically. However, you can manually copy and paste it into the False Pos sheet, which will prevent it from being highlighted again when the Check Table macro is run again. It isn’t strictly necessary to copy the entire row into the False Pos sheet. The only False Pos sheet column needed to prevent re-highlighting by the Check Table macro is the CB ID column.

About one percent of the CB profile errors cannot be fixed. These profiles may not have a first name, last name, or street address, or it may have a really mangled street address.

Submit a help desk ticket (https://help.conventionofstates.com) with the names and CBIDs of the CB profiles you cannot fix. The help desk can ban a profile if it has an email address (to prevent email address re-use), or delete it if it doesn’t. DO NOT mark them as deceased, which will create additional problems. The help desk can merge duplicates you cannot merge, namely where at least one of the profiles has a confirmed account.

While waiting for the ticket to be worked, leave the rows highlighted, and click the “Archive Highlighted Rows” button to send them to the Archive tab. You can do this AND submit a ticket.

After fixing data errors, export a brand-new petitions.csv file that will show your corrections.

1. Run the Copy and Paste CSV Data and “Check Table” macros on the new data.
2. Manually highlight the entire row for any false negatives (real problems not highlighted by the macro).
3. Un-Highlight the entire row for any false positives (marked as a bad but are NOT bad). This can be done manually, or it can be done faster using the method in False Positives (good data highlighted as BAD) section above.

Click the third, “Archive Highlighted Rows” button on the Table tab. All filtering usually has to be turned off to see this button.

The macro behind the “Archive Highlighted Rows” button will remove (cut) all Rows with highlighted cells from the Table tab and paste them into the Archive tab.

Click the fourth and last “Final Print List” button on the Table tab. This formats the Table for printing, and hides the columns for state, districts, and CBID.

If you are printing profiles from several upper or lower legislative districts, it may be necessary to change the Print Title on the printed output. See Appendix – Edit Print Header.

Go to the ribbon, File, then Print. Print the Table directly to a home printer. The report will be many, perhaps even dozens, of pages long. Let’s hope your ink lasts to the end.

This requires making a PDF and saving it to a memory stick.

To do this, you would still go to the ribbon, File, then Print. But this time choose a different printer, a PDF printer. New Windows machines always have a “Microsoft Print to PDF” printer listed. Older machines may have a “CutePDF Writer” or other third-party software. Of course, these aren’t physical printers, but rather software that makes a PDF file from the spreadsheet.

When you click Print, a File Explorer dialog box will open asking you to save the file. Choose a filename and a folder to save it to, then click Save.

One of the benefits of taking rows with errors out of the Table tab so that legislators will see a clean list is that the ARCHIVE tab has all the remaining problem rows.

Outside of the pressures of preparing for a visit with legislators, carefully work the data in the Archive tab. Consider using third party tools like Spokeo to resolve difficult address problems. Consider calling the supporter for clarification. Consult with others that do data cleaning.

As mentioned above, even with extensive data cleaning, some CB profiles, especially their addresses, may be difficult to fix. It is sometimes impossible with the absence of key data.

If you find you do not have permissions to fix some issues (e.g., confirmed account duplicates, banning, deletion), submit a help desk ticket.

Thanks to Rick Raymond for new ideas and direction that made this file much better. Thanks also to Martin Nolan who wrote the code to make the program compatible with Macs. This code also sped up the program by about 3 times. And thanks to JD Pavek for finding many data-related corner cases the program couldn’t handle and for proposing code fixes for them.

Some corporate environments prevent macros from running via “Group Policy”. The only way around this is to request an exception to the Group Policy. Good Luck. We will assume you are not using the Process Petition List file in this environment!

Simple Fix

Usually, all you need to do to enable macros in an Excel xlsm file is to click the “Enable Editing” or “Enable Content” button in the yellow Message Bar at the top of the window, like below:

You may have to click one of these buttons for both the petitions.csv file, and the Process Petition List file. This is safe because we know the origins of both files. Do not click one of these buttons if you have any doubt about the security of the file just opened.

Clicking one of these buttons will allow you to see four buttons at the top of the Table tab, to the right of the data table. If you do not see four buttons, scroll to the right to make sure they are really missing, then try the techniques below.

An alternate dialog box you might see when you export the petitions.csv file looks like this:

SAVE THE FILE, DO NOT OPEN IT. After saving it THEN open it, with macros enabled.

The indication that macros have been enabled is presence of four buttons in the top right part of the Table tab!!

Enable Macros in the Trust Center

If the yellow Message Bar above doesn’t appear, or clicking a button in it doesn’t work, then go to File, Info and “Enable Content” to enable macros:

Trust Center Settings

If this does not work, then we need to make sure that macros are enabled in Trust Center Settings. Click on File, then in the left navigation pane, click “Options.”

Click “Trust Center” in left navigation pane.

Click on “Trust Center Settings”

Above is the “Trust Center Settings” window. Click on “Macro Settings” in the left navigation pane. Your settings should match what is shown above.

If you see an option like this:

“Enable Excel 4.0 macros when VBA macros are enabled” – Uncheck it!!!

Last Resort for Enabling Macros

If nothing above works, try one more option in Trust Center Settings. In the left navigation pane, go to Trusted Locations:

• Add a Trusted Location (folder) by clicking the “Add new location…” button.
• The location should be on your physical computer, not on a network or in the cloud.
• Here is a typical location: C:\Users\[your username]\Documents\COS When you open File Explorer and go to “This PC”, (C:), C:\Users, “your username” will be pretty obvious. It’s often the only username shown.
• Any excel file you place at that location will be trusted, meaning macros will be enabled.

One More Thing

If you use MalwareBytes, you will need to exempt Excel macros from its protection.

If the print header doesn’t match the data, for example if the data is from multiple upper and lower districts, then it will be necessary to edit the header on the print-out.

First click on Page Layout in the ribbon

Then click on Print Titles

This is the Page Setup dialog box. This dialog can also be reached via File, Print, then at the bottom of the Print pane click “Page Setup”

Click on Header/Footer

Then click on Custom Header…

Click in the Center section to edit the text

The program described in this guide is Excel-based, thus using Visual Basic for Applications (VBA), the native macro language in Microsoft Office. The macros in Process_Petition_List will not work in other spreadsheet programs.

Google Sheets

Google Sheets’ macro language is Java. It is a very different language than VBA or any BASIC variant. A complete re-write would be required to convert the macros in Process_Petition_List to work in Google Sheets.

OpenOffice and LibreOffice

These suites use something called CalcBasic as their macro language. They are similar to VBA, not exactly the same. There are tools that aid the conversion from VBA to CalcBasic, but tweaking is required.

Apple Numbers

Numbers can be scripted with AppleScript or JavaScript for automation, but they are not built-in. In any case, their scripting is not compatible with VBA.

Quatro Pro (WordPerfect suite from Corel)

Excel no longer supports importing Quatro Pro files. Only Excel 2003 and earlier versions can import a Quatro Pro file. The Quatro Pro macro language bears no resemblance to either BASIC or Java.

Cleaning profiles is cleaning natural language

This program’s development is never really done, because a human can and will input unexpected text the program has not seen before. For example, we found several supporters with the last name of “True”. Their last name was interpreted as the Boolean value True by the “smart” CSV import Excel process. We found a work-around.

We often find problems with character combinations that, if fixed, would create problems with other character combinations.

Nonetheless, you may find interesting false positives or false negatives we haven’t seen before. Please submit a help desk ticket (https://help.conventionofstates.com) so we can investigate. Attach your petitions.csv file to the help desk ticket.