Home  ›  How to Upload Csv File Into Data Extension Using Automation Studio

How to Upload Csv File Into Data Extension Using Automation Studio

Written By Tuesday, April 12, 2022 Add Comment Edit

Interaction Studio tin import information from external data sources using excerpt, transform, and load data feeds (ETLs).

Want to import data from Marketing Cloud Data Extensions? You can configure an automatic process in Marketing Cloud Automation Studio to automatically export data from information extensions and load them directly into Interaction Studio.

This article details how to connect your Interaction Studio inbound SFTP to Marketing Cloud, create an automation to export information feeds from Marketing Cloud data extensions using Automation Studio, and load these exported files to Interaction Studio.

Procedure Overview

  1. Create an SFTP user in Interaction Studio
  2. Connect the Interaction Studio SFTP to Marketing Cloud
  3. Create a source Data Extension in Marketing Cloud
  4. Define a scheduled Automation in Automation Studio
    1. Populate a source data extension with data such as that from SQL queries
    2. Export the source data extension to a CSV file
    3. Transfer the CSV file to the Interaction Studio SFTP

Create an SFTP User in Interaction Studio

  1. Log into Interaction Studio as an ambassador
  2. Follow the process detailed in Manage SFTP Users to create a new SFTP user in Interaction Studio. If you already accept an Interaction Studio SFTP account, you tin can skip this process and keep to the next section.

Connect the Interaction Studio SFTP to Marketing Cloud

  1. While logged into Interaction Studio or any Marketing Cloud product, hover over your name in the upper right corner and click Setup.
  2. Expand Data Management in the Assistants section of the left runway.
  3. Click File Locations.
  4. Click Create.
  5. Enter the properties for the file location:
    1. Name - Enter a proper noun that includes the dataset and folder. For instance, Interaction Studio Product Entering.
    2. External Central - leave this field blank.
    3. Description - enter a description of the file location that is related to the dataset and binder.
  6. Select the Location Type as External SFTP Site and complete the following fields:
    1. Url - enter the SFTP URL in the following format: FTP_DOMAIN_NAME/DATSET_NAME/inbound. For example, sftp.deutschland-2.evergage.com/appoint/entering
      1. FTP_DOMAIN_NAME - this is the host proper name of your Interaction Studio SFTP server. Information technology can be found in the concluding column of the SFTP user credentials CSV file. For example, sftp.germany-2.evergage.com.
      2. DATASET_NAME - this is the ID of the Interaction Studio dataset that contains the segment y'all are exporting. For instance, engage
    2. Port - enter 22
    3. Username - copy and paste the SFTP username from the CSV file y'all downloaded when you created the SFTP user
    4. Auth Type - select Password
    5. Password - re-create and paste the SFTP password from the CSV file you downloaded when you created the SFTP user
    6. Retype Countersign - copy and paste the SFTP password from the CSV file you downloaded when you created the SFTP user
  7. Click Save
  8. Repeat this for each dataset you have. For example:
    1. Exam Inbound
    2. Product Inbound

Create a Source Data Extension in Marketing Cloud

These instructions follow the procedure outlined in Create a Data Extension in Contact Architect. The steps listed beneath detail how to configure an ETL feed for user or subscriber data. For other ETL feed specifications, refer to the feed requirements for each data type.

  1. Select Audience Architect > Contact Builderfrom Marketing Deject'south primary navigation.
  2. Click Information Extensions at the top .
  3. Click Create in the upper right corner.
  4. Complete the information in the Properties section:
    • Creation Method - The method used to create the data extension. SelectCreate from New.
    • Proper name - Enter a name that matches the segment and then yous tin can identify the data extension in Contact Builder.
    • Description - Use this optional description to further assist identify the information extension within Contact Builder.
    • Is Sendable - Indicates whether yous intend this data extension to have letters sent to it. This checkbox determines whether the information extension is available to apply equally part of sends. For user or subscriber related feeds, create a sendable data extension so standard Marketing Cloud deletion processes will automatically remove information from the information extension.
  5. Click Next .
  6. Optional: Click On nether Data Retentionto gear up a data retentiveness policy for your information extension.
    1. To utilise the data retention policy, click the button under the  Use To  heading for the entity or entities.
    2. Click the appropriate push under the Retentiveness Period heading:
      1. For a revolving time menstruation, enter the number in the text field. Select the unit of measure in the dropdown menu next to the first button. For instance, y'all could enter the number 6 and select Months in the dropdown carte to enforce a 6-month data retention policy.
      2. For a fix date, select the date side by side to the 2nd push button.
  7. Click Adjacent.
  8. Configure allmandatory and optional columns according to the feed specification. Column names must match field names in the feed specification.
  9. Select Text (50 character length) for the Engagement and Boolean fields data types in Interaction Studio. For instance:
    1. attribute:sfmcContactKey
      1. Proper noun: attribute:sfmcContactKey
      2. Data Type: Text
      3. Length: 50
      4. Primary Cardinal: selected
      5. Nullable: non selected
    2. aspect:name
      1. Proper name: aspect:name
      2. Data Type: Text
      3. Length: fifty
      4. Primary Key: selected
      5. Nullable: selected
    3. attribute:isLoggedIn
      1. Proper name: attribute:isLoggedIn
      2. Data Blazon: Text
      3. Length: 50
      4. Primary Key: non selected
      5. Nullable: selected
    4. aspect:firstVisitDate
      1. Proper name: attribute:firstVisitDate
      2. Data Type: Text
      3. Length: 50
      4. Master Central: selected
      5. Nullable: selected
  10. Click Create .
  11. Click OK.
  12. Open up the data extension and save the External Key. You volition need it when you ready data extracts in Automation Studio.

Load Data to a Source Data Extension

A SQL query receives information from other information extensions and standard views of Marketing Cloud, transforms information technology to the required format, and saves it to the target data extension. The instance below describes the process using a SQL query, just you tin employ some other method.

  1. Open Automation Studio.
  2. On the Activities tab, create a SQL query activity.
  3. Fix your SQL Query Action like to SQL Query Example.
    1. Extract date fields: format dates in the ISO 8601 format, such as the format function:FORMAT(YOUR_DATE_COLUMN, 'yyyy-MM-ddTHH:mm:ssZ')
    2. Extract boolean fields:use "True" or "Imitation" for boolean values. By default, Marketing Cloud exports boolean values as "1" or "0". To convert a boolean value to the right format use: IIF(YOUR_BOOLEAN_COLUMN = ane, 'TRUE', 'FALSE')
  4. Continue configuring the SQL Query Activity. Select your source data extension and apply the Overwrite method for faster processing
  5. Manually run your SQL query to make sure that everything works as expected and the query populates your source data extension.

SQL Query Case

select
 Lower(SubscriberKey) as " aspect:sfmcContactKey ",
 FirstName as "attribute:name",
 Email_Address every bit "attribute:emailAddress",
 Number_Of_Visits as "attribute:visitNumber",

IIF(Known_Subscriber = one, 'Truthful', 'False') equally "attribute:isLoggedIn",
FORMAT(Registration_Date, 'yyyy-MM-ddTHH:mm:ssZ') equally "attribute:firstVisitDate"
from
ContactMaster

Ascertain an Automation in Automation Studio

These instructions follow the process outlined in Define a Scheduled Automation , just include customizations specific to automating a segment import into Marketing Cloud from Interaction Studio.

Important

Interaction Studio requires all CSV feeds to be encoded with UTF-eight. Marketing Deject data extension extracts are using a different encoding by default. Therefore you take to brand certain that a special " File Catechumen " data extract blazon is available on your Marketing Cloud Account. If it is not available create a support request to enable it on your business relationship.

To check availability of the " File Convert " data excerpt on your MC Account, create a new Data excerpt activity and make sure that there is " File Catechumen " in the list of Extract types

Create the Starting Source Schedule

  1. Select Journey Builder > Automation Studio f rom Marketing Cloud's master navigation.

  2. Click New Automation.

  3. Click and dragSchedule fromStarting Sources toStart with a Starting Source.

  4. Click Configure.

  5. Enter a Start Date and Time. Do not set a start date and time that is before the date that you will complete finish-to-end testing of this procedure.

  6. Confirm that the Time Zone is correct or make changes as needed.

  7. Select the Echo as Daily every 1 day(due south).

  8. Set theFinish as a number of occurrences, a specific engagement, or never.

  9. Click Done.

Add Required Activities

  1. SQL Query (or any other method) - to load data to the source data extension
  2. Data excerpt (Data Extension Extract) - to extract data from a data extension to a CSV file
  3. Data excerpt (File Convert) - to catechumen a CSV file to UTF-8
  4. File Transfer - to transfer a CSV file to the Interaction Studio FTP

Create a SQL Query

  1. Drag SQL Query onto the workflow canvas.

  2. ClickChoose to select an object for that activity.

  3. Select the SQL Query Action you created

  1. Elevate Data Extract onto the workflow canvas.

  2. ClickChoose to select an object for that activity.

  3. Click Create New Information Extract Activity.
  4. Configure the following settings:
    1. Extract Type: selectInformation Extension Extract
    2. File Proper noun Pattern: this depends on the feed type - see filing naming design example shown for User ETL
    3. Column Delimiter: comma (,)
    4. DECustomer Central: enter the client cardinal for your source data extension
    5. Has Cavalcade Headers: select Aye
    6. Text Qualified: select Yeah

File Naming Pattern Example

The Filing Naming Blueprint for the User ETL would exist:

user-%%Year%%-%%Calendar month%%-%%Solar day%%_00-00-00.csv

  1. Drag Data Extract onto the workflow canvas.
  2. Click Choose to select an object for that activeness.
  3. Click Create New Data Excerpt Activity.
  4. Configure the following settings:
    1. Extract Type:  select File Catechumen
    2. File Name Pattern: this depends on the feed type - see filing naming design example shown for User ETL
    3. Convert To: select UTF8
    4. Is File In Rubber House: select Yes

File Naming Pattern Example

The Filing Naming Blueprint for the User ETL would be:

user-%%Twelvemonth%%-%%Month%%-%%Day%%_00-00-00.csv

Transfer the CSV file to the Interaction Studio SFTP

  1. Elevate File Transfer onto the workflow sheet.
  2. Click Choose to select an object for that activity.
  3. Click Create New File Transfer Activity
  4. Configure the post-obit settings:
    1. Move a File From Safehouse: Yes
    2. File Proper noun Pattern: this depends on the feed blazon - see filing naming pattern example shown for User ETL
    3. Destination: Interaction Studio SFTP

File Naming Pattern Example

The Filing Naming Pattern for the User ETL would be:

user-%%Year%%-%%Month%%-%%Day%%_00-00-00.csv

Save, Test, and Run the Automation

  1. Add and configure more activities as needed. Click a configured activity to review, edit, or delete.
  2. Click Save to salve the Automation.
  3. Click Run Once to examination the automation one time at that place is a file in the inbound folder in the Interaction Studio FTP. New records should appear on the target data extension.
  4. Once yous complete testing, actuate the automation.

Enable and Run the ETL Process

For detailed instructions on how to run the ETL process, run into Setup and Deploy ETL Feeds in this knowledge base.

shatzerbres1944.blogspot.com

Source: https://doc.evergage.com/display/EKB/Send+CSV+Feeds+from+Marketing+Cloud

0 Response to "How to Upload Csv File Into Data Extension Using Automation Studio"

Post a Comment

Subscribe to: Post Comments (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel