Importing Contacts and Import Failures - Cleaning Up Your CSV File
Applies to: All ONTRAPORT accounts.
Import Failures: How to Clean Up Your Import CSV
In most cases the CSV file from your prior service will import fine using the regular import process. If the preview of the CSV file shows that some data is not presented correctly you may need to reformat your CSV file. This video includes a section on cleaning up (or formatting) your CSV file for import at the beginning; more detail is given below.
Errors can happen if Excel automatically changes the data when you double click it to open it. For example, Excel will strip leading zeros off postal or zip codes and account numbers, reformat long phone numbers into scientific notation, etc. Because Excel auto-saves the new format this is irreversible, requiring you to export your contacts again. We recommend the following steps to preserve the CSV format when using Excel or similar spreadsheet programs:
- Do not double click on the downloaded CSV file to open it in Excel.
- Open Excel, click on the "Data" tab, and select "From Text"
- The Text Import Wizard will open. Select "Delimited" in Step 1. Note: If you have accented characters, select UTF-8 from the File Origin box. Leave it at the default if you do not have accented characters.
- In Step 2, select "Comma" for the Delimiter and "{none}" for the Text qualifier
- In Step 3, select all the fields and select "Text" as the Column data format. This prevents reformatting of the data by Excel
- The fields in the CSV file should appear in separate columns in Excel.
- After cleaning up the data remember to save the file as a CSV (or "Windows CSV" if given that choice) before importing into ONTRAPORT.
Cleaning Up your CSV File
- Remove empty columns and rows
- Avoid importing information you don't really need
- Naming the columns to match the default field names in ONTRAPORT can save time during the import process (see Recommend Fields for Your First Import below)
Recommended Fields for Your First Import
These are the basic, default fields for the Contacts collection in ONTRAPORT.
| First Name | Zip Code |
| Last Name | Country |
| Fax | |
| Title | SMS Number |
| Company | Office Phone |
| Address | Birthday |
| Address 2 | Website |
| City | Referring Page |
| State | Contact Tags |
If your service provided just a single "Name" column with both first and last names you can separate the names into separate columns using the "Text to Columns" feature in your spreadsheet (see this Office.com article for information on doing this in Excel).
ONTRAPORT also allows importing certain system fields that are usually populated by actions your Contacts take. These fields can be populated, but it can lead to confusion later as the underlying data for them is not stored in the database. Imagine seeing credit card information in a contact record but not being able to process a manual transaction. We recommend leaving these blank.
| Sequences | Last CC Status |
| Last Invoice # | Card Expiration Year |
| Total $ Unpaid Transactions | Card Expiration Date |
| Last Total Invoice Amount | Last Referrer |
| Last Charge Amount | First Referrer |
| Card Type | Partner Program |
| Card Number (Last 4) | Paypal Address |
| Card Expiration Month | Number of Sales |
| $ Sales | Spent |
Remember to save the file as a CSV file (or "Windows CSV" if given that choice) before importing into ONTRAPORT.
If you want to save a copy of the CSV file remember to archive it on your local computer. ONTRAPORT will store the file for 60 days and then delete it.
Other Resources
- Importing Contacts: Using EU Format CSV
- Importing Contacts: Splitting and Combining Field Values
- Importing Contacts: Capitalizing Proper Names
Articles in this section
- Contact Management Overview
- Importing Contacts
- Best Practices for Preparing a CSV File for Import
- Contact Management Segmentation (Groups)
- Contact Tags
- Add Custom Fields & Sections
- Card View for Contacts and Custom Objects
- Card View - Using the Color Field
- Converting to Card View
- Card View Quick Reference
Comments