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

Note: This function requires a Connect on-premise agent.

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
Note: This is the file that the CSVLOOKUPVALUE function calls.

To create the association between the Lead and the Salesforce account owner (that is, the Salesforce user):

  1. 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.
  2. 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:

Related Topics

Lookup Functions

LOOKUPTARGETVALUE