CSVLOOKUPVALUE
Syntax
CSVLOOKUPVALUE (filename, lookup_column, field_value, return_column, isfirstrowheader, maxfilesize)
Description
Use a value from a CSV (comma-separated value) file as a lookup field.
Arguments
| filename | The name of the CSV file to search. |
| lookup_column | The column to use as the look up to determine the correct row. |
| field_value | The value to look up. |
| return_column | The column from which to get the resulting value from the correct row. |
| isfirstrowheader | Specify: - TRUE if the first row has a header - FALSE if the first row does not have a header If there is no header, names default to Column1, Column2, etc. |
| maxfilesize | The maximum file size (in MB) to cache in memory when performing lookups. The maxfilesize setting must be larger than the size of the lookup file. Having a cache size that is smaller than the lookup file may cause data corruption. |
Returns
The value in return_column in the first row found where lookup_column contains the specified field_value. If no matching row is found, the function returns NULL.
Remarks
The CSV file you use for the lookup must reside in the ..\Scribe Software\TIBCO Scribe® Online Agent n\DataExchange\ folder.
You can edit the file in Excel, but you must save it in CSV (comma delimited) format.
The CSV file should have no more than 10 columns.
Example
Assume that I have a source file, as shown below, containing Leads that I want to move to a Salesforce target. In addition, I want to make sure that each Lead is associated with the Salesforce user assigned to manage that lead.
| LeadName | Company | OwnerName |
|---|---|---|
| Joan Darcy | Manchester Monarchs | Nathan Detroit |
| Hadrian S. Wall | Aaron Fitz Electrical | Judy Chicago |
In addition, I have a CSV (comma-separated value) file, called SFDCOwner.csv, that maps the name of the Salesforce Account owner with the Owners' ID for the Account, as follows:
| Owner | Owner_Key |
|---|---|
| Nathan Detroit | 005E0000000fQDiIAM |
| Judy Chicago | 005E0000000fQDnIAM |
| Sue Salem | 005E0000000fQRLIA2 |
To create the association between the Lead and the Salesforce account owner (that is, the Salesforce user):
- Create a flow with an Insert or Create Block in TIBCO Cloud™ Integration - Connect for which:
- In the source, fields include the name of the Lead, their Company, and Name of the Salesforce user (Owner) to which I want to assign each Lead. The name of the source entity is LeadImport.
- The target is the Lead entity in a Salesforce Connection, which includes the OwnerID field.
- In the Salesforce target, use the following formula on the Salesforce Lead OwnerId field:
CSVLOOKUPVALUE( "SFDCOwner.csv", "Owner", LeadImport.OwnerName, "Owner_Key", TRUE, 400 )
This flow creates a Lead in Salesforce for each row of source data and assigns it to the correct Salesforce user based on the user's Salesforce Owner_Key ID.
Using the CSVLOOKUPVALUE () function to determine the value of the Salesforce Users' ID (the Owner_Key field) can be much faster and use fewer API hits than using the LOOKUPTARGETVALUE to assign the correct Salesforce user.
This formula assumes that the SFDCOwner.csv file contains:
- Column names in the first row
- A column named Owner, which acts as a foreign key to my Dynamics CRM System User
- A column named Owner_Key, which is the primary key in the Dynamics CRM System User entity, needed to assign the Lead