Back to home

Knowledge Base

Reporting Services

Transformation Service

Can I upload data via a file vs API?

YES!

What access does my company need to use file upload?

HUB services must be turned on.

How does the CSV work?

CSVs have to use a comma as a separator and use double quotes to wrap values that contain commas.

Tips for creating correct files when using excel.

If any special characters including & or , is used in a field please make sure to " " the values.
For dates, the TR format is YYYY-MM-DD.
Make sure that large numbers are not displayed in scientific notation.

How do I format the file?

Format

The file must be saved as .csv

Header Names

The header name must match the KOR Tech Spec "Field Tag" column value, not the "Field name" column (e.g., spaces are not allowed in the field name, nor are leading or trailing spaces around the name).

Header Order

The headers may be in any order.

Which cell formats am I allowed to use?

Whitespaces

The HUB trims whitespaces in cells at the front and the back of the value.

CSV input:

uniqueTransactionIdentifier , messageType
GEB8RU198SXPWY53DARM038ENEISP7BUKEIVUDMLPLFF2AWR37 , TRANSACTION

Correct result after HUB processing:

uniqueTransactionIdentifier,messageType
GEB8RU198SXPWY53DARM038ENEISP7BUKEIVUDMLPLFF2AWR37,TRANSACTION

Null values

When reporting multiple submissions at the same time, which is the case with CSV files, often have fields that certain submissions either don't have a value for or don't need to fill in at all.

The TR has the strict expectation that the value for a field that should be omitted, has to be empty in the CSV row.

The HUB, on the other hand, accepts many different "empty" values, such as NULL.
Null, null, NULL, "null", "Null", "NULL", "", and no characters at all, are all accepted.

callAmountLeg1,messageType
null,TRANSACTION
Null,TRANSACTION
NULL,TRANSACTION
,TRANSACTION
"null",TRANSACTION
"Null",TRANSACTION
"NULL",TRANSACTION
"",TRANSACTION

All these rows are correctly interpreted as:

callAmountLeg1,messageType
,TRANSACTION

Unknown fields

The HUB ignores any fields not part of the KOR technical specification.

Booleans

The HUB accepts many different forms of booleans.

True booleans

TRUE
True
true
1
Y
y
YES
Yes
yes
"True"

False booleans

FALSE
False
false
0
-1
N
n
NO
No
no
"No"

Numbers

The HUB trims all leading and trailing zeros to be aligned with the KOR technical specification.

CSV input:

notionalAmountInUSDLeg1
0
0.0
1.0
01
1.00
0.1
0.10
100000000000000000

Correct result after HUB processing:

notionalAmountInUSDLeg1
0
0
1
1
1
0.1
0.1
100000000000000000

Certain numbers are not interpreted due to loss of precision or unsupported formats:

1,0
1L
1i
1e
1π
cos(1)
1.0d
1.0f
1E-10
1E+10
1e+10
1.01e+10
1!
1(0)
1*1
١٢٣
१२३
0xFF
+1
a
1.1.1
--1
-1..0
-1..0
=1
|1
'1'
#1
1;

Dates

The HUB interprets many different date formats as long as there is no ambiguity possible.

2001-07-04
2001.07.04
2001/07/04
"Wed, 4 Jul 2001"
"Wed, 4 July 2001"
"Wednesday, 4 Jul 2001"
"Wednesday, 4 July 2001"
"Wed, Jul 4 2001"
"Wed, July 4 2001"
Jul 4 2001
July 4 2001
4 Jul 2001
4 July 2001
4-Jul-2001
4-July-200

All these values are correctly interpreted as:

2001-07-04

The HUB does not interpret dates for which the day, month, and year are not unambiguously identifiable:

04-07-01
04.07.01
04/07/01
4-Jul-01
4-July-01
"Wed, 4 Jul 01"
"Wed, 4 July 01"
"Wed, 4 Jul 01"
"Wed, 4 July 01"
"Wednesday, 4 Jul 01"
"Wednesday, 4 July 01"
"Wednesday, 4 Jul 01"
"Wednesday, 4 July 01"
"Wed, Jul 4 01"
"Wed, July 4 01"
"Wed, Jul 4 01"
"Wed, July 4 01"
"Wednesday, 4 Jul 01"
"Wednesday, 4 July 01"
"Wednesday, 4 Jul 01"
"Wednesday, 4 July 01"
"Wed, Jul 4 01"
"Wed, July 4 01"
"Wed, Jul 4 01"
"Wed, July 4 01"

Datetimes

The HUB interprets many different datetime formats as long as there is no ambiguity possible.

2001-07-04T12:08:56Z
2001-07-04T12:08:56.5Z
2001-07-04T12:08:56.53Z
2001-07-04T12:08:56.235Z
2001-07-04T12:08:56.678Z
2001-07-04T12:08:56+00:00
2001-07-04T12:08:56.235+00:00
2001-07-04T12:08:56.235-00:00
2001-07-04T11:08:56.235-01:00
2001-07-04T13:08:56.235+01:00
2001-07-04 12:08:56 +00
2001-07-04 04:08:56 -08
2001-07-04 04:08:56.678 -08
2001.07.04 04:08:56 -08
2001.07.04 04:08:56.678 -08
2001/07/04 04:08:56 -08
2001/07/04 04:08:56.678 -08
"Wed, 4 Jul 2001 04:08:56 -08:00"
"Wed, 4 July 2001 04:08:56 -08:00"
"Wed, 4 Jul 2001 04:08:56.678 -08:00"
"Wed, 4 July 2001 04:08:56.678 -08:00"
"Wednesday, 4 Jul 2001 4:08:56 -08"
"Wednesday, 4 July 2001 4:08:56 -08"
"Wednesday, 4 Jul 2001 4:08:56.678 -08"
"Wednesday, 4 July 2001 4:08:56.678 -08"
"Wed, Jul 4 2001 12:08:56 +00"
"Wed, July 4 2001 12:08:56 +00"
"Wed, Jul 4 2001 12:08:56.678 +00"
"Wed, July 4 2001 12:08:56.678 +00"
Jul 4 2001 12:08:56 +00
July 4 2001 12:08:56 +00
Jul 4 2001 12:08:56.678 +00
July 4 2001 12:08:56.678 +00
4 Jul 2001 12:08:56 +00
4 July 2001 12:08:56 +00
4 Jul 2001 12:08:56.678 +00
4 July 2001 12:08:56.678 +00
4-Jul-2001 12:08:56 +00
4-July-2001 12:08:56 +00
4-Jul-2001 12:08:56.678 +00
4-July-2001 12:08:56.678 +00

All these values are correctly interpreted as:

2001-07-04T12:08:56Z

The HUB does not interpret datetimes for which the day, month, and year are not unambiguously identifiable.

Three-letter time zone IDs such as "PST", "CTT", "AST" are also not interpreted because the same abbreviation is often used for multiple time zones (for example, "CST" could be U.S. "Central Standard Time" and "China Standard Time"):

2001-07-04T12:08:56.235+0000
2001-07-04T12:08:56.235-0000
2001-07-04T11:08:56.235-0100
2001-07-04T13:08:56.235+0100
04-07-2001 12:08:56 +00:00
04-07-2001 04:08:56 -08:00
04-07-2001 04:08:56.678 -08:00
04.07.2001 04:08:56 -08:00
04.07.2001 04:08:56.678 -08:00
04/07/2001 04:08:56 -08:00
04/07/2001 04:08:56.678 -08:00
04-07-01 12:08:56 +00:00
04-07-01 04:08:56 -08:00
04-07-01 04:08:56.678 -08:00
04.07.01 04:08:56 -08:00
04.07.01 04:08:56.678 -08:00
04/07/01 04:08:56 -08:00
04/07/01 04:08:56.678 -08:00
4-Jul-01 12:08:56.678 +00:00
4-July-01 12:08:56.678 +00:00
4-July-01 12:08:56.678 +00:00
2001-07-04T12:08:56
2001-07-04T12:08:56.5
2001-07-04T12:08:56.53
2001-07-04T12:08:56.235
2001-07-04T12:08:56.678
2001-07-04T12:08:56
2001-07-04T12:08:56.235
2001-07-04 12:08:56
2001-07-04 04:08:56
2001-07-04 04:08:56.678
2001.07.04 04:08:56
2001.07.04 04:08:56.678
2001/07/04 04:08:56
2001/07/04 04:08:56.678
Jul 4 2001 12:08:56
July 4 2001 12:08:56
Jul 4 2001 12:08:56.678
July 4 2001 12:08:56.678
4 Jul 2001 12:08:56
4 July 2001 12:08:56
4 Jul 2001 12:08:56.678
4 July 2001 12:08:56.678
4-Jul-2001 12:08:56
4-July-2001 12:08:56
4-Jul-2001 12:08:56.678
4-July-2001 12:08:56.678
4-Jul-01 12:08:56
4-July-01 12:08:56
4-Jul-01 12:08:56.678
4-July-01 12:08:56.678
2001-07-04 12:08:56 UTC
2001-07-04 04:08:56 PDT
2001-07-04 04:08:56.678 PDT
2001.07.04 04:08:56 PDT
2001.07.04 04:08:56.678 PDT
2001/07/04 04:08:56 PDT
2001/07/04 04:08:56.678 PDT
4-Jul-01 12:08:56 +00
4-July-01 12:08:56 +00
4-Jul-01 12:08:56.678 +00
4-July-01 12:08:56.678 +00
Jul 4 2001 12:08:56 UTC
July 4 2001 12:08:56 UTC
Jul 4 2001 12:08:56.678 UTC
July 4 2001 12:08:56.678 UTC
4 Jul 2001 12:08:56 UTC
4 July 2001 12:08:56 UTC
4 Jul 2001 12:08:56.678 UTC
4 July 2001 12:08:56.678 UTC
4-Jul-2001 12:08:56 UTC
4-July-2001 12:08:56 UTC
4-Jul-2001 12:08:56.678 UTC
4-July-2001 12:08:56.678 UTC
4-Jul-01 12:08:56 UTC
4-July-01 12:08:56 UTC
4-Jul-01 12:08:56.678 UTC
4-July-01 12:08:56.678 UTC
"Wed, 4 Jul 2001 04:08:56"
"Wed, 4 July 2001 04:08:56"
"Wed, 4 Jul 2001 04:08:56.678"
"Wed, 4 July 2001 04:08:56.678"
"Wednesday, 4 Jul 2001 4:08:56"
"Wednesday, 4 July 2001 4:08:56"
"Wednesday, 4 Jul 2001 4:08:56.678"
"Wednesday, 4 July 2001 4:08:56.678"
"Wed, Jul 4 2001 12:08:56"
"Wed, July 4 2001 12:08:56"
"Wed, Jul 4 2001 12:08:56.678"
"Wed, July 4 2001 12:08:56.678"
"Wednesday, 4 Jul 2001 4:08:56 PDT"
"Wednesday, 4 July 2001 4:08:56 PDT"
"Wednesday, 4 Jul 2001 4:08:56.678 PDT"
"Wednesday, 4 July 2001 4:08:56.678 PDT"
"Wed, Jul 4 2001 12:08:56 UTC"
"Wed, July 4 2001 12:08:56 UTC"
"Wed, Jul 4 2001 12:08:56.678 UTC"
"Wed, July 4 2001 12:08:56.678 UTC"

Arrays

Certain fields are allowed to have multiple values, submitted with a delimiter.

The HUB accepts only two delimiters. The following logic is applied:

For any fields that allow multiple values, the entire value is evaluated.
If a semicolon is detected, the entire value is split using semicolon as delimiter.
If no semicolon is detected, the HUB looks for a pipe as delimiter. If a pipe is detected, the entire value is split using pipe as delimiter.
Any trailing delimiters are ignored.
Any leading delimiter, or consecutive delimiters, are resolved with null values. Note that null values inside arrays are not allowed per the KOR technical specification and will yield rejections in the TR.

The following valid CSV demonstrates allowable values by the HUB for an array field called clearingSwapUTIs:

uniqueTransactionIdentifier,clearingSwapUTIs
foo,bar
foo,bar;baz
foo,bar;baz;
foo,"bar;baz"
foo,bar|baz
foo,bar|baz|
foo,"bar|baz|"

Note that wrapping array elements in double quotes is not allowed. If any array value contains a comma, the entire array needs to be wrapped in double quotes. The use of brackets to denote arrays is not supported.

Is there a file size limit?

Yes, 10MB.

Is there a maximum number of submissions?

Yes, we have a maximum of up to 20.000 submissions within the same CSV file.

Using the UI to upload a CSV file

To use the UI to upload a CSV file, you need a HUB subscription. Please get in touch with KOR if you need more information and/or if you want to subscribe.

Navigate to the "Upload" section and choose "CSV File upload". On this page, you will see an overview with more information on your previous uploads and their status. Clicking on an upload will navigate to a detail page with more information on each individual submission that was part of the upload
Click "Upload new file" to start uploading a new CSV file.
When the file is submitted KOR will perform some initial validation (see next section) and, upon successful validation, start processing the entries. Refresh the page or enable "Auto Updates" to see the most recent status of your upload.

Screenshot 2023-01-31 at 13.43.28

Screenshot 2023-01-31 at 13.42.35

Initial input validation

Before a CSV file gets processed, some initial validation is performed to avoid unnecessarily unauthorized and rejected submissions.

The following validation criteria are applied:

The following columns are mandatory at all times:
- messageType
  - Value must be a valid message type. Refer to the KOR technical specification.
- uniqueTransactionIdentifier or uniqueSwapIdentifier
- validationVersion
  - Value must be "2.0"
- reportingJurisdiction
  - Value must be "CFTC"

- otherSubmitterIdentifier
  - Value must be equal to your LEI
- initialMarginCollateralPortfolioCode or variationMarginCollateralPortfolioCode (only mandatory if the file contains any COLLATERAL messages)
At least one record must be present in the CSV file next to the headers
The number of headers and values of each line should be the same

Refer to the displayed error message(s) to figure out the exact error, make a correction in the file and try re-submitting it.

Looking up the details of the uploaded CSV

Clicking a row in the table of the "CSV file upload"-tab opens a detail page:

Clicking any of the processed submissions opens the detail of the submission in a new tab which can be useful to immediately troubleshoot any rejections the submission may have.