To initiate the automated imports process, have your IT department contact Enable. You will be given credentials for an SFTP server where you can upload files with data to be imported to your channel. Enable will provide you with templates as part of the onboarding process to ensure that data is exported from your system in the correct format for the type of import you wish to run.
In order for an automatic import to work properly, please ensure that the file to upload adheres to the strict formatting requirements described below. Failure to follow these requirements can result in undesirable outcomes, like rejected uploads or the overwriting of large amounts of data.
If you are having issues with Excel like losing leading zeros, dates changing format unexpectedly, or character delimitation, please refer to our articles on how to load CSVs into Excel.
No matter what type of data you are trying to import, all of the following criteria must be satisfied:
- The dataset to be imported must be a CSV file within an encrypted ZIP file.
- The names of these files must be in the format "DATASET-TIME.csv" and "DATASET-TIME.zip", where:
- DATASET is replaced by a descriptor of the data set, further specified below (this field is not case sensitive), and
- TIME is replaced by a 14-digit number in the form YYYYMMDDHHMMSS.
- To be clear "DATASET-TIME" must be consistent across both files.
- AES-256 encryption must be used. Your Customer Success team will coordinate the encryption key with you.
- The compression method must be either 0x08 (deflate) or 0x00 (store).
- The names of these files must be in the format "DATASET-TIME.csv" and "DATASET-TIME.zip", where:
- The CSV file must have:
- a first row consisting of correctly named labels for each column/field.
- comma delimited fields in each row.
- double-quoted text field values (to protect against commas in text values).
- double-quote characters in text fields are represented by two double-quote characters.
- a final row consisting of the text "ROWCOUNT IS X", where X is replaced by the number of non-blank rows in the file, not including the column labels row (if present) or the row count row itself. (Specifying the rowcount parameter is unique to the automatic import situation, and not done for manual imports.)
- UTF-8 encoding and rows delimited by CRLF.
- For each zipped CSV that is successfully uploaded to the \Imports folder on Enable's SFTP server, an empty file of the same name with an additional appended ".ok" must also be uploaded after to this directory. (This begins the import process into your channel and prevents accidental use of files while they're being uploaded. The .ok postfix does not replace the .zip in the filename; it is merely added after.)
Example: Suppose you have a CSV of 20 products, along with their descriptions and prices, in a file generated on May 10th, 2021 at 13:15:23. To satisfy requirement 1.1, this file should be called "Products-20210510131523.csv". To satisfy requirement 2.1, when viewed in a text editor the first row could read
"Product name", "Product description", "Price",
and a subsequent example row (respecting requirements 2.2, 2.3, and 2.4) could be
"Example product", "Special 10"" widget, blue", 5.00,
Because the labelling row is not counted, to satisfy requirement 2.5 the final row in the spreadsheet would simply read
"ROWCOUNT IS 20"
After agreeing with your Customer Success team on an encryption key, you would then zip this CSV into an encrypted zipped file, say, "Products-20210510131523.zip" and upload it to the Imports folder on the SFTP server. Once this succeeded, you would add an empty file called "Products-20210510131523.zip.ok" to the same directory and the import process would typically begin.
Additional specific requirements
Depending on the type of data being imported, any additional formatting requirements are listed under the corresponding heading below.
- The column labels row from requirement 2.1 must contain column labels for the trading partner name, reference, and each attribute to be displayed in Enable.
- The character limits for these labels are 100, 20, and 200 characters, respectively.
- The placeholder name DATASET from requirement 1.1 must be replaced by the text TradingPartners.
- Each trading partner name and reference must only occur once in its column.
- There must only be one trading partner per row.
Possible common types of dimension items are Locations and Products, but these are just special cases of Enable's support for the addition of new dimensions. Any CSV containing dimension items for a particular dimension must have the following formatting, in addition to the universal formatting requirements above:
- The column labels row from requirement 2.1 must contain column labels for the dimension name, reference, and each attribute of that dimension.
- The character limits for these labels are 255, 50, and 800 characters, respectively.
- The placeholder name DATASET from requirement 1.1 must be replaced by the pluralized name of the relevant dimension. Common examples include "Products", "Locations", and "Transaction Types" but this will depend on your specific channel configuration.
- Each dimension name and reference in the import file must only occur once in its column.
- There must only be one dimension item per row.
- Any cells that correspond to dimension 'attribute' values must be non-null. If you are not sure which columns correspond to attributes of your data, please discuss this with your implementation team. If you are missing data for an attribute, populating the corresponding cell with a placeholder character (e.g., a hyphen) is a potential workaround.
- The column labels row from requirement 2.1 must contain column labels for the date, Trading Partner, product reference, location reference, each of the dimensions configured in your Trading Programs channel, units, value, currency code, external reference, and interface date.
- Do not use commas to separate thousands, "-" to denote zero, or parentheses to denote negatives. Just write the number without these, using a leading negative sign if necessary.
- Dates in the data file must be in the form YYYY-MM-DD.
- TIME is a timestamp in the form YYYYMMDDHHMMSS. This should generally be when the file was generated.
- TRANSACTIONDATE is a date in the form YYYYMMDD. This must coincide with the date that every transaction in the file took place. If you are uploading transactions over a range of dates, you should not use this filename format.
- TRANSACTIONDATERANGE is an 8 digit start date in the form YYYYMMDD, followed by the letters to, and then another identical or later 8 digit end date in the form YYYYMMDD. This allows for uploading groups of transactions over a number of days. This range must completely encompasses the transaction dates in the file you wish to upload.
It is very important to note that whenever a transaction date or date range is specified in a file being imported, any transactional data already in Trading Programs within this range is deleted (unless the import file is rejected; see below). This deletion applies to reconciled, unreconciled, and unmatched data, and the date range is inclusive.
If the behaviour in 3.4 is not desired, (perhaps you typically receive some transactions late and wish to add these without overwriting existing transactions), then the filename format for your uploads should just be "Turnover-TIME.csv".
- The corresponding ZIP file must have the same name as the CSV it contains.
Example: Suppose you have a CSV of transactions generated on June 1st, 2021 at 15:00:00, and it contains transaction data from May 1st to May 31st only. An appropriate name for this file would be
Asides from the requirements above, your automatic upload will be rejected if the data inside it does not satisfy certain other consistency requirements. Specifically, for files you wish to upload:
- There must not be any duplicate rows.
- Each transaction in the import file must occur either on the transaction date or within the transaction date range specified in Transactions Requirement 3. (Use of a primary key may supersede the rejection rule in this case; please discuss the advantages and drawbacks of primary keys with your implementation team.)
- The first date in a transaction date range cannot be chronologically later than the second date.
- The data must match your channel's configured dimensional structure.
- The number of transaction lines must match the rowcount from Universal Requirement 2.5.
- All mandatory columns must be populated for each transaction line.
- Each value in the currency column must match a permitted currency.
- Each transaction line must have correctly formatted values in the Date, Units, and Value columns.
If the data is rejected for any reason (e.g., even if there is just one offending line), no changes to your dataset on Enable's servers will be made at all.
Note: Be careful! If the name associated with a particular reference in an import file differs from the name currently stored in Enable, then the latter is overwritten with the former (unless this produces a duplicate name).
If your file has failed to import it will land in the 'Failed' folder within the 'Import' folder on the SFTP location. In this scenario, an accompanying Error.txt file will be created for each failed file detailing what the cause of failure is. For example, this may be an incorrect ZIP password, incorrect row count, incorrect naming/ formatting convention, etc. If you encounter any error messages that are unclear, or if you would like assistance troubleshooting these, please raise a Helpdesk ticket with us.
How do I send files to the SFTP location?
An FTP client will be required to send data to, and view, the SFTP location. Within Enable we use FileZilla, but this is not a requirement and any alternative FTP clients are suitable.
My file hasn't processed - help!
If your file has not processed and is still in the 'Imports' folder, please consider whether you have uploaded the corresponding .OK file, and also check that this is correctly named. The name of the .OK file should be identical to the .ZIP file, but instead of ending merely with .ZIP, it should end with .ZIP.OK.
I cannot connect to the SFTP?
The SFTP location has a firewall in place to prevent unauthorized access. Please ensure that you have provided us with your public IP address in order for us to add this to the whitelist. If you are unable to connect, please raise a Helpdesk ticket with us.
How can we see if a file has been received?
In the SFTP location, you are able to see the files that Enable have received. If a file is in the 'Succeeded' folder, this indicates that the data is now in the system. If a file is in the 'Failed' folder, unfortunately, the file has failed and a corresponding Error.txt file will explain the reason.
We recommend having an internal process to check these folders regularly to ensure the data in the system is as expected.
For further information on the format and content of the files required for automated imports, please raise a Helpdesk ticket with us.