Transferring information from one system to another can be tricky. This HOWTO provides information on ensuring data is saved in the correct format, how to run test imports, understanding the options when importing, and reading import status information.
If this process feels overwhelming, please email email@example.com. Our Services Team can also provide custom imports. During your trial, we offer a free data import. If you continue to need help, our Services Team can provide custom data work - handling large scale migrations, cleanup, and deduping. Pricing for custom data work may be subject to an initial scoping and is available upon request.
Table of Contents
- Save data as a CSV
- Clean the CSV file
- Confirm the use of UTF-8 characters
- Take a snapshot
- Begin with a test file
- Understand import options
- Use settings again
- Status of imports
- Related HOWTOs
Save data from Excel to a CSV
If you need to make an changes to your data, open it in Excel. Remember the following:
- Each sheet of a workbook needs to be saved as a separate CSV.
- Replace formulas with exact values.
- Remove colors, flags, comments, or conditional formatting (Note: Commas should be deleted from your CSV, with the exception of the "tag_list" field. A list of tags should be separated by commas).
- Hard returns must be removed from all fields.
Once you complete any formatting changes needed, save the file as a CSV.
Go to File > Save As and change the file type to be saved as Comma separate values (CSV).
Accept the warning that not all Excel features are supported by the current file type.
A CSV is a text-only file with a comma separating each field. Each record will be on its own line. If you open a CSV in Excel, you will need to repeat this process to save the content as a new CSV file.
Clean a CSV file
Saving an Excel file may save hidden characters and other characters the importer won't understand. Here is an easy way to clean a CSV file:
- Open the CSV file in a text editor.
- Select the whole file and copy it.
- Open a new text file.
- Paste the CSV data into the new file.
- Save the new file.
- Import the new CSV file.
Confirm the use of UTF-8 characters
UTF-8 is the largest standard set of characters available. It includes most characters in all languages. By default, most computers save CSV files using UTF-8 characters for plain text encoding.
On a Mac, plain text encoding only needs to be set once. Once the character encoding is set, it will stay set until it is changed.
- Open the CSV in TextEdit.
- Open the text editor preferences: The plain text encoding should be set to UTF-8 for both opening files and for saving files.
- Re-save the file using a new name.
On a Windows computer, plain text encoding must be set for each file.
- Open the CSV file in Notepad.
- Click Save As.
- In the Save As screen, change the preferences for the file so the character set is UTF-8.
- Save the CSV using a new file name.
Take a snapshot
If you already have data in your nation, take a snapshot before starting an import. The snapshot must finish before you begin the import.
Begin with a test file
Doing a test import will help ensure clean imports. This four-step process limits the need to re-import files.
- Create a test file from the original file.
- Import the test file and check for errors.
- Fix the errors.
- Repeat steps 1-3 to check for new errors.
If you have multiple files to import, we recommend completing this process for each file.
Create a test file: select records at random, change names, save as an Excel file and a CSV file
Select test records at random. There is a temptation to take the first records. The first few records are reviewed every time the file is opened. If there were obvious errors, they would have been fixed. Records from the middle of the file tend to be a better indication of the state of the data.
After copying the random data, replace real names with obviously fake ones. Creating fake names decreases the chances that existing records will be updated instead of adding new records. Additionally, this makes it easier to see if the records have been deleted after the test import.
Save the test information as both an Excel file and as a CSV file.
Import test file and check for errors
Import the file using the correct importer based on the type of information included in the file. Check for errors.
If errors are found, you'll be able to download a CSV. Download the error CSV file and open it in Excel. The file will only contain the first 2 megabytes of errors. The last field in each record will contain the error logged.
Common errors seen include:
Email address does not look like an email address. Indicates that either the wrong field was mapped to the email field or the email address was malformed. Common malformations include spaces or special characters in the email address, email addresses without a period or an @ sign, and email addresses without the final extension.
Malformed CSV - Illegal quoting. Indicates that the record has unexpectedly ended during import. Common causes are records containing hard returns (line breaks) and/or quoted content. In both of these cases, the file should be checked for fields that don't show where you expect them. For example, a single record that continues across more than one row.
Twitter is not a number. Indicates that the Twitter ID contains letters or special characters. This could be a field mapping problem - perhaps the Twitter ID field and the Twitter login field were swapped.
Fax number is too long (maximum is 30 characters). Indicates either a field mapping problem or bad data.
Fix the errors
Use the errors from the test file import as a roadmap to find and fix errors in the real file. Save a new copy of the file.
Import a new test file
First, delete records imported during the initial test import. Then repeat the above process and import a new random set of test records.
If no errors are logged in the second test import, delete the test records from the nation and begin importing the full file. Otherwise, continue fixing errors and repeating the process until no errors are logged.
Remember to delete all test data from the nation before doing an import of the complete data set.
Understand import options
Step 1: Mapping fields
Mapping fields means telling your nation what type of data can be found in each field. You must map to fields available for import. All fields cannot be imported at all times. There are seven importers available and a complete list of fields available for import.
In the above example, I mapped fields, but missed a step. Every time fields are mapped, the system will validate the first row of data to confirm it looks correct. Since the first row in this example is a header row, a validation error is displayed. I will be unable to import the file until this error is fixed.
Checking the box next to "don't import" will tell the system the first row contains header information and the second row of data will be validated. After checking the box, the following displays:
Step 2: Customize your import
The second step is defining global settings for the import. Seven options appear on one-time imports. Fewer choices are included in other imports.
1. Decide whether to overwrite existing signup data. The unique ID imported with a record could connect it to an existing profile in your nation. This checkbox is part of the process of determining whether existing data is overwritten.
2. Support status: Profiles always have a support status, which is supporter, non-supporter, or prospect.
If the records being imported are people who have opted into communication, select supporters / non-supporters. Any new profile created with an email address, mobile number, Facebook username/ID, or Twitter login/ID will be labeled a supporter. Other profiles will be created as non-supporters.
If the records being imported are people who have not opted into communication, select prospects. When prospects is selected, all profiles created will be labeled prospects, including records with email address, mobile number, Facebook username/ID, or Twitter login/ID.
In certain circumstances, information being imported may over-ride the above rules. Please see how support status is determined for more information.
3. Activity stream: Decide whether to show that each profile was added in the activity stream in the Dashboard section and on the person's profile. Sometimes, this can be useful. Other times, it is just clutter.
4. Add tags to each person: If a tag or tags should be added to every record imported, include it in this text box. Tags can have spaces. Including a comma separates one tag from another tag. Tags included in this text box will be appended to the tag list for each person added / updated by the import.
5. Add everyone to a list: This dropdown menu allows you to add every profile added / updated by the import to a list. The list must exist before it can be selected from this dropdown menu.
6. Bypass webhooks: If you check this box, no signal will be sent to the API regarding the import. Checking the box will prevent external apps / API-based integrations from updating based on the import.
7. Start import button: Until fields are mapped, you will not be able to press this button. Fields must be mapped and a unique identifier must exist before the file can be imported.
Once you click the Start import button, the file is loaded into the import queue. A nation can import one file at a time.
Use settings again
Once an import starts, its field mapping is saved and can be re-used. When you upload a new file, check the box next to "Use settings from a previous import." A dropdown box will appear.
In the dropdown box, select a previously imported file.
Once a file is selected, the fields will be mapped. This assumes that the file being imported has the exact same layout as the previous import. You can adjust any discrepancies in field mapping between the current and previous imports.
The options selected in the "Customize your import" section will also be filled in based on the previous import.
Reusing settings is available for one-time, voter, vote history, donation, and membership imports. Each type of import saves settings from previous imports. The mapping cannot be shared between different types of import, e.g. you cannot use a previous donation import's settings in a membership import.
Status of imports
After clicking the Start import button, a file is added to the import queue. The status displayed with that file will be "Queued for importing, but not started yet. Refresh to check."
Once an import starts, the status will change to "importing" or "working." Information available includes:
- Completion percentage for an in-progress import.
- Number of records processed.
- Total number of records to process.
- Estimated time until the import finishes.
If the estimated time to complete the import seems unusually long, email firstname.lastname@example.org. The file may be unusually large and we may be able to help speed up the process.
Once an import finishes, the status will look like this:
Source: Click on the name and a copy of the imported CSV will download to your computer.
Size: The size of the imported file.
Added: Number of records added by the import. Clicking on the number takes you to a filter of the records created by the import.
Updated: Number of records updated by the import.
Errors: Number of errors detected in the import. The CSV error file will only contain the first 2 megabytes of errors. The last field in each record will contain the error logged.
Time spent: Amount of time spent importing the file.
Status: When the import finished in relative time, or the current status of a queued / working import.
Imported addresses can take 2-6 hours to geocode. This aspect of the import status can be reviewed in Settings > Database > Geocoding services.