This article shows how to import data from CSV formatted files to your GI.bench or GI.cloud to view, store or analyze your data, or to export data to CSV formatted file from your GI.bench dashboard elements, for example charts.
General Settings
Define your general CSV settings separately for import/export within the Global Settings of your Dashboard Designer. There default import/export settings are set accordingly, to keep an export and import of the same formatted files easy.
Please pay attention that only numerical values can be imported. Any CSV file including non numerical values within the data fields will fail to import.
Import
When importing data, the data is handled as a “data stream”. This makes it possible to process the data similar as other data streams. Therefore, we also need some information for the created data stream, like stream name, uuid, or retention time.
Up to 10 different import settings can be defined and stored for later file imports. Set a suitable CSV config name for your import settings to identify for later use during import!
General Import Settings:
|
Parameter |
Description |
|---|---|
|
Source name |
Choose a human-readable name for the imported data stream. (Default: “CSV Import”) |
|
Source id |
UUID for the resulting data stream, used for identification (leave empty to generate a new one). Can be used for example to append data to an existing stream of previously imported data. |
|
Retention time sec (GI.bench/Q.core) |
Defines the resulting buffer capacity in seconds. (Default: 600) |
|
Retention time sec (GI.cloud) |
Defines the time in seconds for how long data should be stored in the backend. Set to 0 if infinite storage is needed. Depending on the backend cleanup policy, |
|
Session timeout sec |
Seconds after import session will be closed if inactive. (Default: 60) |
|
Sample rate |
Sample rate configuration to specify the stream sample rate (= sample rate of the CSV data) or set to -1 if sample rate should be detected automatically. |
|
Time offset |
Add a time offset to the imported data time stamps in seconds. The imported time stamps are considered to be UTC! |
CSV Import Settings:
To find out the correct settings for a CSV file import, please open first one of the files in a simple text editor (Notepad for example), to explore and identify the format.
|
Parameter |
Description |
|---|---|
|
CharSet |
Each text based file has a certain character set and encoding format. For a correct decoding, it is necessary to know this setting. |
|
Precision |
Set the precision for the imported data (number of digits right of the decimal point). Default: 3 |
|
Column Separator |
Specifies the string (1 or more characters) that separates columns |
|
Decimal Separator |
Specifies the character (1 char) used as a decimal separator for values |
|
Thousand Separator |
Specifies the character (1 char) used as a thousand separator for values (optional; default = “,”) -> Attention: if the ThousandSeparator is equal to DecimalSeparator, it will be automatically set to “.”. |
|
Name Row Index |
Index of the row that contains variable names (0..n). |
|
Unit Row Index |
Index of the row that contains variable units (0..n). |
|
Values Start Row Index |
Index of the first row that contains values to be imported (0..n) |
|
Values Start Column Index |
Index of the first column that contains values to be imported (0..n) |
|
Date Time Format Column 1 |
The data format of the 1st of up to 3 possible date/time columns (see table below) |
|
Date Time Format Column 2 |
The data format of the 2nd of up to 3 possible date/time columns (see table below) |
|
Date Time Format Column 3 |
The data format of the 3rd of up to 3 possible date/time columns (see table below) |
Example:
This example shows how to identify the parameters in a CSV file (take care: all indices are “0” based):
-
Name Row Index: 0
-
Values Start Row Index: 1
-
Values Start Column Index: 1
-
Unit Row Index: -1 (no units in this example)
-
Decimal Separator: “.”
-
Thousand Separator: not seen in the example. Use default “,”
-
Precision: 6 (can also be < 6)
-
Column Separator: “;”
-
Date Time Format Column 1: this example has 1 column for time information. Identify the correct format using the table below!
-
Date Time Format Column 2-3: leave empty.
The Date Time format string of all 3 format columns is used as a template to parse the date/time column. It can be a combination of the following placeholders:
|
Format string |
Description |
Example |
|---|---|---|
|
%w |
abbreviated weekday |
Mon, Tue, … |
|
%W |
full weekday |
Monday, Tuesday, … |
|
%b |
abbreviated month |
Jan, Feb, … |
|
%B |
full month |
January, February, … |
|
%d |
zero-padded day of month |
01 .. 31 |
|
%e |
day of month |
1 .. 31 |
|
%f |
space-padded day of month |
1 .. 31 |
|
%m |
zero-padded month |
01 .. 12 |
|
%n |
month |
1 .. 12 |
|
%o |
space-padded month |
1 .. 12 |
|
%y |
year without century |
70 |
|
%Y |
year with century |
1970 |
|
%H |
hour |
00 .. 23 |
|
%h |
hour |
00 .. 12 |
|
%a |
am/pm |
|
|
%A |
AM/PM |
|
|
%M |
minute |
00 .. 59 |
|
%S |
second |
00 .. 59 |
|
%s |
seconds and microseconds |
equivalent to %S.%F |
|
%i |
millisecond |
000 .. 999 |
|
%c |
centisecond |
0 .. 9 |
|
%F |
fractional seconds/microseconds |
000000 - 999999 |
|
%z |
time zone differential in ISO 8601 format |
Z or +NN.NN |
|
%Z |
time zone differential in RFC format |
GMT or +NNNN |
|
%% |
percent sign |
Only for DateTimeFmtColum1:
|
Format string |
Description |
Example |
|---|---|---|
|
%%ets |
epochtime in seconds |
1702394735 |
|
%%etm |
epochtime in milliseconds |
1702394735228 |
|
%%ot |
Time in OLE2 time format (time in days since 01.01.1900, usually in double format) |
45272.643535278 |
|
%%dt |
distributed clock time (nanoseconds since 01.01.2000, usually in uint64 format) |
755709968278000000 |
|
Format |
Value |
|---|---|
|
%d.%m.%Y %H:%M:%S.%F |
23.09.2020 07:54:42.000000 |
Export
These export settings are not available in GI.cloud. Use the “Export” menu in GI.cloud web-UI as explained in “From GI.cloud via GI.export”.
|
Parameter |
Description |
|---|---|
|
Header Text |
Header line(s) to be appended before data. Default: empty (no additional info added) |
|
Timestamp Header |
Specifies the text for the date/time column |
|
Timestamp Format |
Specifies the format of the date/time column (see table below) |
|
Column Separator |
The character to separate columns (default = “;”) |
|
Decimal Separator |
The decimal point character |
The timestamp can have these defined formats:
|
Format |
Description |
Example |
|---|---|---|
|
Date and Time |
2023-12-12 15:22:50 |
|
|
Date and Time (incl. us) |
2023-12-12 15:23:51.835000 |
|
|
Date |
2023-12-12 |
|
|
Epochtime (ms) |
time since 01.01.1970 |
1702394735228 |
|
DC Timestamp (ns) |
distributed clock time (nanoseconds since 01.01.2000, usually in uint64 format) |
755709968278000000 |
|
OLE2Time |
Time in OLE2 time format (time in days since 01.01.1900, usually in double format) |
45272.643535278 |
Import CSV Data
When CSV settings are configured correctly (see Import), the file import can be started. Generally, CSV files are imported via Dashboard using the “Upload File” button on the Dashboard Designer.
The “Upload File” window will open:
Select one or multiple continuous CSV files.
If files with CSV format are selected, the “CSV config” dropdown menu is active.
Select one of your predefined CSV Import configurations in the dropdown menu. The configuration MUST fit for the selected CSV files format!
By clicking the “Upload” button, the upload progress will start.
After successful import, all data (also from continous files) will be imported (or appended) to one resulting data stream (as configured in the settings). Depending on the platform (GI.bench or GI.cloud), we have a slightly different behaviour after upload:
GI.bench
The data stream will be visible after import in the “Streams” section of your Dashboard Designer beside other data streams on the system. Drag and Drop channels to any Charts element on the dashboard to visualize data.
Attention: the data stream is only available in the volatile memory (RAM)! After reboot of GI.bench, the stream is not available anymore. Data is not stored in the GI.bench file archive!
You can use the trash-bin button beside the data stream to remove the data immediately if not needed anymore.
The complete availability of your data stream after import might take some minutes in GI.cloud (please be patient). Please refresh your dashboard some times to update the source informations in the Dashboard Designer. When the data is completely loaded, you can Drag and Drop channels to any Charts element on the dashboard to visualize data.
The data stream is now stored in the cloud backend. Data will be available as long as defined with retention time setting.
You can use the trash-bin button beside the data stream to remove the data immediately if not needed anymore.
Export CSV Data
The CSV file export differs between your local GI.bench and GI.cloud.
From GI.bench via Dashboard
Before your first CSV file export, be sure to have set the export settings (see “CSV Export Settings”) according your needs.
Directly from ChartThe file export can be triggered from a “Chart” element. The current visualized (online) data in a chart is exported to a single file by clicking on the “Export options” button on the chart and select Save as csv.
Select the desired destination directory for the export. The default GI.bench export directory is:
c:\Users\Public\Documents\Gantner Instruments\GI.bench\data\files
The default file name of the exported file looks like:
ExportStream__0_2023-11-24_09-21-52_892000.csv
which is a similar string pattern as used for GI.bench data logger files, using
Stream name (default: ExportStream)
Index (“0” - cannot be changed)
Timestamp of first sample in the file
To predefine a more intuitive naming for the exported files, it is possible to define optional export settings per chart (stored with the chart). You can find these in the “Element Settings” of the chart:
“Export” -> “Naming”:
Stream name: define your own name for the exported data stream
Measurement name: define a measurement name for the exported measurement
Using these additinal export settings (as seen in the screenshot), the file name will result in:
StreamName_MeasName__0_2023-11-24_09-21-52_892000.csv
From “Markers” Window
Using Markers in a chart, it is possible to select a more precise time range for the data to be exported.
When moving the second marker, the “Markers” window will open. Directly in this window, you can define a “Measurement name” for the new export (this will overrule the setting of the chart).
From GI.cloud via GI.export
Configuration of GI.export
Selecting the Source, Variable(s), Resolution, and CSV formatting
First, choose a Source stream and Variables. Click “Add Parameter” to add the selected Variables.
Next, choose a Resolution. Example: A resolution of ‘15 min’ exports 15 minute intervals of data, as in 00:00:00 - 00:14:59, 00:15:00 - 00:29:59.
Then select the CSV Dialect (e.g., Excel (semicolon)), the Decimal Separator (e.g., . (dot)), and Aggregation (e.g., avg (mean)).
Selecting the Time period
Define the Time period as either Today, Yesterday, Last 7 days, Current month, Last month, or Custom. Select Custom to define the Time period using a date time picker.
If the Resolution is set to ‘15 min’ and the Time period’s ‘To date’ is set to ‘07:15’, then the last timestamp in the CSV file will be 7:00 since all values less than the ‘To date’ and greater than or equal to the ‘From date’ are exported. (‘From date’ ≤ Timestamp < ‘To date’)
Export example
Click the “Export” button at the bottom of the GI.export window to export the selected data.
