Table of Contents
- Save data from Excel as a CSV
- Clean the CSV file
- Confirm the use of UTF-8 characters
- Take a snapshot
- Begin with a test file
Save data from Excel as 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.