In this article, you're going to learn how does the Multicolumn CSV gateway, available on the EMS Platform, work.
Contents of this article:
- What is a Multicolumn gateway?
- Register a Multicolumn CSV gateway in the EMS platform
- Preparing a Multicolumn CSV file
What is a Multicolumn CSV gateway?
The multi-column CSV gateway allows data to be inserted into the platform using multi-column CSV format files, arranging each day's data in columns. This gateway allows you to adapt and map your multicolumn data files in a personalized way, as explained here. These files can be inserted through the Multicolumn CSV gateway via Email or FTP.
Register a Multicolumn CSV gateway in the EMS
To register this gateway, follow these steps and select the Multicolumn CSV gateway (FTP or email).
Once in the settings of the gateway, choose a name and select your timezone. Both FTP and email Multicolumn gateways will list the connection details in them, which work just like their generic FTP and email counterparts. Use the connection details listed to send your data.
Once in the Gateway Configuration fill in the following fields:
- Name: chose the gateway's name.
- Time zone: select the corresponding time zone.
- Insertion mode: Discrete or Accumulated. More information on this section.
- Date format: Local or UTC.
- Date column: Indicate the character of the csv file column where the date, the day, is indicated.
- Meter column: Indicate the character of the csv file column where the meter name is indicated.
- Units or Parameter column: Indicate the character of the csv file column where the parameter ID is indicated. In case you don't have any column with the parameters select "None", and in the Default parameter select the desired parameter, such as Active Energy (402), Gas (420) or Water (901), more to come! More information about parameter IDs here.
- First data column: Indicate the character of the csv file column where data begins.
- First data row: Indicate the number of the row of the csv file column where data begins.
Note: If you select "None" in the Default parameter field, our platform will select Active Energy (402) by default. Hence, make sure to select the desired parameter.
Learn how to configure the CSV file on this section with an example.
Inserting data through a Multicolumn CSV gateway
3 modes are available to select:
- Time interval type values (Discrete data): In this mode, the gateway will insert the consumption for each period as it is listed in the CSV file sent.*
- Time interval type values: In this mode, the gateway will accumulate the consumption data for each day, and insert the accumulated reading in the platform, assuming that it is reset to 0 every day at 00:00. (ex.: 15min electrical consumption, 30min water consumption).
- Instantaneous/cumulated values: For imported data with accumulated values (ex.: 15min electrical consumption added over time, and inserted in that way).
Parameters with nature type "instantaneous" (such as temperature, power, etc..) will only be inserted in insertion mode "instantaneous/cumulated values, insert as such"
If you send hourly data with the Discrete insertion mode the gateway will calculate and insert the values in 30 min frequency by dividing the hourly value by 2.
Example: Imagine that you have an hourly value of 10kWh at 01:00, which means that your consumption between 01:00 and 02:00 is 10kWh. The multicolumn CSV parser will divide this value by 2 (interpolate it) and will insert in DEXMA:
- 5kWh at 01:30
- 5kWh at 02:00
This process is done so that you are able to assign cost to your devices, as the 30 min frequency is required.
To keep in mind
Prepare a CSV Multicolumn file
The file must have the format CSV comma-separated.
The gateway accepts files that have data every 15 min, 30 min or hour, so it will need exactly 24, 48 or 96 columns for the readings, respectively.
The file must contain the following fields in order for the CSV information to correspond to the configuration of the gateway.
Meter name: Indicate the names of the meters in a column.
Date: Indicate the date, in D/M/Y format, of the day whose data in each row correspond. (Ex: 21/02/2021 or 21/02/21).
Parameter identifier: Indicate the key or parameter identifier of the data you are inserting. Learn how to indicate the parameter here.
Rows and columns of data: Each day's data readings are added by row. Meaning, one day of data corresponds to one row. In this row, you have to indicate in three of the columns the name of the meter, the parameter identifier and the date. The data will occupy the next 24, 48 or 96 columns, depending on the data frequency, to this information. The format of the data must be a number with a decimal separator "." or ",".
There are several options to indicate the parameter being inserted. Firstly, to insert any type of parameter available to the platform you must indicate its identifier (ID), available in this list.
Indicating the parameter ID of active energy is optional. If you do not use any column to indicate it in the configuration of the CSV, then you would indicate the option None in the option of Column of units or parameters on the Gateway configuration.
In case the parameter to be inserted is reactive energy or capacitive reactive energy the following keys can also be used as Parameter ID:
- LAG: Reactive Energy (parameter 404)
- LEAD: Capacitive Reactive Energy (parameter 408)
Here is an example to illustrate how to set up the gateway from the CSV file. Here you can see an example of CSV file:
In the example file:
- Meter name: Column A (meter 1, meter 2)
- Date: Column C (01/11/2020...)
- Parameter identifier: Column B (402, Active energy)
- First data column: Column D
- First row containing data: row 2
The number of columns containing data is 24, therefore the frequency of data is hourly.