Contents
Overview
Imports time series data from the Microsoft Access database file.
There are two types of the import depending on type of measurement station:
- analog - old data format
- digital - new data format
The difference between two formats in how time series are stored in the database.
For analog data type it is .mdb file containing a set of tables where each separate time series is stored in a separate table.
In case of digital data type one table in the tmx mdb file can contain many time series.
What does tmx abbreviation means?
There is no flag column when data type is digital?
Status
Both analog and digital data format can be used.
Configuration (Example)
The configuration files below define import of 4 time series from the tmx .mdb file:
Data Format |
Parameter (tmx) |
Location (tmx) |
Parameter (fews) |
Location (fews) |
|---|---|---|---|---|
analog |
Ai1 |
Loc063 |
P1.m |
tmx_location1 |
analog |
Ao1 |
Loc063 |
P2.m |
tmx_location2 |
digital |
1 |
46 |
P3.m |
tmx_location3 |
digital |
1 |
51 |
P3.m |
tmx_location4 |
Note
Tmx database (mdb) may contain data for both digital and analog types of data.
When data are in analog format they usually stored in tables which names are defined using location and parameter name, e.g.: Loc063Ao1, Loc063Ai1. In case of digital format everything is stored in one table, e.g.: ReportAo or ReportDi. When in digital tables other columns than the regular ActualValue column must be imported the externalParameterQualifier can be used to indicate the correct table; ReportDi_Open to import the MinutesOpen column of the ReportDi table.
ModuleConfigFiles/
Time series which are listed in this file can be imported into fews.
Defines what time series can be imported from the TMX .mdb file and which tables contain their values.
<?xml version="1.0" encoding="UTF-8"?>
<timeSeriesImportRun xmlns="http://www.wldelft.nl/fews" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.wldelft.nl/fews/schemas/version1.0/timeSeriesImportRun.xsd">
<import>
<general>
<importType>Tmx</importType>
<folder>../junit_test_output/nl/wldelft/fews/system/plugin/dataImport/TimeSeriesImportTestData/import/tmx</folder>
<idMapId>tmxMapId</idMapId>
<importTimeZone>
<timeZoneOffset>+01:00</timeZoneOffset>
</importTimeZone>
</general>
<!-- Analog, table = Loc063Ai1 -->
<timeSeriesSet>
<moduleInstanceId>ImportTmx</moduleInstanceId>
<valueType>scalar</valueType>
<parameterId>P1.m</parameterId> <!-- parameter = Ai1 -->
<locationId>tmx_location1</locationId> <!-- location = Loc063 -->
<timeSeriesType>external historical</timeSeriesType>
<timeStep unit="minute" multiplier="15"/>
<relativeViewPeriod unit="day" start="0" end="11"/>
<readWriteMode>add originals</readWriteMode>
</timeSeriesSet>
<!-- Analog, table = Loc063Ao1 -->
<timeSeriesSet>
<moduleInstanceId>ImportTmx</moduleInstanceId>
<valueType>scalar</valueType>
<parameterId>P2.m</parameterId> <!-- parameter = Ao1 -->
<locationId>tmx_location1</locationId> <!-- location = Loc063 -->
<timeSeriesType>external historical</timeSeriesType>
<timeStep unit="day" multiplier="1"/>
<relativeViewPeriod unit="day" start="0" end="11"/>
<readWriteMode>add originals</readWriteMode>
</timeSeriesSet>
<!-- Digital, table = ReportAo, defined in the mapping -->
<timeSeriesSet>
<moduleInstanceId>ImportTmx2</moduleInstanceId>
<valueType>scalar</valueType>
<parameterId>P3.m</parameterId> <!-- Channel = 1 -->
<locationId>tmx_location2</locationId> <!-- LocCode = 46 -->
<timeSeriesType>external historical</timeSeriesType>
<timeStep unit="day" multiplier="1"/>
<relativeViewPeriod unit="day" start="400" end="470"/>
<readWriteMode>add originals</readWriteMode>
</timeSeriesSet>
<!-- Digital, table = ReportAo, defined in the mapping -->
<timeSeriesSet>
<moduleInstanceId>ImportTmx</moduleInstanceId>
<valueType>scalar</valueType>
<parameterId>P3.m</parameterId> <!-- Channel = 1 -->
<locationId>tmx_location3</locationId> <!-- LocCode = 51 -->
<timeSeriesType>external historical</timeSeriesType>
<timeStep unit="day" multiplier="1"/>
<relativeViewPeriod unit="day" start="400" end="470"/>
<readWriteMode>add originals</readWriteMode>
</timeSeriesSet>
</import>
</timeSeriesImportRun>
IdMapFiles/
Defines mappings between TMX and FEWS parameters and locations.
<idMap xmlns="http://www.wldelft.nl/fews" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.wldelft.nl/fews ..\..\..\..\..\..\..\..\..\..\..\xml-schemas\idMap.xsd" version="1.1">
<!-- analog -->
<map internalParameter="P1.m" internalLocation="tmx_location1" externalParameter="Ai1" externalLocation="Loc063" />
<map internalParameter="P2.m" internalLocation="tmx_location1" externalParameter="Ao1" externalLocation="Loc063" />
<!-- digital -->
<map internalParameter="P3.m" internalLocation="tmx_location2" externalParameter="1" externalLocation="46" externalParameterQualifier="ReportAo" />
<map internalParameter="P3.m" internalLocation="tmx_location3" externalParameter="1" externalLocation="51" externalParameterQualifier="ReportAo" />
</idMap>
Import of TMX in CSV file format
The importtype is called TmxCsv
1. Missing values
If string can not be parsed as a number it is assumed as a missing value,
e.g.: "---", "???", ">>>", "<<<"
Example:
Datum;Tijd;Ai8;Ai1;Ai3;Ai2;Ai7;Ai9
;;Gem;Gem;Gem;Gem;Gem;Gem
25-05-2005;12:00;16.257;15.958;16.135;15.026;15.513
25-05-2005;13:00;16.257;15.958;16.135;15.026;15.507
25-05-2005;14:00;--;-;-;-;--
25-05-2005;15:00;16.257;15.958;16.135;15.026;15.494
...
09-01-2006;01:00;1.648;?;?;?;?;???
09-01-2006;02:00;0.399;12.743;12.606;12.333;?;?
...
27-08-2007;01:00;>>>;>>>;>>>;>>>;>>>;>>>
27-08-2007;02:00;<<<;<<<;<<<;<<<;<<<;<<<
...
2. Date and time format
It is assumed that date and time comes always in the "DD-MM-YYYY hh:mm" format. Since december 2008 also the time format of "DD-MM-YYYY hh:mm:ss" (with seconds) supported.
TimeZone can be used in the configuration file to set an offset.
3. 2nd line
2nd line containing ";;Gem;Gem;Gem;Gem;Gem;Gem" is always skipped.
4. Reader also assumes Date and Time always come as a 1st and 2nd columns.
5. File naming convention
TMX CSV files to be imported by FEWS should be in the following format:
<location> anything else.csv
- Location should be at the first position and should be the same as location defined in the idMap.xml
- Location must be separated by " " - space from the rest of file or ".csv", for example:
File Name |
Valid |
|---|---|
location1 01-01-2007.csv |
|
location1.csv |
|
location1 .csv |
|
location1_01012007.csv |
|
location101012007.csv |
|
6. Two new warnings have been added to this import:
The systems gives a warning when data in the database is overwritten by new data:
- 29.02.2008 08:52:04 WARN - Timeseries.Changed: ModuleInstance=ImportTMX, locationId=0002_boven, parameterId=H.meting, timestep=10minutes, period=2006-01-01 00:40:00 - 2006-01-31 23:10:00, number of timesteps changed 744
The system gives a warning when multiple series are imported for one location-parameter combination:
- 29.02.2008 09:07:45 WARN - Multiple time series sets found for parameter-internal/external=H.meting/Ai2 location-internal/external=WAM0400_afwat_kan/WAM0400 ensemble member main$0
