File Importer Overview

Tulip’s File Importer tool simplifies the process of uploading files by enabling clients to send simple files via placing them in Tulip’s SFTP. Tulip supports CSV currently in simple decomposed (normalized) entity-specific formats that are ingested by an integration layer.

Files are landed through Tulip’s SFTP framework and uploaded to the Client tenant using Tulip’s Bulk API.

File Importer provides a rapid way to get data to Tulip as an alternative to using the Bulk API. The key difference between Bulk API and File Importer – aside from file-based transfers that are not synchronous/real time – is limited flexibility/synchronous error handling, support for custom lists of attributes, and additional specific endpoints that File Importer currently does not support. See later section in this page for details. You can manage File Importer in Admin Console under the Integratons -> Data Imports tab. You can turn on Tulip’s File Importer on this tab. Note that File Importer will be autodisabled if no files have been processed within the last 180 days. If this is the case, please return to this tab and reenable file importer.

Enable File Imports in your Environment#

1. Go to Admin Console and select the Integrations tab, then navigate to Data Import.
2. Select Activate File Importer, and then confirm the activation.

Uploading your SSH key#

1. Create a public/private key pair in ssh-rsa format. You can use the command ssh-keygen -t rsa in Windows command prompt or Mac terminal to generate the key.

2. Follow the prompts to generate the public and private key. The private key should never be shared except with an authenticating tool like Filezilla or a scheduling/managed automated file transfer tool you may use to send Tulip data.
   
   .
   By default, your first rsa public key is saved as the C:\Users<yourusername>.ssh\id_rsa.pub file on Windows, and the ~/.ssh/id_rsa.pub file on a Mac.
   Note that the .pub is the public key and the file without the .pub extension is your paired private key. You will want to copy the contents inside the .pub file to your clipboard for the next steps.

3. Navigate to the SFTP Keys module in Admin Console under the Integrations header in the sidebar.
   
   

4. Add the PUBLIC key by using the add SFTP Key UI. Note: Remember to add a helpful comment to remember what this key is used for. Example: OMS Integration or Staging Environment.
   
   
   

5. Connect to the SFTP server using your preferred SFTP client (for example, FileZilla). Connection details can be found by clicking the SFTP Connection Instructions button on the SFTP Keys module.
   .
   If your tenant was called mystore and you were in the staging environment, your host would be sftp-tulip-staging.tulipretail.com and your username would be mystore. If you were in the production environment on the mystore tenant, your username would remain the same, but you host would be sftp-tulip-prod-<region>.tulipretail.com, where region is one of the supported Tulip host regions in which your tenant is hosted - for instance, na, eu, de, or cn. The below is how your connection would appear in Filezilla if you were connecting to the mystore tenant in the staging environment. Not that the key file is the private key (i.e. without the .pub) that you generated in steps 1-2.
   .

6. To upload a file go to <yourtenant>/upload/, and drop your files in the generic folder (or the entity folder for any entities not in aerial - i.e. customer_associations) using the appropriate prefix on the file as mentioned in File Naming Conventions. For instance, if your tenant was named mystore and you wanted to upload a customers file to the staging environment, you would connect to the host sftp-tulip-staging.tulipretail.com, navigate to mystore/upload/generic, and drop the csv file starting with TGEN_customers into that folder.
   .

7. You can monitor the progress of your files in Admin Console under integrations -> Data Import. This will display the total records processed and how many uploaded successfully or received errors. Error logs can be downloaded from this interface. Error logs will also be surfaced under the mystore/logs SFTP folder.
   .
   Any files that do not succeed will be moved into the appropriate subfolder of the mystore/failed/ folder, where they will be retained for 30 days.

File Processing at a glance#

At a high level file processing involves the following steps:

• The Client team places files in their appropriate SFTP upload directory. The name of the file determines the type of record to import. See Naming conventions in this guide.
• The records contained in the file are processed soon after they are uploaded to Tulip’s API. Uploads can be sent mutiple times on the same record and each time it will be updated fully with the contents in the latest record.
• Logs are generated in error files for failed records.
• Logs and current import statuses can be viewed in Admin Console under the Data Imports tab.

Supported File Formats#

File formats currently supported include:

• CSV (today)
• JSON (future)

Supported Data Entities#

Due to the decoupled nature of data processing on Tulip side and to apply a uniform cross client applicability of being to prepare, provide and test data, the entities in the File importer may not be combined currently into unified (denormalized) importable formats. Following data entities are supported:

Full list available in Aerial (TGEN prefix)

Other files are specified below.

File Naming Conventions#

The type of records to import are defined by the file prefix. Note, please only use alphanumeric characters and (hyphens and underscores) when naming your file.

TGEN files#

Many endpoints that exist in Aerial have a corresponding file that can be sent via File Importer. To see a full list of the files available, see the TGEN page. Each file begins with a TGEN prefix.

The filename of the file you upload to the upload/generic folder (whether through SFTP or directly via Admin Console) must follow the format:

TGEN_<recordType>_<importType>_<apiVersion>_<optionalSuffix>.csv

Only the part up to <apiVersion> should be included in the file prefix.

The below files are on Tulip’s older API and do not yet support the TGEN prefix. The required prefix is specified in the table.

Working with TGEN Files#

This section outlines how to map API schema to headers in your FFI CSV file.

Top level fields#

• Simply include the field name as documented in the upsert or patch endpoint’s request body.
  • Example: to target the firstName top-level field for a Customer Upsert, name the header firstName.
• To omit the field from the API payload, leave the column completely blank.
• To send a blank string as the field value, set this column as ''.

Nested fields#

• Use dot-notation to target any nested fields.
  • Example: to target the fulfillFromMultipleStoresEnabled setting field for a Stores Upsert endpoint, name the header settings.fulfillFromMultipleStoresEnabled.
• Multiple levels of nesting are supported.
  • Example: to target the open field for Monday hours for a Stores Upsert, name the header monday.hours.open.
• To omit the field from the API payload, leave the column completely blank.
• To send a blank string as the field value, set this column as ''.

Multiple (array based) fields#

• Use a zero-based array indexing convention (i.e. fieldName[0]) to target any array based field values.
  • Example: to target the first address' externalId field in a Customer Upsert, name your header addresses[0].externalId.
• To target subsequent addresses, increment your array index as required:
  • Example: to target the second address' externalId field for a Customer Upsert, name your header addresses[1].externalId.
• Combine this with the dot-notation convention to target any field in the request body.
• To omit the field from the API payload, leave the column completely blank.
• To send a blank string as the field value, set this column as ''.

Error responses and debugging#

FFI Error checking#

The FFI system will parse the filename and respond with an error if:

• The recordType is not supported.
• The importType is not one of fullUpdate or partialUpdate.
  • It will NOT check if an upsert or patch endpoint is available for the record type (this error will be caught by the API call itself)
• The apiVersion is not of the format YYYY-MM.
  • It will NOT check if the api version is supported by the targeted endpoint (this error will be caught by the API call itself)

API Error checking If the above checks pass, the CSV file will be parsed row by row. Each row will be converted to a JSON payload and sent to the targeted API endpoint as determined by the Filename and CSV header naming conventions detailed above.

Any error that results from the API call itself will be captured and written to the errors file available once the data import is complete (in the same locations as any other failed import).

Partial Update Files#

A file that file allows uses partialUpdate as the import type corresponds to a Tulip PATCH endpoint. This allows the user to provide partial data without the unsent data getting cleared out. This means that the integrator does not need to populate every field in the .csv to update a single field. If an integrator wants to remove / set a field as NULL or empty string in Tulip, they can simply provide the string ‘SET_AS_NULL" in the appropriate field and that field will be emptied.

Important Considerations for Performance and Formatting#

Sending Rows of Content that Contain Arrays (APPLIES TO NON-TGEN FILES ONLY)#

This section clarifies how Tulip Importer supports sending arrays of nested fields under a record type. This provides a lot of flexibility in being able to sent arrays of fields in the files. This allows for multiple values for an entity, such as multiple ADDRESSES and PHONE numbers for a single Customer object. The same functionality is available for Order entities as well.

For every possible type of CSV file you can import (such as customers or products), there is a set of one or more required fields that must be filled out in order to have a successful import.

On a given row:

• A row that contains different ID fields than the previous row counts as a new instance (such as a new customer or a new product).

• A row that contains the SAME KEY or ID fields as the PREVIOUS row counts as an additional row, and is a PART of same instance as the previous row, except where specified.

  • Additional rows that share the same Unique Row ID may only contain information under groups of columns labelled as Multi-Row Headers. Information in fields not under these types of columns will not be used since the first row’s info is used.

• Multi-Row Headers and Multi-Column Headers are both types of columns where multiple pieces of information can exist.

  • Multi-Row Headers allow for information to exist in additional rows whereas
  • Multi-Column Headers allow you to spread the same information across multiple columns

• Every group of Multi-Row Headers has a Multi-Column Headers group counterpart, and vice versa.

  • These group counterparts may not be used together in the same CSV file.
  • However, Multi-Row Headers and Multi-Column Headers may exist in the same CSV file as long as their group is different.

Examples#

To see the difference between a multi-row header and a multi-column, consider the following example.

A customer CSV file includes two phone numbers. The first row is the header row.

Both customer ID and Phone Number ID repeat but the values are different. This allows this customer1 to have 2 phone numbers.

Multi-Row

How it looks inside of a CSV file - not how the Customer ID is the same. IMPORTANT: The rows with the same ID MUST BE CONTIGUOUS. Distributing the unique row ID in different locations in the file will lead to File processing considering each record as a separate UPDATE instead of attempting to group the array items together.

Customer ID,Phone Number ID,Phone Number,Phone Number Country ID
customer1,phone1,123-456-7890,CAN
customer1,phone2,123-654-0987,CAN 

Multi-Column

How it looks inside of a CSV file:

Customer ID,Phone Number 1 ID,Phone Number 1,Phone Number 1 Country ID,Phone Number 2 ID,Phone Number 2,Phone Number 2 Country ID
customer1,phone1,123-456-7890,CAN,phone2,123-654-0987,CAN 

Unlike the Multi-Row example, headers of the Phone Number group using Multi-Column headers include identifying numbers. These numbers have no meaning other than for the importer to differentiate different Phone Numbers.

These identifying numbers must be integers, but can be in any order, and do not have to begin with 1 nor be sequential. Although for readability it is recommended to start with 1 and increased towards the right side.

Although both ways to implement such multiplicity in values that are tied to single parent record is supported, please see the samples provided throughout this documentation to help create your data mapping accordingly.

Supported CSV File Types#

Click on the link below to see the formatting and column level header specifications and data types for each.

• TGEN
• Categories
• Option Types
• Option Values
• Products
• Product Value Categories
• Variants
• Prices
• Customer Assigned Entities
• Customer Associations
• Order History
• Inventory