Overview
Imports time series data from files in CSV format with one header line containing a column heades of the time series:
- The first line contains the column names (fields) in the csv file, the line is used to determine the field separator and to determine the names of the data columns
- All other lines contain the date-time as field and the values for each time series.
- Values between -1000.0 and -999.0 (inclusive) are regarded as missing values.
The CSV files can be supplied in a ZIP file.
Import type
The import type is generalCSV. There is no particular file extension required.
Example
Here is a simple example:
Time,Waterstand,Pomp-1 Born 04-05-2011 03:24,0.000000,-0.450000 04-05-2011 03:44,0.000000,-0.450000 04-05-2011 03:54,0.000000,-0.440000 .....
for configuration of the table layout see Table Layout
<?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 http://fews.wldelft.nl/schemas/version1.0/timeSeriesImportRun.xsd"> <import> <general> <importType>generalCSV</importType> <folder>$IMPORT_FOLDER$/OBS</folder> <failedFolder>$IMPORT_FAILED_FOLDER$</failedFolder> <backupFolder>$IMPORT_BACKUP_FOLDER$/OBS</backupFolder> <table> <dateTimeColumn name="Time" pattern="dd-MM-yyyy HH:mm"/> <valueColumn unit="m" locationId="Bosscheveld" parameterId="H.meting" name="Waterstand"/> <valueColumn unit="min" locationId="Bosscheveld" parameterId="DT.meting" name="Pomp-1 Born"/> </table> <idMapId>IdImportOBS</idMapId> <unitConversionsId>ImportUnitConversions</unitConversionsId> <importTimeZone> <timeZoneOffset>+00:00</timeZoneOffset> </importTimeZone> </general> </import> </timeSeriesImportRun>
Another example, skipFirstLinesCount
Example to read from CSV files where unfortunately the first contains a key instead of the column headers.
The CSV files look like:
[DATA] Tagname,TimeStamp,Value,DataQuality WDD.MEM_BER0001_01_LT01_MW,2010-04-01 12:21:00,-0.000,GOOD WDD.MEM_BER0001_01_LT01_MW,2011-01-12 10:34:05,-0.001,GOOD WDD.MEM_BER0001_01_LT01_MW,2011-01-12 10:35:00,-0.011,BAD WDD.MEM_BER0001_01_LT01_MW,2011-01-12 10:36:00,-0.003,GOOD WDD.MEM_BER0001_01_LT01_MW,2011-01-12 10:37:00,-0.000,GOOD WDD.MEM_BER0001_01_LT01_MW,2011-01-12 10:38:00,-0.000,GOOD WDD.MEM_BER0001_01_LT01_MW,2011-01-12 10:39:00,-0.000,GOOD
In this example the first line should be skipped, so skipFirstLinesCount = 1
<general> <importType>generalCSV</importType> <folder>$IMPORT_FOLDER_KETEN$</folder> <fileNamePatternFilter>*.csv</fileNamePatternFilter> <failedFolder>$IMPORT_FAILED_FOLDER_KETEN$</failedFolder> <backupFolder>$IMPORT_BACKUP_FOLDER_KETEN$</backupFolder> <table> <dateTimeColumn name="TimeStamp" pattern="yyyy-MM-dd HH:mm:ss"/> <locationColumn name="Tagname"/> <flagColumn name="DataQuality"/> <valueColumn name="Value" unit="SI"/> </table> <idMapId>IdKETEN</idMapId> <flagConversionsId>ImportKETENFlagConversions</flagConversionsId> <importTimeZone> <timeZoneOffset>+01:00</timeZoneOffset> </importTimeZone> <dataFeedId>CSV files</dataFeedId> <skipFirstLinesCount>1</skipFirstLinesCount> </general>
Another example, no header at all (since 2012.02)
The CSV files look like:
WDD.MEM_BER0001_01_LT01_MW,A,2010-04-01 12:21:00,-0.000,GOOD WDD.MEM_BER0001_01_LT01_MW,A,2011-01-12 10:34:05,-0.001,GOOD WDD.MEM_BER0001_01_LT01_MW,A,2011-01-12 10:35:00,-0.011,BAD WDD.MEM_BER0001_01_LT01_MW,A,2011-01-12 10:36:00,-0.003,GOOD WDD.MEM_BER0001_01_LT01_MW,A,2011-01-12 10:37:00,-0.000,GOOD WDD.MEM_BER0001_01_LT01_MW,A,2011-01-12 10:38:00,-0.000,GOOD WDD.MEM_BER0001_01_LT01_MW,A,2011-01-12 10:39:00,-0.000,GOOD
<general> <importType>generalCSV</importType> <folder>$IMPORT_FOLDER_KETEN$</folder> <fileNamePatternFilter>*.csv</fileNamePatternFilter> <failedFolder>$IMPORT_FAILED_FOLDER_KETEN$</failedFolder> <backupFolder>$IMPORT_BACKUP_FOLDER_KETEN$</backupFolder> <table> <locationColumn/> <skippedColumn/> <dateTimeColumn pattern="yyyy-MM-dd HH:mm:ss"/> <valueColumn unit="SI"/> <flagColumn/> </table> <idMapId>IdKETEN</idMapId> <flagConversionsId>ImportKETENFlagConversions</flagConversionsId> <importTimeZone> <timeZoneOffset>+01:00</timeZoneOffset> </importTimeZone> <dataFeedId>CSV files</dataFeedId> </general>
Samples Import
For the import of ecological data based on samples, the general csv import is used extensively because there is a lot of information per timeseries.
Besides columns for location, parameter, date/time, value and unit, there is a column for sample id and multiple columns for qualifiers and sample properties.
The CSV files have just 1 time series per line, for example:
SMP_CODE;SMP_NAME;COORD_X_S;COORD_Y_S;ANAL_CODE;EMP_NR_S;PROD_CODE;METH_SAMP;METH_ANAL;EMP_NR_A;LOC_CODE;DATE_SMP;PAR_REF;waarde;eenheid;BRON;parameter_id;type 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Tubifex tubifex;2;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Tubificidae;2;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Hypania invalida;5;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Gammarus tigrinus;10;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Dicrotendipes pulsus;3;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Microtendipes chloris agg.;3;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Polypedilum;5;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Procladius;1;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Pseudochironomus prasinatus;1;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Stictochironomus;1;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Ceratopogonidae;3;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Caenis horaria;1;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Ecnomus tenellus;1;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Corbicula fluminea;6;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Dreissena polymorpha;268;n;MACEV2010;AANTL_MEA;gemeten 3110040601;Spiegelplas 4;133054;475032;MEA;WP_CAS_ZUY;OW;Van Veenhapper;SPV7030;WP_CAS_ZUY;SBP130;06-04-10 00:00;Potamopyrgus antipodarum;1;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Piscicola;1;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Tubificidae;1;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Hygrobates longipalpis;1;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Piona;1;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Chelicorophium curvispinum;23;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Gammarus tigrinus;22;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Asellus aquaticus;3;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Limnomysis benedeni;29;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Ablabesmyia;1;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Chironomus;5;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Chironomus riparius agg.;9;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Clinotanypus nervosus;4;n;MACEV2010;AANTL_MEA;gemeten 3110040602;Spiegelplas 1;132546;476323;MEA;WP_CAS_ZUY;OW;macrofaunanet;SPV7030;WP_CAS_ZUY;SBP175;06-04-10 00:00;Corynoneura scutellata agg.;2;n;MACEV2010;AANTL_MEA;gemeten
With corresponding import xml:
<general> <importType>generalCSV</importType> <folder>$IMPORT_FOLDER$/HydroBiologie</folder> <table> <dateTimeColumn name="DATE_SMP" pattern="dd-MM-yyyy HH:mm"/> <locationColumn name="LOC_CODE"/> <unitColumn name="Eenheid"/> <parameterColumn name="PARAMETER_ID"/> <qualifierColumn name="PAR_REF" prefix="PAR_REF_"/> <qualifierColumn name="PROD_CODE" prefix="PROD_CODE_"/> <qualifierColumn name="ANAL_CODE" prefix="ANAL_CODE_"/> <qualifierColumn name="METH_ANAL" prefix="METH_ANAL_"/> <qualifierColumn name="METH_SAMP" prefix="METH_SAMP_"/> <qualifierColumn name="TYPE" prefix="TYPE_"/> <sampleIdColumn name="SMP_CODE"/> <propertyColumn name="SMP_NAME" key="SMP_NAME"/> <propertyColumn name="COORD_X_S" key="COORD_X_S"/> <propertyColumn name="COORD_Y_S" key="COORD_Y_S"/> <propertyColumn name="EMP_NR_S" key="EMP_NR_S"/> <propertyColumn name="EMP_NR_A" key="EMP_NR_A"/> <propertyColumn name="BRON" key="BRON"/> <valueColumn name="Waarde"/> </table> <idMapId>IdImport_HydroBiologie</idMapId> <dataFeedId>generalCSV</dataFeedId> </general>
Multiple qualifiers are used per time series divided over different colums. The column a qualifier originated from is important to keep track of. That is why a "prefix" attribute can be configured for a qualifier column. This prefix is added to the content of the column to form the external qualifier id.
With the use of id mapping, this prefix helps mapping to the correct qualifiers ssing a qualifier function <qualifierIdFunction externalQualifierFunction="@ExternalQualifierId@"/> and qualifiers configured in a csv file:
<csvFile> <file>par_ref_qualifiers.csv</file> <id>%qualifierId%</id> <name>%taxonname%</name> <attribute id="ExternalQualifierId"> <text>PAR_REF_%taxonname%</text> </attribute> </csvFile>
The content out of the qualifier column with prefix "PAR_REF_" will in this way be used to map to a qualifier in par_ref_qualifiers.csv that has equal content as "taxonname".
In the same way the content out of the qualifier column with prefix "ANAL_CODE_" will be mapped to a qualifier in analyse_code.csv that has equal content as "qualifierId" using the next qualifier configuration:
<csvFile> <file>analyse_code.csv</file> <id>%qualifierId%</id> <name>%qualifierName%</name> <attribute id="ExternalQualifierId"> <text>ANAL_CODE_%qualifierId%</text> </attribute> </csvFile>
Details of the import format
If the first line contains a comma, the decimal separator is taken to be a period (.), otherwise it is supposed to be a semicolon (;) and the decimal separator is taken to be a comma. This way locale-specific CSV files are supported.
The field separator is either a comma or a semicolon. Tabs are not supported.