Automatically importing data

Rather than manually importing trading partners, dimension items, or transactions through Enable's user interface, you can configure this data to be automatically imported on a regular schedule.

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.

Universal requirements

No matter what type of data you are trying to import, all of the following criteria must be satisfied:

1. The dataset to be imported must be a CSV file within an encrypted ZIP file.
2. The names of these files must be in the format "DATASET-TIME.csv" and "DATASET-TIME.zip", where:
3. DATASET is replaced by a descriptor of the data set, further specified below (this field is not case sensitive), and
4. TIME is replaced by a 14-digit number in the form YYYYMMDDHHMMSS.
5. To be clear "DATASET-TIME" must be consistent across both files.
6. AES-256 encryption must be used. Your Customer Success team will coordinate the encryption key with you.
7. The compression method must be either 0x08 (deflate) or 0x00 (store).
8. The CSV file must have:
9. a first row consisting of correctly named labels for each column/field.
10. comma delimited fields in each row.
11. double-quoted text field values (to protect against commas in text values).
12. double-quote characters in text fields are represented by two double-quote characters.
13. 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. Please note the final row "ROWCOUNT IS X" has to all be in upper case. (Specifying the rowcount parameter is unique to the automatic import situation, and not done for manual imports.)
14. UTF-8 encoding and rows delimited by CRLF.
15. 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.

Trading Partners

1. 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.
2. The character limits for these labels are 100, 20, and 200 characters, respectively.
3. The placeholder name DATASET from requirement 1.1 must be replaced by the text TradingPartners.
4. Each trading partner name and reference must only occur once in its column.
5. There must only be one trading partner per row.

Dimension items

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:

1. The column labels row from requirement 2.1 must contain column labels for the dimension name, reference, and each attribute of that dimension.
2. The character limits for these labels are 255, 50, and 800 characters, respectively.
3. 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.
4. Each dimension name and reference in the import file must only occur once in its column.
5. There must only be one dimension item per row.
6. 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.

If you have more than one collection for a dimension, the file name will need to be updated to represent the collection you want to update. The relevant DATASET becomes the pluralized name of the dimension concatenated with the name of the collection. For example, say there are two collections in the Product Dimension, one called "2021 Products", the other called "2022 Products". When updating the 2022 Products collection, the file name should be "Products-2022 Products-20220310131523.zip".

Transactions

1. 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.
   
2. 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.
3. Dates in the data file must be in the form YYYY-MM-DD.

If you are using an interface date, it must be the interface date, not the transaction date, that is specified in the file name.

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

• Turnover-20210501to20210531-20210601150000.csv

You can also choose to specify a single dimension item in your transactions filename. This allows you to only upload, and delete, transactions for that specific dimension item. To do this, you must follow the format of "Turnover-DIMENSION NAME=[DIMENSION ITEM REFERENCE]-TRANSACTIONDATERANGE-TIME.CSV".

TURNOVER-TRANSACTION TYPE=[SALES]-20200908-20200908113815.zip is a valid filename for a channel with a dimension of 'Transaction Type' and an item within that dimension with a reference of SALES.

Rejection criteria

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).

Recommendations

When should I send my file?

We recommend files are sent overnight. Importing large data files can be intensive, and to limit the impact on performance, files should be uploaded at a time where there is a low user load on Enable.

Which files should I upload first?

We recommend that trading partners are uploaded first, then your dimensions (as dimensions can be listed against a specific trading partner), and finally the transactions. This ensures that the dimension data uploaded can be matched to trading partners, since if they are not the file will be rejected, and so the transaction lines can be matched to a supplier or dimension. The file won't be rejected if a transaction line isn't matched, however the transactions will appear as unreconciled and won't appear in reporting until they have been matched to a trading partner and all required dimensions. Find out more on the reconciliation process here.

Common errors

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.