File Upload Connector Configuration Guide

Before you start setting up a connector ensure:

• You have Data Admin permissions in One Model
• You can access Data > Sources from the One Model menu
• You know the details of the file you wish to upload: type of file, column delimiters, data formats

The File Upload method is used to complete once-off imports of simple files (uploaded manually by a user), or transferred automatically via One Model's hosted SFTP server. When using One Model hosted SFTP, customers push files to the One Model SFTP server, which monitors for new files and retrieves them automatically. SFTP users (for SFTP files) and file definitions are configured within the data source.

Creating a File Upload Data Source

From Data > Data Sources, click Add Data Source:

Select the One Model File Upload / SFTP connector:

Complete the data source configuration and click Add:

Your new File Upload data source will now be available in the Data Sources list. It can be managed via its options:

1. Upload file/s to the data source
2. Add/configure SFTP Users for the data source
3. Schedule when files are expected (note: this can also be used to configure Alerts)
4. Configure the data source
5. Add File Input
6. Delete the data source (note: this will also delete all file inputs)

File Input Types and YAML Configuration Overview

The File Upload connector accepts three types of files: delimited, JSON, and XML. A YAML editor is used to configure the file definitions (column names, data types, and format). YAML is a plain-text format that uses indented key-value pairs to define settings. Each field appears as fieldName: value. YAML is case-sensitive and uses consistent indentation (spaces, not tabs) to structure the configuration. If there are any syntax errors, the editor will not save and will display an error message at the top of the script. Some fields will have default values applied and once saved, if you reload the script you might see some additional fields are present with blank values - this is expected.

The configuration fields common to all delimited, JSON, and XML file inputs are listed below. Refer to each file type section for further details on their specific YAML configuration.

How to add and configure a delimited file input

Go to the data source grouping you will add your new file to and click + then Add Delimited Input:

Configure the file definition of the delimited input in the editor and click Save:

Here is an overview of the fields for the delimited file definition:

Here is an example of the YAML file definition for a comma delimited file that has one header row, no footer rows and includes quotes on columns that contain the delimiter and/or line breaks:

fileNamePattern: Original Location Mappings%
incrementalFileNamePattern: New Location Mappings%
schema: mapping
table: location
delimiter: ','
encoding: Utf8
fileNameColumn: filename
fileNameColumnIsKey: true
required: true
hasHeaderRows: true
headerRowsCount: 1
quoteCharacter: '"'
escapeCharacter: '"'
ignoreQuotes: false
skipInvalidRows: false
rowDelimiter:
ignoreFooterRows: false
footerRowsCount: 0

Next set up the definitions for the columns that the file is expecting to have in it. Each column in the file must be defined in the column list and in the same order as the file. The column names between the YAML configuration and the data file do not have to match as long as it correctly represents the data in the file. Column names in the definition must be lower case, start with a letter and contain only letters, numbers and underscores (_). As with table names, the column names set in the YAML configuration will be used in the data processing and when querying the data via SQL Query Explorer, it helps to be concise and descriptive.

For string fields, set these as a minimum:

- name: objid
  type: String
  size: 20

For integer fields, set these as a minimum:

- name: divgv
  type: Integer

For decimal fields, set these as a minimum:

- name: divgv
  type: Decimal
  precision: 18
  scale: 4

For date or timestamp fields, set these as a minimum:

- name: begda
  type: Timestamp
  dateFormat:
  - yyyyMMdd
  - yyyy-MM-dd

The final data source script ready to save will combine the file and column definitions eg:

fileNamePattern: Original Location Mappings%
incrementalFileNamePattern: New Location Mappings%
schema: mapping
table: location
delimiter: ','
encoding: Utf8
fileNameColumn: filename
fileNameColumnIsKey: true
required: true
hasHeaderRows: true
headerRowsCount: 1
quoteCharacter: '"'
escapeCharacter: '"'
ignoreQuotes: false
skipInvalidRows: false
rowDelimiter:
ignoreFooterRows: false
footerRowsCount: 0
columns:
- name: level_1
  type: String
  size: 100
- name: level_2
  type: String
  size: 100

How to add and configure a JSON file input

Go to the data source grouping you will add your new file to and click + then Add JSON Input:

Configure the file definition of the JSON input in the editor and click Save (note: the configuration will not save if there are syntax issues with the YAML configuration):

The JSON file input uses the same common YAML fields (fileNamePattern, schema, and others) described in the overview above. The JSON-specific configuration centres on the objects list, which tells One Model where to find the records in your JSON file and how to handle each set of data.

Next set up the definitions for the JSON objects. Each entry in objects defines one table - the path to the repeating data, what to include, and what to skip. A single JSON file can define more than one object, loading data into multiple tables in one load.

Example: a simple JSON file

For a JSON file structured like this:

{
  "employees": [
    { "id": "E001", "name": "Alex Smith", "department": "Finance" },
    { "id": "E002", "name": "Jordan Lee", "department": "HR" }
  ]
}

The complete YAML definition would be:

fileNamePattern: employees%.json
incrementalFileNamePattern:
schema: hr
fileNameColumn:
fileNameColumnIsKey: false
required: true
startAt: ''
encoding: Utf8
multiJsonObjectFileFormat: false
objects:
- name: employees
  path: employees
  cascadePaths: []
  ignorePaths: []
  ignoreWhenNull: false

Example: a nested JSON file with a wrapper object

For a JSON file where records are nested inside a wrapper object, use startAt to point directly to the nested records:

{
  "data": {
    "records": [
      { "id": "001", "status": "active" },
      { "id": "002", "status": "inactive" }
    ]
  }
}

The complete YAML definition would be:

fileNamePattern: records%.json
incrementalFileNamePattern:
schema: hr
fileNameColumn:
fileNameColumnIsKey: false
required: true
startAt: 'data'
encoding: Utf8
multiJsonObjectFileFormat: false
objects:
- name: records
  path: records
  cascadePaths: []
  ignorePaths: []
  ignoreWhenNull: false

Example: a newline-delimited JSON (NDJSON) file

For files where each line is a separate JSON object, set multiJsonObjectFileFormat: true:

{ "id": "E001", "name": "Alex Smith" }
{ "id": "E002", "name": "Jordan Lee" }

The complete YAML definition would be:

fileNamePattern: employees%.json
incrementalFileNamePattern:
schema: hr
fileNameColumn:
fileNameColumnIsKey: false
required: true
startAt: ''
encoding: Utf8
multiJsonObjectFileFormat: true
objects:
- name: employees
  path: ''
  cascadePaths: []
  ignorePaths: []
  ignoreWhenNull: false

How to add and configure an XML file input

Go to the data source grouping you will add your new file to and click + then Add XML Input:

Configure the file definition of the XML input in the editor and click Save (note: the configuration will not save if there are syntax issues with the YAML configuration):

XML files are hierarchical - data is structured as nested elements and attributes rather than flat rows and columns. The YAML configuration for an XML input maps this structure into flat database tables by defining table paths (the repeating elements that become rows) and using path syntax to identify the values to extract as columns.

Unlike delimited and JSON inputs, XML inputs do not have a table field at the top level. Instead, one or more tables are defined within the tables list, each with its own path into the XML structure. A single XML file can therefore populate multiple tables in one load.

Next set up the definitions for the XML tables. Each entry maps a repeating element in the XML to a database table:

Path syntax reference

All paths in the XML YAML configuration use the following token syntax. Table paths always begin from the root of the document; column paths (used in cascadePaths, renamePaths, and ignorePaths) always begin from the current table element.

Rules to follow when constructing paths:

• Table paths always begin from the root of the XML document (use //).
• Column paths always begin from the current table element (use ./ or ../).
• Any XML element with two or more occurrences at the same path must be defined as a table - it cannot be a column.
• Columns cannot be duplicated within a table. If the same path would produce two columns, either define it as a table or use renamePaths to give one a distinct name.
• All names (tables[].name, cascadePaths[].name, renamePaths[].name) must begin with a letter, contain only letters, numbers, and underscores, and be no longer than 128 characters.

Example: a simple XML file

In this example, each <employee> element becomes one row in the employees table. The id attribute and child elements (name, department, startDate) are automatically extracted as columns.

<employees>
  <employee id="E001">
    <name>Alex Smith</name>
    <department>Finance</department>
    <startDate>2021-03-15</startDate>
  </employee>
  <employee id="E002">
    <name>Jordan Lee</name>
    <department>HR</department>
    <startDate>2019-08-01</startDate>
  </employee>
</employees>

The complete YAML definition would be:

fileNamePattern: employees%.xml
incrementalFileNamePattern:
schema: hr
fileNameColumn:
fileNameColumnIsKey: false
required: true
cascadeRowIdOrNull:
  isKey: false
tables:
- name: employees
  path: //employees/employee
  recursive: false
  cascadePaths: []
  renamePaths: []
  ignorePaths: []
dataElementName:

Example: an XML file with a nested child table

Because <role> repeats, it must be defined as its own table. The two tables are linked automatically by One Model using a generated key - cascadeRowIdOrNull is set to isKey: true so that One Model generates a unique ID for each employee record that can be used to join the two tables. The ignorePaths entry on the employees table prevents the <roles> content from also being loaded as columns into the parent table.

<employees>
  <employee id="E001">
    <name>Alex Smith</name>
    <roles>
      <role>
        <title>Analyst</title>
        <from>2019-01-01</from>
      </role>
      <role>
        <title>Senior Analyst</title>
        <from>2021-06-01</from>
      </role>
    </roles>
  </employee>
</employees>

The complete YAML definition would be:

fileNamePattern: employees%.xml
incrementalFileNamePattern:
schema: hr
fileNameColumn:
fileNameColumnIsKey: false
required: true
cascadeRowIdOrNull:
  isKey: true
tables:
- name: employees
  path: //employees/employee
  recursive: false
  cascadePaths: []
  renamePaths: []
  ignorePaths:
  - path: ./roles/role
- name: employee_roles
  path: //employees/employee/roles/role
  recursive: false
  cascadePaths: []
  renamePaths: []
  ignorePaths: []
dataElementName:

Next steps

You have now configured your File Upload data source and file input. You can now:

• Upload a file to test the configuration via the Upload file/s option on the data source
• Monitor your data load from Data > Loads. For additional information on data loads see the guide to Data Loads.
• For additional assistance with date and timestamp column formats, see File Source - Delimited Input Date and Timestamp columns.
• For questions, contact your One Model Customer Success team.