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
- Create a new spreadsheet.
- Click the Data tab and select "From Text" in the "Get External Data" section.
- Select your downloaded CSV file to import it.
- The Text Import wizard opens; select "Delimited" in step 1
- Choose only the comma character in step 2.
- 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
- Create a new spreadsheet
- Click the File dropdown and select import
- Select your downloaded CSV file to import it.
- The Text Import wizard opens; select Replace spreadsheet
- 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 |
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
- 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