Import File to Data Extension

NOTE This task requires assistance from your eComm Specialist.

Learn how to create a Data Extension as you typically would, then Import over the data from a file. Just remember, you'll need the Contact 18 ID to Import successfully.

Call for Help Importing a file over a Data Extension is simple in theory but more difficult when put into practice the first time. There are some small intricacies that will vary, depending on your use case. Feel free to arrange 30 min. with Melanie Jones to walk through it together.

1 | Create Data Extension

The goal is to create a Data Extension as usual. Access the Marketing Cloud Business Unit that the message will be sent from and configure the Data Extension. There is no need to 'Start' the Data Extension once created.

Required Fields:

  • Contact ID
  • Primary Email (or any Email field)
  • Other personalization fields (like First Name)
  • Contact 18 ID

Create a Data Extension

Your Data Extension should be configured like the following:

  • Contact ID=Key
  • Primary Email Field=Email Address
  • Include any fields used for personalization or dynamic content
  • DO NOT INCLUDE the Contact 18 ID in the Data Extension

 

  • Set to Overwrite
  • Click Save at the top
  • There is no need to Start the Data Extension since you will import a file to the Data Extension in step 3.

Add Column to Data Extension

With the basic Data Extension created, you need to add another column to house the data that you wish to upload. To manually add a new column to a Data Extension:

  • Navigate to Subscribers >> Salesforce Data Extension
  • Open the Data Extension you created
  • Add a field by working in the Fields box in the bottom right
  • Click the Edit Fields button
  • Click the Plus icon

 

  • Name the new field as you wish (ie External Data below)
  • If the value is text, keep the data type as text (most common)
  • Ensure the length number is larger than the number of characters in the field being uploaded
  • Check Nullable if this value can be blank
  • Provide a Default Value if you wish
  • Click Save Fields in the top-right

null

 

2 | Create File

There are many ways to go about getting prepared to upload a file to a Data Extension. 

  • Prepare a spreadsheet with columns including:
    • 18-digit contact ID
    • Email
      • these values can vary in the domain
    • other personalization fields
    • External data to add
  • Save as a CSV or TXT

XLOOKUP Function

  • Oftentimes, your file will need to be created by merging a Salesforce Report (with the 18-digit contact ID, email address, first name & last name) with an external file including custom information. The XLOOKUP function plays an important role in merging two sheets into a single sheet to be imported.

See three use cases below for importing a file:

Some users wish to have external information included in their Data Extension. This could be a custom salutation for personalization (like Mr. & Mrs. Jones) or a piece of information to drive dynamic content in an email. 

  1. Create a Report with the required columns and export:
    • 18 Digit Contact ID
    • University Email
    • field used to merge files (step 2 below)
    • other personalization fields
  2. Use XLOOKUP function to merge external file with exported Salesforce Report
  3. Save as file with columns labeled

Adding External Information for AMPscript

  • While uploading external details to a Data Extension to create custom emails with AMPscript is a valid use-case, it is not supported by eComm. Users with AMPscript experience are welcome to explore this functionality, noting eComm will not be able to provide resources or support.

Some of the campuses recently noticed many employees/students have an incorrect/missing email in their university email field. This issue stems from the source system (Campus Solutions or HRMS) and takes time to remedy. As an immediate work-around, you can Import over the Subscribers in Marketing Cloud that have incorrect/missing emails.

  • Create a Report with the respective University Email Field
    • Required Columns include: 18 Digit Contact ID, University Email, other personalization fields
  • Export Report
  • Manually input new email addresses in replace of the incorrect ones
  • Save as file with columns labeled

Merge multiple Data Extensions (with different University emails) into a single Data Extension. 

Employee Services is a good example of using this Import feature. Typically, when they email across the campuses they send to four different Data Extensions to target four different university emails. We can essentially merge those into one Data Extension so they can leverage A/B Testing and Journey Builder (which don’t support including multiple data extensions).

  1. Download and open the file
  2. Look for past 401(a) reports in SF
    • Save As a new report
    • Rename report with the date and add the description
  3. Open old campaign
    • Clone campaign
    • Rename the campaign with the date
    • Share campaign with Employee Services Public Group
    • Update campaign name in SF report filter
  4. Excel file
    • Add Campaign column > copy and paste campaign ID
    • Add Status column = Sent
    • Copy and paste all columns
  5. Apsona
    • Campaign Members > Tools > Import
    • Copy and paste from Excel file
    • Add=no checking > Tab > Next
    • Map Employee ID
    • Map Campaign ID
    • Map Status
    • Download any rejections. Sometimes Employee ID is wrong.
  6. Go to SF report and campaign and make sure the total number matches
    • Export report for each campus using the filters
    • Save as a file file (CSV or TXT)
    • Make sure there aren’t blank email addresses. If there are, look them up in SF and add the email address if they have a campus address.
    • Copy from campus email column and paste it into the email column (keep it all in one excel file)
    • Do this process for each campus (1, 5, 2 3 6, 4). Usually, there won’t be anyone for System but be sure to check.
    • Copy and paste all columns to the first exported file and make sure to copy and paste the campus emails into the email column.
  7. Go to Marketing Cloud to create the data extension
    • Open Employee Services BU
    • Email > Interactions > Import > Create

[image]

  1. Put DO NOT RUN in DE description
  2. Salesforce Objects & Reports > Search Report
  3. Campaign Name, Contact ID, First Name, Last Name, Email
  4. Contact ID is Key
  5. Update Email type to Email Address
  6. Save & Start
  7. Go to DE in Salesforce Data Extensions > DE > Records Tab > Import > Select excel file

[image]

[image]

  1. Double check import and make sure there aren’t personal email addresses
  2. Move DE to Shared Items > Employee Services folder

 

3 | Import File to Data Extension

  • Navigate to Content Builder
  • Hover Subscriptions then drill into Salesforce Data Extensions
  • Select your Data Extension name to open details

Open Salesforce Data Extension Details

  • Select tab two titled Records
  • Click Import in the top left

View Salesforce Data Extension Records & Import

  • File: find your saved file
  • Deliminator: Comma or Tab (depending on the file type)
  • Date Fromat: Leave as Default - English
  • Import Type: IT DEPENDS
    • Overwrite (most common) | Removes existing records and overwrites with file data
    • Add & Update | Add new records and update existing, will not remove existing
    • Add Only | Add new records, do not update or remove existing records
    • Update Only | Update existing records, do not add new or remove existing records
  • Import Options: Leave as Default - do not check/uncheck anything

Salesforce Data Extension, Import, Upload File

  • Select Next in the bottom right

STEP 1 | Create Data Extension The Import Type you chose here (Step 3A | Import file Type) depends on your desired outcome. This will also impact if you Start your Data Extension in Step 1 or not.

Select Map Manually

  • Contact 18 ID=Contact ID
  • Email=Email
  • Other fields=other fields

Salesforce Data Extension, Import, Configure Mapping

  • Select Next in the bottom right
  • Review your Import details
  • Select Import in the bottom right
  •  You’ll receive an email notice once complete. Confirm it is successful.
    • Failed Imports often read a Case Sensitive issue. Confirm you are importing a file with Contact 18 ID rather than just the Contact ID (15 digit).

WARNING

Do NOT re-Start your Data Extension after completing the import. The data you just imported will be overwritten.

4 | Add Personalization to Email & Test

  • To add personalization, click into the content block of the email
  • Select the Personalization icon and click Choose New Data Source in the dropdown

null

  • Choose you data source by opening the Salesforce Data Extensions folder 
  • Select your Data Extension and click the blue OK button in the bottom right

null

  • Now when you select the Personalization icon the Data Extension selected should be listed with available fields
  • Select the attribute you wish to add

null

Don't forget to test your personalization.