Call Us Now at 888-391-4493 x103

Our Salesforce Best Practice of the Week: Tips for Importing Salesforce Data

If you’re like most customers, you already have existing data when you become a salesforce.com customer. A key task in getting Salesforce CRM up and running is getting your data from its current location into Salesforce CRM.

tips for Importing Salesforce Data, Salesforce Support, Salesforce Partner, StarrForce

The following tips for importing Salesforce data are best practices that we use to help our customers import their Salesforce data while keeping their records clean and avoiding duplicates.

CSV files cannot have any commas.

  • Do a “Find & Replace” to find commas and replace with nothing before importing or your data will end up in the wrong field.
  • CSV stands for Comma Separated Values so if a comma is in a cell, then the import will move everything after the comma to the next cell. Each comma shifts the data by one cell.

Remove all duplicates before importing data.

  • It is easier to remove duplicates from a spreadsheet than merging them in Salesforce.
  • The Import Wizards will attempt to merge duplicate accounts, contacts and leads but you will stand a better chance of a clean import if you clean them yourself
  • The Data Loader, or Excel Connector, will not attempt to merge any duplicates.


Are you looking for easy Salesforce Tips? Download our list of more than 50 Salesforce Best Practices!

Compare data already in Salesforce before importing more.

  • Use the Excel Connector or Apex Data Loader and V-lookup to compare new records against existing records before importing. If you have under 10,000 records of the type you want to import, export matching information (like email, phone, company, name, etc. along with the Salesforce record ID. Then do a V-lookup from your import sheet to your exports to see if any return an ID as a match.
  • Consider using a third party app from the AppExchange if you regularly import records and you have too many for the above to be practical. Apps like RingLead or Dupe Blocker will check your list against your database before import and flag duplicates.

Do a small import first

  • Import 5-10 records first to make sure they uploaded as expected before importing all records.
  • Be sure to check that zip codes and phone numbers are formatted correctly and that the data is in the right fields
  • If they all look good, then you can do the main import – Be sure that the first imported records are removed from your main list before importing them or you may end up with duplicates.

CSV files do not save formatting

  • After formatting columns for Number, Date, or Zip Code, save the CSV but keep it open when importing or formatting (like leading zeros in zip codes) will be lost – Use Excel’s special formatting for zip codes and dates.
  • Date formatting is different for the import wizards and the Data Loader – For the import wizards, see article 97633 in Salesforce help. For the Data Loader, see articles 101977, 344999 and 105697 in Salesforce help. The Data Loader articles also cover time zone differences that you should be aware of.

Search before creating records manually.

  • A quick search will tell you if the contact or account already exists before creating it.

Use validation rules to maintain your data structure.

  • By using validation rules, you can enforce data formatting such as two letter states or three letter country codes.
  • Be sure to test your rules against all scenarios – If you create a rule that says a pick list cannot be changed, what happens if there are blank values in existing records? Be sure to let your users know when you create new rules so they know what to expect when you activate them.
  • Salesforce has a list of sample validation rules that you can use as a start or to get ideas from – Search for “Examples of Validation Rules” in Salesforce help for the help topic of the same name.

Use “Required Fields” where needed, but don’t over do it.

  • On certain objects, required fields may be necessary, such as Phone or Status fields but if you make every field required, records may not be created or false data may be entered.

Use reports to check data integrity on a regular basis.

  • Create reports for your different objects and include the fields that are most important to you. Sort by those columns to do a spot check on their utilization.
  • For Enterprise and above, you can use free apps from the AppExchange to tell you how complete records are or how much a field is used – Field Trip and % Complete are both free and will give you.


StarrData is a Salesforce Partner that helps companies deploy, simplify and support Salesforce.com. If you would like information on the services we offer call us at (888) 391-4493 Ext. 103.

 

Posted by Darren

Darren

Posted: July 25, 2012

view all posts


6 Comment

  • Stefaan Somers
    February 22, 2013 at 4:50 am

    I succeeded to create a correct CSV-file, but I need to do a lookup in Excel to another sheet to fill in some additional fields. Problem when saving as CSV file in Excel, is that the double quotes are removed. Any idea on how to solve this??

  • admin
    February 25, 2013 at 6:32 am

    Hi Stefaan,

    There are many options (like saving to Access and then from Access to CSV, Macros, and third party programs) available depending on your version of Office programs and desired results.

    If you do an internet search for “double quotes removed when saving excel file to CSV”, you will find solutions using the above and more.

    Hope this helps.

  • Jamie
    April 1, 2013 at 6:41 am

    An application call SimpleImport which is available for free on the AppExchange is great for importing files into any Standard object. It has many features such as automated field mappings and also requires no software downloads.

    It’s created by a company called Provenworks: http://www.provenworks.com

    The AppExchange link is: https://appexchange.salesforce.com/results?keywords=simpleimport

  • Brandon
    June 5, 2013 at 1:50 pm

    Bad idea to use VLOOKUP against the Salesforce ID. Salesforce IDs are case-specific and VLOOKUP is not case sensitive. One can see this by doing a pivot table analysis & grouping by Salesforce IDs in a large data set, particularly one that was created by the Data Loader or mass import.

    You’ll need a unique auto-number field to use Excel OR use some ridiculous conversion workaround in Excel to get quality matches.

    Be careful assuming Salesforce IDs “play well” in Excel. They don’t and can trip you up at the worst possible times.

  • George
    June 6, 2013 at 4:37 pm

    Hi Brandon,

    Thank you for bringing this up. There are times where the Salesforce ID does not play well with other programs like Excel or Access. This applies when exporting reports from Salesforce as the record ID is always 15 characters and is Case Sensitive.

    When using the Excel Connector (link below) in Professional edition (or Enterprise if desired) or the Data Loader in Professional Edition with API access or higher editions, the queries of IDs are always in the 18 character Case Insensitive format.

    http://code.google.com/p/excel-connector/

    With the 18 character Case Insensitive format, VLOOKUP works just fine but it will not work on the 15 character IDs in Salesforce reports. If you need to use reports and gather related IDs, there is an option that still allows you to export 18 character Case Insensitive IDs:

    Create a new custom formula field on the object (like Leads, Accounts, Contacts, etc.) and give it a name like “Lead 18 Digit ID”. Instead of calling each field “18 Digit ID”, preface it with the object name so you can tell the difference when running related reports, like Opportunities and Accounts.

    Set the formula field as a Text field and insert the Function of “CASESAFEID(id)” without the quotes. In Professional Edition, the field needs to be on the page layout but in higher editions, the field can be hidden. Then run your report with your new field and export it. You can now use reports just like the queries from the tools above without having to deal with Case constraints.

    Hope this helps.

  • Erica
    June 27, 2013 at 6:57 am

    Hey Brandon,

    The following formula is similar to a VLOOKUP, but it case sensitive.

    =INDEX(B:B,MATCH(TRUE,EXACT(A:A,C2),0))

    =INDEX(RETURN VALUE,MATCH(TRUE,EXACT(MATCH FROM,MATCH),0))

    * This is an array formula, it needs to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

    It’s a quick way to compare ID’s on a small dataset.

    -E

Comment Form

Your email address will not be published.




Scroll to Top
Click here to download our FREE Salesforce Best Practices Guide!