The need to change the values actually being imported is inevitable when you are importing data into Salesforce. Scary to some, a matter of fact to most , but a way to shine for the users of Informatica . At Perficient we have been able to use the cloud tool Informatica which offers a wide range of means for transforming the value of a field being imported into Salesforce. Two that I will cover in this post are an in-line change via a formula and a complete remapping to new data based on tables contained in flat file stored on the machine running the task.
In-line Formula
Much of the required transformations can be completed simply through the formulae provided to you on the “Field Mapping” step of the Task Wizard. Any person familiar with Excel formulae will immediately recognize these and understand how to use them. A couple common tasks where you might need this is to take a 18 digit field and trim it to 15 digits. The last three digits are a check-sum a differ based on the method used to retrieve records. Trimming to 15 digits makes the matching for look-up fields more reliable. To do this a SUBSTR() formula is provided.
Another common use is data cleansing, testing for bad data and replacing it with defaulted values. An example would be on the Account object field AccountSource. This field may not have been populated in the source data, in this case we want to swap out the null values with “Unknown”.
In-line formula editor for Informatica
Flat File Transformations
Though an in-line formula has the ability to do many of the tasks required it is limited to only knowing the record being imported. Let’s say you are migrating Activity records. The WhoIs and WhatIs fields are look-up’s but cannot be mapped with the standard relationship functionality because the referenced object is not a single object and could be one of many possible objects.
The first step is to create a CSV file with at least two columns. Remember that this file needs to be stored on the same machine as the Informatica Agent. The first column is the Id field from the source object and the second is the matching Id in the target object. Yes, this means that you will have to do some work matching, but if you used an external id field to capture the source Id in the target object then this will have already been done for you.
Sample CSV
Secondly we will need to teach Informatica where this file is and how to read it. Again this is done on the “Field Mapping” step of the wizard. In the Target mapping, the second icon after the target field name is for this look-up and clicking it will open the following window.
Informatica Look-Up Dialog Window
In the Lookup section you will set the connection and file to use. The connection should have already been set up and will point to the folder on the local machine where the files are stored. The look up fields give you the ability to map field from the source object a CSV column. Informatica will perform a search on the Id column for matching values, and then return the value from the TargetWhatId column.
Data Transformed
You can turn to Informatica for changing values while importing data into Salesforce from simply trimming data to substituting values from completely different data sources. All your tasks are easily shared and managed since the mapping and transformations are managed through a cloud based interface.
