CSV Data Format

An uploaded comma separated values (CSV) file must follow the Oracle Intelligent Track and Trace proprietary format.

Rows

• The first row is a header row, which is mandatory in a CSV file. It lists all possible fields from the document schema.
• The second row is a hint row and is optional. This row provides information about the data type.
• Remaining rows contain data.

Columns

• The first column is a special column containing record identifier(s). A document can span over multiple CSV rows (this is necessary for arrays, as described below), which is dictated by this column. The value of cells in this column have no restrictions. The parser reads the CSV file row by row. If it encounters an unique record identifier, all rows below this row that have the same or no record identifier belong to the currently processed document.
• All other columns correspond to properties from the document type schema.

Header row

• The first column is referred as record identifier.
• Remaining columns are generated using the following algorithm:
  • If the field type is an object, all inner properties are flattened using /.
  • If the field type is an array of primitive types, simply, the field name is used and can be nested in an object.
  • It the field type is an array of objects, <arrayFieldName>/<innerObjectName> notation is used. Objects having nested properties are supported and follow the generic rule for objects.
  • For all other field types, simply the field name is used.

Hint row

The cells of this row represent the <dataType>. If the data type is an array of primitives, list[<primitiveType>] is used. If it's an array of simple objects, list[object(<fieldFromObject>)] is used. If a hint row is present, the first column, also known as the record identifier, in this row must be empty.

Data Validation

If there is any conflict detected in the CSV file, the particular document is considered to be invalid. For example, if a field that is defined as String has two strings one below other belonging to the same document, then it fails the parsing rules.

Empty lines are allowed in a CSV file.

Null Values

To represent null values, leaving the corresponding cells empty.

JSON Schema Draft 7 Constructs Not Supported

The CSV format does not support the following JSON constructs of the document variant schema:

• allOf
• anyOf
• oneOf
• not
• if
• then
• else
• array in type field (allowed only single null-able types), e.g. type: ["string", "integer"] is forbidden
• arrays inside arrays

Sample CSV document

 
 record identifier, result/orderNumber, result/orderDate, result/currencyCode, result/orderLine/itemNumber, result/orderLine/quantity, result/orderLine/itemDescription
 ,                  string,             integer,          string,              list[object(number)],        list[object(number)],      list[object(string)]
 1,                 X118654,            1614955016,       USD,                 1,                           2,                         LAPTOP
 1,                 ,                   ,                 ,                    2,                           12,                        KEYBOARD
 1,                 ,                   ,                 ,                    3,                           2,                         MOUSE
 2,                 X118566,            1614955385,       GBP,                 1,                           5,                         LAPTOP
 2,                 ,                   ,                 ,                    2,                           3,                         MOUSE
                          

This sample CSV file gets translated to two JSON documents as follows:

JSON Document 1:

             {
                    "result": {
                        "orderNumber": "X118654",
                        "orderDate": 1614955016,
                        "currencyCode": "USD",
                        "orderLine": [
                            {
                                "itemNumber": 1,
                                "quantity": 2,
                                "itemDescription": "LAPTOP"
                            },
                            {
                                "itemNumber": 2,
                                "quantity": 12,
                                "itemDescription": "KEYBOARD"
                            },
                            {
                                "itemNumber": 3,
                                "quantity": 2,
                                "itemDescription": "MOUSE"
                            }
                        ]
                    }
                }

JSON Document 2:

                {
                    "result": {
                        "orderNumber": "X118566",
                        "orderDate": 1614955385,
                        "currencyCode": "GBP",
                        "orderLine": [
                            {
                                "itemNumber": 1,
                                "quantity": 5,
                                "itemDescription": "LAPTOP"
                            },
                            {
                                "itemNumber": 2,
                                "quantity": 3,
                                "itemDescription": "MOUSE"
                            }
                        ]
                    }
                }
            

Note:

Processing of the uploaded CSV file is not transactional. If one of the records in the uploaded CSV file has conversion or validation issues, then the processing of the remaining records won't be affected.