Upload CSV and Excel

This section outlines the common functionality between the CSV and Excel uploads.

Overwrite Control

Overwrite Load Id

If the load id already exists in the system, all the data related to the load id will be deleted and the new data will be uploaded.

Append Data

This will append the uploaded data to the existing load id.

Recreate Table

This will drop the entire table, and recreate it in the format of the uploaded CSV/Excel file. This is useful if the format of the file has changed significantly. This will delete all data.

Error Handling

Error handling defines how to handle errors encounted during the load. Most errors are data type errors, eg Akumen has identified the column as a float, but the data coming through has #NA in it.

• Raise Error: Prevents the load if any errors occur
• Flag Row: Creates a new column called ErrorString that contains the column within that row that had the error, along with the value that caused the error.
• Empty Cell: Simply sets the cell in error to null, but continues to write the row. Useful for data in long format when the rest of the row still needs to be written.
• Ignore Entire Row: Simply doesn’t write the entire row. This is usefull for data in wide format, where it is useful to skip a row if the transposed cell has an error.

Date Format

The date format specifies the expected format for loading data via CSV. As well as the date format, it is also used when converting the financial year date formats into the approriate financial year for the selected country.

Supported formats are en-AU, en-US, en-CA, fr-CA, ja-JP.

Load Id

The Load Id is an important concept in datasources. It provides the ability to reference individual data “loads”. The Load Id can be autogenerated or user entered. Querying datasources through Python or VDM can reference the load id to only fetch a subset of the data within the database.

The load id selection is an autocomplete box that detects other load ids to assist in selecting an existing one for overwrite.

Tranposition

Incoming data can be in either Long format

Or Wide format

If data is in wide format and Akumen detects dates in the headers, it will convert the dataset to long format for ingestion. All of the “category” columns are retained, but a new column is created called “date” where the date values are copied into, and a new column called “value” is created, where all the values are copied into. Basically a new row is created for every cell within the wide format data.

In addition, dates can either be valid dates, or as in the example above, financial year references. Supported formats are Q1FY22, H1FY22 or FY22, and convert it to the appropriate financial year timestamp for storage. See above for valid date formats.

Saved Upload Configuration

Once the upload has successfully completed, the configuration (with the exception of the load id) will be saved for the next upload on the same datasource.