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 /upload/, and drop your files in that folder 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/customers, and drop the csv file starting with TCA 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 mystore/failed/<entity> 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:

Core:

  • Stores
  • Employees
  • Goals

Catalog

  • Categories
  • Products and Product Value Categories
  • OptionTypes and OptionValues
  • Variants
  • Prices
  • Inventory

Clienteling

  • Customers / Customer Patch
  • Order History
  • Tasks
  • Preferences
  • Loyalty
  • Notes

Limitations

  • EMPLOYEES: You can provide a default password against an employee record, but you must ensure that password is 3 chars long. no encryption/encoding of that default password is currently support. Note that files are transferred using Secure SSH enabled SFTP and Production files are handled in a compliant manner. Associate passwords must be reset using the HQ app by Client after initial set up (Requires a manager access).

File Naming Conventions

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

Entity/Record TypeFile PrefixExamples
CustomerTCUTCU-CustomerSample001
Customer PatchTPCU
Customer Assigned EntitiesTCAE
Customer AssociationsTCAS
StoreTSTTST-StoresSample001.csv
Store PatchTPST
CategoryTCATCA-CategoriesSample001.csv
EmployeesTEMTEM-EmployeesSample001.csv
Option TypesTOTTOT-OptionTypesSample001.csv
Option ValuesTOVTOV-OptionValuesSample001.csv
ProductsTPRDTPR-ProductsSample001.csv
Product Value CategoriesTPVC
VariantsTVATVA-VariantsSample001.csv
PricesTPRCTPRC-PricesSample001.csv
InventoryTINTIN-InventorySample001.csv
Inventory - PATCH SupportTPINTINinventory_goodData1.csv
TINinventory_goodData2.csv
OrdersTORTOR-OrderHistorySample001.csv
Tasks (i.e. followups)TTATTAtasks_goodData1.csv
Customer PreferencesTCP
Customer Preferences GroupsTCPG
Customer Preferences ValuesTCPV
GoalsTKPI
Customer Loyalty Tier ValuesTCLTTCLTvalues_goodData1.csv
NotesTNOT

Important Considerations for Performance and Formatting

FORMATTING/NOTEDETAILS
VOLUMESTulip supports large files per type up to 1GB per file - however you may choose to keep the batch sized appropriately to aid troubleshooting in dev/qa phases. Product volumes can be larger based on optimal performance of your exporters and batched/split accordingly.
NAMING CONVENTIONAll files must be csv with the .csv extension. For Naming convention please see
Suggested: FILEPREFIX_FILETYPE_YYYYMMDD_HHII.csv (FILEPREFIX = mentioned below, FILETYPE = classification of a file, YYYY = year, MM = month, DD = day, HH = hour, II = minute)
Example: TCU_customers_20200120_1025.csv
Tulip absolutely needs to have the FILEPREFIX and its ending extension as “.csv”. Rest can be customized to your liking.
CATEGORY FILES PROCESSINGCategory records are split in our intake process and processed asynchronously. This means that to ensure that the Category levels are processed in the right order in Parent-Child relationship, you must split the Categories into separate files BY LEVEL. This means it is best to the send DEPARTMENT Categories separately, then CLASS, then SUBCLASS… SUB-SUB-CLASS and so on. For example, if we have the following categories Tops > T-Shirt, Tops > Jacket, Tops > Other. Split the data in two files. category_level1.csv included Tops. And category_level2.csv includes T-Shirt, Jacket, Other
SFTP PROCESSINGFiles get picked up as soon as the transfer to our SFTP has been completed. Files get moved in this folder path: upload -> received -> in progress -> completed (if successful) or failed (if not). Logs can be found in the root logs folder, organized by date. The log files are organized by data file name and line number of error.
PARTIAL SUBRESOURCE CREATIONThe latest release of File Importer loosens up the restrictions of the order that certain files must be processed in. A partial subresource will not be surfaced inside Tulip and the full entity must be imported before it will appear inside Tulip.
CategoryWhen a Category is provided that references a Parent Category that does not exist. File Importer will create a partial Parent Category whose data is to be provided at a later time (or later in the file). This means that Categories can be provided in any order.
ProductWhen a Product is provided that references a Parent Category that does not exist. File Importer will create a partial Parent Category whose data is to be provided at a later time, and assign the provided product to that partial category. This allows products to be imported before their associated Categories.
VariantWhen a Variant is provided that references a Product that does not exist. File Importer will create a partial Product whose data is to be provided at a later time, and assign the provided variant to that partial product. This allows Variants to be imported before their associated Products
InventoryWhen Inventory is provided that only references a Variant that does not exist, File Importer will result in an error. The Variant must exist in the system for Product to be inferred and mapped. The best practice is to provide the Variant and Product and if Variant and/or Product does not exist in the system, partials will be created. This allows Inventory to be imported before their associated Product / Variants. Stores however must be imported before.
PriceWhen a Price is provided that references a Product or Variant that does not exist. File Importer will create a partial Product or Variant whose data is to be provided at a later time, and assign the provided Price to that partial product or variant. This allows Price to be imported before their associated Product / Variants. Stores however must be imported before.
OBJECT / FILE PROCESSING SEQUENCEHierarchy is IMPORTANT to consider when sending files to Tulip. Ensure data in files is scheduled to be sequenced in the following manner to prevent errors due to parent child relationship that are enforced. E.g. Load STORES before EMPLOYEES because it is required for an employee to be assigned to a store, and hence Stores must exist in Tulip before Employees are sent.
1Stores (Can also be set up in Admin console)
2Employees (Can also be set up in Admin console)
3Categories *Partial subresource creation enabled (See the split above)
4Products *Partial subresource creation enabled
5Option Types
6Option Values
7Variants *Partial subresource creation enabled
8Price *Partial subresource creation enabled
9Inventory *Partial subresource creation enabled
10Customers
11Orders/Receipts
Which Files to Develop FirstGroup 1: Store > employees > Customers
Group 2: Categories > Products > OptionTypes > OptionValues > Variants > Prices > Inventory (which is dependent on Stores)
Group 3: Order History
Groups 1 & 2 can be worked on in parallel. Note, however, that if you are using multicatalog, the catalog entity will need to be sent before stores are sent. Within a group, each integration should come in sequentially. Group 3 (Order History) should come last after Groups 1 and 2 are fully processed.
Concurrent file handlingIf a file is uploaded twice in quick succession:
- If the first file is in processing status, the second file will be added to the queue.
- If the first file is in a received state, only the second file will be imported.
CONTENTS FORMATTINGHow to format Contents of the File
DATA TYPES, DATESAll dates must be ISO8601 format. Except important dates (CUSTOMER ENTITY) - which support YYYY-MM-DD only. All other formats must be string. Ensure to check metadata and FILE IMPORTER SAMPLES.
DATA TYPES, PHONE NOPhones numbers must always in the E164 format, that includes a “+” symbol, country, area, and phone number.
UPDATES TO RECORDSIf the ID of an entity already exists, then the record will be updated to match whatever data is passed. If the ID of an entity does not exist, then it will be created. Tulip will REPLACE the contents of its database with the incoming data from Client side via files - please ensure to include full record each time when sending an update to existing record.
EMPTY FIELDSNo need to put any placeholder texts for optional fields, leaving them blank will do the equivalent of setting them to null. Trim any extra white spaces in the header line or in the data. Remove any special encoding and if there is any data with a comma in it use double quotes to wrap the string. For example, “23,“Whitby, York”, 64CA”.
FILE TYPEUTF8 ENCODING
APP DISPLAY NOTECategories not visible if active product is not in category/ Each variant needs to have the same set of option types for the same product. E.g. If product A has variants X, Y, and Z. Then each X, Y, and Z need to have the same option types. For each entity (variants, orders, etc.) similar key ids (like Product ID in products file, Variant ID in variants file, etc.) needs to be added as contiguous record in the file one after another. See below for Multi-row formatting.
IDs in TulipAll columns (fields) that are of the naming convention “field ID” are the client-side system identifiers (eg Employee,Customer, Store, Product etc). The ids can be alphanumeric and up to 255 characters in length. Below is a reference to the key refnums, their names and maximum supported lengths:
  • Store ID - 64 characters
  • Category ID - 255 characters
  • Order ID - 32 characters
  • Customer ID - 255 characters
  • Employee ID - 255 characters
  • Option Type ID - 255 characters
  • Option Value ID - 255 characters
  • Product ID - 128 characters
  • Variant ID - 255 characters
IMAGESImage URLs domains will need to be whitelisted by Tulip. It is very important to send us the image hosts before you send us any image urls to process (such as when products and variants are sent).
HEADER ROWA FIRST row as the header row. The header row must contain, at a minimum, the required fields for that CSV file. The columns in the header row are recommended to be in a specific order as described in the File Importer Templates. All rows after the header row must be data rows.

Sending Rows of Content that Contain Arrays

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

Customer IDPhone Number IDPhone NumberPhone Number Country ID
customer1phone1123-456-7890CAN
customer1phone2123-654-0987CAN

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

Customer IDPhone Number 1 IDPhone Number 1Phone Number 1 Country IDPhone Number 2 IDPhone Number 2Phone Number 2 Country ID
customer1phone1123-456-7890CANphone2123-654-0987CAN

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.

← Async Bulk Operations
File Format - Customer / Customer Patch →