Format data for import

Here are some best practices to consider when formatting your data for import to a new List or an existing List in SalesforceIQ.

Note: The import tool includes a downloadable sample CSV template to help you format your data correctly.

Data Required for Import

When preparing your data for import, keep in mind that certain fields are required, as follows:

  • For an Account-based List, you must have at least one data field that you can import as the Account Name field.
  • For a Contact-based List, you must have at least one data field that you can import as the Contact Name field.
  • Be sure to include an email address for any Contacts you import.

File Size and Format

Currently, you can import CSV files containing up to 50,000 rows.

Data you want to import must be in CSV format. Sometimes, when downloading to CSV you may run into some issues. For example, if any cells in your data file start with the = symbol, you may get a #NAME? in those cells. To fix this, remove the = symbol from the cells displaying this error.

Also note the following:

  • Make sure your CSV files contains no empty column headers.
  • Make sure the first row in your CSV contains your field names.

Multiple Contacts in an Account List

You can import multiple Contacts per Account List object (lead, opportunity, etc., shown here as Lead in the first column), but you must format them in a specific way. The name of the company must be duplicated for each List object, and the objects must appear one after the other, as shown here:


In this example, certain columns (Primary Contact, Primary Contact Email, Phone) contain Contact-specific data, while others (Lead, Company Location) contain Account-specific data. The import tool uses the first row of the CSV file to create the company Account.

The import tool accepts the following Contact-specific data only:

  • Name
  • Email
  • Phone(s)
  • Title
  • Company
  • Twitter
  • Address

Skype or Facebook accounts associated with individual Contacts aren't imported. If you can't lose that data, we recommend that you create a Contact list.

If your spreadsheet contains multiple contacts, but they are listed in columns as primary contact, secondary contact, etc., as shown here:


then you must move each contact to its own row. In this example, you must move Darius below Richard to a separate row. If your spreadsheet contains a large number of Contacts in this format, you can reformat them quickly by doing the following:

  1. Copy and paste the first column next to the column for the secondary Contact.
  2. Copy and paste all of the secondary Contact information, including the new first column, below the primary Contact row. Make sure to move the pasted data back to initial column.
  3. Select all cells, and then click Data > Filter.
  4. Sort by the name of the List object. This leaves the primary Contact on top and the secondary, tertiary, etc. Contacts below.

Multiple Phone Numbers or Emails Per Contact

If you have multiple phone numbers or emails for a Contact, they must be in the same row as the Contact—either in a different column or in the same column, but comma-separated. The following images show acceptable formats:



Date Formatting

The dates in your data may be in a format other than 12/24/2010. To change this:

  1. Select all cells.
  2. Right-click and select Format Cells.
  3. On the Number tab, click Date.
  4. Under Locale, make sure English (U.S.) is selected, and then select the date format as shown above.

Sometimes the date format includes the time as well: 12/24/2010 13:45:26. You can import this format into SalesforceIQ; however, only the date appears, without the time. If you want the time to appear in SalesforceIQ also, you must insert a space between the date and the time to create separate columns. You can then format each column by right-clicking it and selecting Format Cells.

If the date cells contain other information such as words, symbols, etc., this information doesn't appear in SalesforceIQ.

Phone Numbers in Scientific Notation

Phone numbers sometimes show up in scientific notation. If you want every digit displayed, do the following:

  1. Select all cells.
  2. Right-click and select Format Cells.
  3. On the Number tab, click Custom.
  4. Under Type, select 000.

This changes the number format to show every digit.

Multiple List Items

If you want to populate a field with multiple options, enter each option separated by a comma in a cell under the field name column. In this example, Office Location is the field name and APAC, EMEA, and US are the options.


During the import process, if you import the field as a multi select field, each option appears as a list option.

Deals and Accounts

If you would like to set the List object as a deal name and the Account as something different, simply set them up as two separate columns. During the import process, on the Import Account Properties screen, drag the Account column here:

Then, on the Import Fields to a New/Existing List screen, drag the Deal column into the object name field as shown here:

Address for Contact or Account Properties

If you would like to associate an address with a Contact or company, you must ensure that the entire address is in a single cell. Excel offers two formulas for combining cells. Using the following address as an example:


To combine it all, you can enter the following formula in cell G2:

=B2&" "&C2&" "&D2&" "&E2&" "&F2

or

=CONCATENATE(B2," “,C2," “,D2," “,E2," “,F2).

Note: If the address contains extra spaces before, in the middle, or after, you can use the following formula:

=TRIM(G2).

Contacts and Accounts in Separate Lists

If you have Contacts and Accounts in a separate List, you have to use a vlookup to add the contents to the same List. If you have an Account ID in both Lists, use that as the identifier. If not, you need to use the name of the List object.

The vlookup formula is as follows:

=VLOOKUP([{cell name}{contact ID or name you want to look up}], [range of data you're looking up, with the first column being the column containing the contact ID or name you're looking up and the last column being the data you're looking up],[number of columns until the column with data you're looking up],[the word “false"])

An example might look like this:

=VLOOKUP(B2,E$2:F$999,2,false)

If you're having any difficulties, many helpful videos are available on YouTube.

In any case, the strategy is to get the List objects that correspond to the contacts on the contacts spreadsheet (on the same row as each corresponding contact). Then you copy all the pertinent contact data with the List object data back into the Account sheet (below all the other data). Take care to move the List object data to the same column as the other List object data and then filter by List object to move the Contacts right under the row of data that corresponds to the right List object.

Special Characters

SalesforceIQ supports importing special characters that contain accents, such as é, ü, or ñ. The steps for importing special characters depend on whether you are exporting from Excel or Numbers.

Excel

The Excel CSV file type doesn’t support special characters. Follow these steps to import special characters from Excel.

  1. Make sure your Excel file is formatted correctly, and save it as a .txt file.
  2. Open the text file in TextEdit (Mac) or Notepad (Windows).
  3. The file is tab-delimited. You need to change it to comma-separated. To do this:
    • Do a find and replace for all the tabs in the document.
    • Select a space between and two values and copy it (Ctrl+C).
    • Open the find and replace window.
    • Paste the tab in the Find field. Enter a comma in the Replace field.
    • Click Replace All. Your text file should now look like a string of comma-separated values.
  4. From the File menu, select Save As.
  5. For the plain text encoding, select Unicode (UTF-8).
  6. Open the folder where you saved the file and manually change the extension from .txt to .csv.
  7. Import the newly created CSV file.

Numbers

  1. Open your spreadsheet in Numbers.
  2. Remove any extra columns or rows. There should be no empty columns or rows visible beyond your data, as shown here.
  3. Go to File > Export to > CSV.
  4. Click Advanced Options and select Unicode (UTF - 8) from the Text Encoding drop-down menu.
  5. Save your CSV file as usual and import.

Before Merging Lists

The new import tool does not check for duplicate List objects in a List merge. If you don't want to import a duplicate List object , you must do a vlookup to see which (if any) objects already exist in the original List.

Mixed Data in Columns

If you have a column that has mixed data and you are trying to find a way to separate them, a useful function combo is IF + ISNUMBER.

For example: If you have a column with emails and names (column A), you can paste the following formula in column B:

=IF(ISNUMBER(FIND("@",A2)),"true","false")

This gives you a “true" response if it finds the “@" symbol (which any email has), and a “false" response if it doesn't. You can then apply filters and sort by column B so that you have all the cells containing emails in consecutive order.

1 2 3 4 5