search
Partners Support Log In
Rochelle Yoshida
Updated

Contents

    Back to Top

    Best Practices for Preparing a CSV File for Import

    Best Practices for Preparing Your CSV File for Import

    Applies to: All ONTRAPORT accounts.

    This article covers the best recommended practices for preparing your CSV file for import including:

    • Opening your CSV files the right way
    • Removing extraneous data
    • Understandable column names
    • Separating names into columns
    • Separating addresses into columns
    • Formatting dates as MM-DD-YYYY
    • Separating multiple phone numbers into columns
    • Choosing one primary email address per contact record

    Open CSV Files the Right Way

    Do NOT double-click the CSV file to open it in Microsoft Excel. Excel may strip leading zeros off postal codes, change longer phone numbers to scientific notation, and other unwanted formatting.

    Instead, follow these instructions for Excel or Google spreadsheets.

    Excel

    1. Create a new spreadsheet.
    2. Click the Data tab and select "From Text" in the "Get External Data" section.
    3. Select your downloaded CSV file to import it.
    4. The Text Import wizard opens; select "Delimited" in step 1
    5. Choose only the comma character in step 2.
    6. In step 3, select all columns and set the column type to "Text" and click "Finish". This preserves the formatting of zip or postal codes, phone numbers, etc.

    Google spreadsheets

    1. Create a new spreadsheet
    2. Click the File dropdown and select import
    3. Select your downloaded CSV file to import it.
    4. The Text Import wizard opens; select Replace spreadsheet
    5. The file is imported into your new Google spreadsheet

    Remove Extraneous Data

    ONTRAPORT will not import graphs, images, charts, comments, formulas, notes or text art. Remove any data that you do not wish to import including graphs, charts, notes and any other extraneous data.

    Create Understandable Column Names

    For a smoother import experience, add clear and understandable column names so that you recognize what data each column contains. For example, if you have a first name column, don't call it name because you won't know if it's the first name or the last name column.

    Tip: If you match the columns up exactly as they are listed in ONTRAPORT, our system will automatically match them up during the import process. For example, if you name the first name column First Name, ONTRAPORT will recognize it upon import, thereby shortening your import process time when mapping fields.

    Here are the default field names 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

    Separate Names into Columns

    ONTRAPORT has default fields for first name and last name. Your CSV needs to have separate columns for first name and last name. If your file has one column for name and you need to split it into two columns, see this article.

    Separate Addresses into Columns

    ONTRAPORT has default fields for street address, street address 2, city, state, zip (postal codes) and country. Ensure your CSV file has separate columns for each field. If your file has one column for all address fields, split it into columns. See this article for a how-to.

    States: United States and Canadian provinces need to be formatted in standard two letter abbreviations. For Australian states, use the three letter abbreviations. If the state does not exist in our dropdown menu and your imported data has something other than US, Canadian or Australian states, the state field will populate with your imported data.

    Countries: Use standardized two letter abbreviations as noted here.

    To learn more about formatting States and Country fields, see this article.

    Format dates as MM-DD-YYYY

    ONTRAPORT accepts dates in the US "MM/DD/YYYY" or "MM-DD-YYYY" format only. By default, ONTRAPORT has birthday as a default field. To create other date fields, use the Field Editor or create a new field during the import process.

    To learn how to convert dates in European / Australian format of "DD/MM/YYYY" for importing, see this article.

    Separate Phone Numbers

    ONTRAPORT has three default phone number fields already created in your account: Office Phone, Fax and SMS Number. Separate phone numbers into appropriate columns in your CSV file. To create other phone number fields such as home phone, use our Field Editor.

    When importing phone numbers, they are imported exactly as they appear in your import file. If you'd like them to appear in a specific format, we recommend formatting them in a spreadsheet application prior to import.

    Note: SMS numbers are a special field in ONTRAPORT. You can import the number, but cannot send to the SMS number without specific permission. Your contacts will have to fill in a form with that number, or text your ONTRAPORT supplied SMS number to initiate SMS usage.

    Choose One Primary Email Address

    ONTRAPORT supports one email address per contact. The default email address field is a key identifier that merges contact records together if an email address already exists. Choose one primary email address for each contact you'd like to import.

    While you can create additional custom fields to house alternate email addresses, you cannot send an email to those custom fields. If you need to be able to send to alternate email addresses, you can use a custom object if you're comfortable with this advanced feature.

    Articles in this section

    Created - Updated
    Have more questions? Submit a request

    Comments