Partners Support Log In
Frank Hagan


    Back to Top

    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:

    1. Do not double click on the downloaded CSV file to open it in Excel.
    2. Open Excel, click on the "Data" tab, and select "From Text"

      data tab, from text file
    3. 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.

      Excel text import step 1 of 3
    4. In Step 2, select "Comma" for the Delimiter and "{none}" for the Text qualifier

      Excel text import step 2 of 3
    5. In Step 3, select all the fields and select "Text" as the Column data format. This prevents reformatting of the data by Excel

      Excel text import step 3 of 3
    6. The fields in the CSV file should appear in separate columns in Excel.
    7. 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
    Email     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 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


    Articles in this section

    Created - Updated
    Have more questions? Submit a request