Go Back
Upsert Excel Data to Salesforce Contacts
Publisher
: Lingk
Run In Lingk
Description
This recipe will upsert one record from an Excel document into a Salesforce Contact object. To use this recipe, upload the file into the Local File connector for your Environment and connect a Salesforce instance.
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - UPSERT EXCEL DATA TO SALESFORCE CONTACTS # Recipe URL - https://app.lingk.io/a/10932/tf/17501 # Description - This recipe will upsert one record from an Excel document into a Salesforce Contact object. # To use this recipe, upload the file into the Local File connector for your Environment and connect a Salesforce instance. # Industry - Higher Ed # Business Process - Graduate Reporting # Systems - Ethos, Salesforce # Connectors - # Data Flows - Single Direction # Connection Type - API to API # Data Models ## Salesforce Objects Used: #1. Contacts # Add Recipe notes / Change log information here! # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # CONNECTORS specify what data will be pulled into the in-memory database during processing connectors: # Configure Salesforce credentials in your Environment before running this recipe # Salesforce Setup - https://help.lingk.io/en/articles/267-salesforce-connector-setup-guide ###### Start - LocalFile Reader connectors ###### - name: sampleFile type: localFileReader format: excelFormat properties: fileName: "rows_66k.xlsx" ###### End - LocalFile Reader connectors ###### ###### Start - Salesforce Reader connectors ###### - name: contactReader type: salesforceReader properties: useBearerToken: true query: SELECT Id, Email from Contact schema: fields: - name: Id type: String - name: Email type: string ###### End - Salesforce Reader connectors ###### ###### Start - Salesforce Writer connectors ###### - name: contactWriter type: salesforceBulkWriter properties: onError: continue useBearerToken: true sfObject: Contact # batchSize: 50 # default 10000 # upsert: true # default false - requires external Id in sfObject ###### End - Salesforce Writer connectors ###### # ______ _ # | ____| | | # | |__ ___ _ __ _ __ ___ __ _| |_ ___ # | __/ _ \| '__| '_ ` _ \ / _` | __/ __| # | | | (_) | | | | | | | | (_| | |_\__ \ # |_| \___/|_| |_| |_| |_|\__,_|\__|___/ # FORMATS specify how files should be processed as they are being read or written to readFormats: - name: excelFormat type: excel properties: worksheets: "data" useHeader: "true" # _____ _ _ _ # / ____| | | | | | # | (___ | |_ __ _| |_ ___ _ __ ___ ___ _ __ | |_ ___ # \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __| # ____) | || (_| | || __/ | | | | | __/ | | | |_\__ \ # |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/ # STATEMENTS specify how the data should be processed while in memory statements: #******************************************************************** D I S C L A I M E R *********************************************************************************************** # * # Note that in an effort to keep recipes optimized for DPH (Data Processing Hours), print statements should be commented out after development has concluded for a recipe. * # For more information on DPH optimization, please visit the following help article - https://help.lingk.io/en/articles/212-minimizing-data-processing-hours-on-the-lingk-platform * # * #******************************************************************** D I S C L A I M E R *********************************************************************************************** # get the first row of data -- remove "limit 1" for testing larger datasets - statement: (data) => SELECT * FROM sampleFile LIMIT 1 #- statement: print data # debugging output - statement: (dataCount) => SELECT count(*) FROM sampleFile #- statement: print dataCount # Do mappings and transformations between Excel columns and Salesforce fields - statement: | (transformed) => SELECT email Email, last_name LastName FROM data #### Start - Update Contact #### - statement: | (transformedToUpdate) => SELECT contactReader.id id, transformed.Email, transformed.LastName FROM transformed INNER JOIN contactReader ON transformed.Email = contactReader.Email - statement: (results, errors) => UPDATE transformedToUpdate INTO contactWriter #### End - Update Contact #### #### Start - Insert Contact #### # The 'LEFT ANTI JOIN' function retrieves the rows from the first table (table after the FROM statement) that do not match with the rows on the second table (right table) - statement: | (transformedToInsert) => SELECT transformed.Email, transformed.LastName FROM transformed LEFT ANTI JOIN contactReader ON transformed.Email = contactReader.Email - statement: (results, errors) => INSERT transformedToInsert INTO contactWriter #### End - Insert Contact #### # Add more statements to convert, join, aggregrate, transform, and integrate your data
Public Location Data Set Browser with Distance Calculation
Convert Nested Array to Table