You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 12 Next »

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. 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 look like:

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>

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.

  • No labels

Disclaimer