Company Blog Support
Frank Hagan
posted

Contents

    Back to Top

    Importing Contacts and Import Failures - Cleaning Up Your CSV File

    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 can happen with some programs such as Excel that automatically change the data if you double click it to open it without "importing" 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:

    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 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

    ONTRAPORT / Answers is our peer-to-peer support site where you can get answers from our community of entrepreneurs, support staff and Certified Consultants. Some Answers provided on importing contacts:

    ONTRAPORT / Projects are step-by-step in-app instructions with a checklist to track your progress. You can stop and resume the Project at any time. Log into ONTRAPORT in another tab and click Importing Contacts to start the Project.

    Have more questions? Submit a request

    Comments

    Powered by Zendesk