TSTool-Vol2-CommandReference - Laserfiche WebLink

sort order will be used. Top Indicate that Top rows should be returned. This functionality may not be implemented for all databases (SQL is not fully standardized for this feature). This parameter is useful to determine the columns for a table prior to using the Sql or SqlFile parameters. Return all rows. Sql The SQL string that will be used to query the database, optionally using ${Property} notation to insert processor property values. If specified, do not specify DataStoreTable or SqlFile. None. SqlFile The name of the file containing an SQL string to execute, optionally using ${Property} notation in the SQL file contents to insert processor property values. If specified, do not specify DataStoreTable or Sql. None. DataStoreProcedure The name of the database procedure to run. Currently, only procedures that do not require parameters can be run. None. TableID Identifier to assign to the output table in TSTool, which allows the table data to be used with other commands. A new table will be created. None – must be specified. 344 Command Reference: ReadTableFromDBF() Read a table from a dBASE file Version 09.09.00, 2010-09-23 The ReadTableFromDBF() command reads a table from a dBASE file, such as the files used with ESRI GIS shapefiles. dBASE files are self-contained binary database files. Handling of dBASE files is limited and support for newer features may not be included. The following dialog is used to edit the command and illustrates the syntax for the command. ReadTableFromDBF ReadTableFromDBF() Command Editor 345 Command Reference – ReadTableFromDBF() -1 ReadTableFromDBF() Command TSTool Documentation The command syntax is as follows: ReadTableFromDBF(Parameter=Value,…) Command Parameters Parameter Description Default TableID Identifier to assign to the table that is read, which allows the table data to be used with other commands. None – must be specified. InputFile The name of the file to read, as an absolute path or relative to the command file location. None – must be specified. Command Reference – ReadTableFromDBF() -2 346 Command Reference – ReadTableFromDelimitedFile() -1 Command Reference: ReadTableFromDelimitedFile() Read a table from a delimited file Version 10.03.00, 2012-01-09 The ReadTableFromDelimitedFile() command reads a table from a comma-delimited file. Tables are used by other commands when performing lookups of information or generating summary information from processing. Table files have the following characteristics: • Comments indicated by lines starting with # are stripped during the read. • Extraneous lines in the file can be skipped during the read using the SkipLines parameter. • Column headings indicated by “quoted” values in the first non-comment line will be used to assign string names to the columns. If no quoted values are present, columns will not have headings. • Data in columns are assumed to be of consistent type (i.e., all numerical data or all text), based on rows after the header. The data type for the column will be determined automatically. • Missing values can be indicated by blanks. However, a line ending with the delimiter may cause warnings because blank is not assumed at the end of the line (this is a software limitation that may be addressed in the future) – work around by adding an extra delimiter or ensure that the last column is not blank. • Strings containing the delimiter should be surrounded by double quotes. This command currently does not deal with ”””text””” notation although support may be added in the future (see information about comma-separated-value [CSV] standards: http://en.wikipedia.org/wiki/Commaseparated_values). The following dialog is used to edit the command and illustrates the syntax for the command. 347 ReadTableFromDelimitedFile() Command TSTool Documentation Command Reference – ReadTableFromDelimitedFile() -2 ReadTableFromDelimitedFile ReadTableFromDelimitedFile() Command Editor The command syntax is as follows: ReadTableFromDelimitedFile(Parameter=Value,…) Command Parameters Parameter Description Default TableID Identifier to assign to the table that is read, which allows the table data to be used with other commands. None – must be specified. InputFile The name of the file to read, as an absolute path or relative to the command file location. None – must be specified. SkipLines Indicates the number of lines in the file to skip, which otherwise would interfere with reading row data. Individual row numbers and ranges can be specified, for example: 1,5-6,17 No lines are skipped. HeaderLines Indicate the rows that include header information, which should be used for column names. Currently this should only be one row, although a range may be fully supported in the future. If the first non-comment line contains quoted field names, they are assumed to be headers. Otherwise, no headers are read. 348 TSTool Documentation ReadTableFromDelimitedFile() Command Command Reference – ReadTableFromDelimitedFile() -3 The following example command file illustrates how to read a table from a delimited file: ReadTableFromDelimitedFile(TableID="Table1", InputFile="Sample.csv",SkipRows="2") An excerpt from a simple delimited file is: # A comment some junk to be skipped “Header1”,”Header2” ,”Header3” 1,1.0,1.0 2,2.0,1.5 3,3.0,2.0 349 ReadTableFromDelimitedFile() Command TSTool Documentation Command Reference – ReadTableFromDelimitedFile() -4 This page is intentionally blank. 350 Command Reference – ReadTableFromExcel() -1 Command Reference: ReadTableFromExcel() Read a cell range from a Microsoft Excel file and create a new Table Version 10.18.00, 2012-02-25 The ReadTableFromExcelFile() command reads a table from a Microsoft Excel file, more specifically from a worksheet in an Excel workbook file. A contiguous block of cells (rectangle) must be specified in one of the following ways: • Specify a range of cells using Excel address notation (e.g., A1:D10) (TODO – figure out if worksheet can be specified in this address, in which case the Worksheet parameter is not required). • Specify the name of an Excel named range. • Specify a table name (essentially a named range). Table column types (number, text, etc.) are determined from the cells in the first data row being read (NOT the column name row) – data types must be consistent for all cells in a column, although blanks are allowed. Table column names are determined according to the ExcelColumnNames command parameter. TSTool uses the Apache POI software, version 3.9 (http://poi.apache.org) to read the Excel file and consequently functionality is constrained by the features of that software package. The software reads and writes Excel files. It does not communicate with a running Excel program, as does other software tools (for example IronPython using Excel interoperability libraries). POI does not fully implement Excel functionality and consequently some formula capabilities are not available, which will generate errors getting values for some cells. One solution, for example to create test data in Excel, is to copy cells with “paste special” and then paste the values. It is expected that updates to POI will continue to add more formula support. Table columns must contain consistent data types (all strings, all numeric, etc.). The following table describes how column types are determined and data values are transferred to the table. Column type determination uses the first data row in the specified address range. If a column is determined to be a type and then cell values in the column are different, conversions are made to maintain the intent of the values if possible. For example, a Boolean value stored in a cell will get converted to 1.0 if the table column has been determined to be for double precision numbers. Errors in processing cells may result in empty cell values in the output table. Excel Data Type Conversion to Table Excel Cell Format (“Number Category” Conversion from Excel to TSTool Table Number: • General • Number • Currency • Accounting • Percentage • Fraction • Scientific • Special • Custom • If Excel cell is internally a “numeric”, convert to a double-precision number, where the format “Decimal places” is used in the TSTool table for formatting. The number of decimal places in Excel is fixed for some of the number categories shown on the left (e.g., Special=Zip Code). Excel internally stores integers as numbers with zero decimals. Need to figure out how to get the Excel cell formatting number of decimals to similarly set in the output table – but DO NOT assume zero decimals should convert to an integer. • See the ExcelIntegerColumns parameter, which specifies the output table to use integers. • If Excel cell is internally a “Boolean”, convert to an integer having 351 ReadTableFromExcel() Command TSTool Documentation Command Reference – ReadTableFromExcel() -2 Excel Cell Format (“Number Category” Conversion from Excel to TSTool Table values 0 or 1. Need to evaluate having a parameter ExcelBooleanColumns to transfer to a Boolean column in the output table. Excel seems to handle Booleans as text with values True or False. Date: • Date • Time TSTool does not generally deal with only time and therefore implementation is limited. The POI library does not seem to have all date/time functions implemented. Text: • Text Converts to a string. Blank • Treated as Text (may in the future scan down the column to determine data type from first non-blank cell). • Blank cells found once the column type is determined are set to empty strings in text columns, and null in number and date columns. Error • Treated as Text (may in the future scan down the column to determine data type from first non-error cell). • Blank cells found once the column type is determined are set to empty strings in text columns, and null in number and date columns. Formula Expanded internally and the resulting cell value is set in the output table. POI does not support all formulas and errors may be generated, which result in empty output table cells. Consider the following Excel worksheet example, which is equivalent to a comma-separated-value (CSV) file that has comments at the top and four columns: ReadTableFromExcel_SheetComments Example Excel Workshet With Comments, Column Names, and Text and Integer Columns Although it is possible to use comments in Excel (annotation on cells), these comments cannot be saved in simple text files like CSV files. Consequently, for transparency and automation of a full process, embedding comments in the worksheet may make sense. Note also that the numeric cells are formatted as type “Number” with 0 decimals in Excel. Internally, Excel does not have an integer data type and consequently it is difficult for the ReadTableFromExcel() command to know when to convert a zero-decimal number in Excel to a floating point or integer number in the output table (it therefore defaults to a floating point number in output). To make this conversion more explicit, use the ExcelIntegerColumns command parameter. The comment lines in the above example will be ignored in determining the headings, and any data rows that have a first cell value starting with the comment character will be ignored. 352 TSTool Documentation ReadTableFromExcelFile() Command Command Reference – ReadTableFromExcelFile() -3 The following dialog is used to edit the command and illustrates the syntax for the command when reading the above Excel worksheet. ReadTableFromExcel ReadTableFromExcel() Command Editor The command syntax is as follows: ReadTableFromExcelFile(Parameter=Value,…) Command Parameters Parameter Description Default TableID Identifier to assign to the table that is read, which allows the table data to be used with other commands. None – must be specified. InputFile The name of the Excel workbook file (*.xls or *.xlsx) to read, as an absolute path or relative to the command file location. None – must be specified. Worksheet The name of the worksheet in the workbook to read. Currently this is required if a specific sheet is read but in the future it may be made optional because Read the first worksheet. If no address parameter is specified, read the entire worksheet. 353 ReadTableFromExcel() Command TSTool Documentation Command Reference – ReadTableFromExcel() -4 Parameter Description Default the sheet can be determined from named range and table names (global resources in the workbook) and absolute Excel addresses that include the sheet name. ExcelAddress Indicates the block of cells to read into the table, using Excel address notation (e.g., A1:D10). Must specify address using one of available address parameters. ExcelNamedRange Indicates the block of cells to read into the table, using an Excel named range. Must specify address using one of available address parameters. ExcelTableName Indicates the block of cells to read into the table, using an Excel named range. Must specify address using one of available address parameters. ExcelColumnNames Indicate how to determine the column names for the table, one of: • ColumnN – column name will be Column1, Column2, etc. • FirstRowInRange – column names are taken from the first noncomment row in the address range • RowBeforeRange – column names are taken from the first non-comment row before the address range ColumnN, or FirstRowInRange when ExcelTableName is specified? Comment Specify the character that if found at the start of the first column in a row (not just the specified address range) indicates that the row is a comment and can be ignored in transferring data to the output table. Comments are particularly useful when processing entire data sheets. No comments are used. ExcelIntegerColumns Indicate the names of columns (separated by commas) that should be treated as integer columns in the output table. Numeric columns are treated as double-precision values in the output table. ReadAllAsText Indicate with True or False whether all columns in the Excel address block should be treated as text columns. False – set table column types using the first data row 354 Command Reference – ReadTimeSeries() -1 Command Reference: ReadTimeSeries() Read a single time series using a full time series identifier Version 10.21.00, 2013-05-17 The ReadTimeSeries()reads a single time series using the time series identifier to uniquely identify the time series. This generalized command is useful for converting time series identifiers from the TSTool interface into read commands that assign an alias to a time series. Because the command is generic, it does not offer specific parameters that may be found in read commands for specific input types. Use the specific read commands where available for additional functionality and more specific error handling. See also the ReadTimeSeriesList() command. The following dialog is used to edit the command and illustrates the syntax of the command. ReadTimeSeies ReadTimeSeries() Command Editor 355 ReadTimeSeries() Command TSTool Documentation Command Reference – ReadTimeSeries() -2 The command syntax is as follows: ReadTimeSeries(Parameter=Value,…) The following older command syntax is updated to the above syntax when a command file is read: TS Alias = ReadTimeSeries (Parameter=Value,…) Command Parameters Parameter Description Default TSID The time series identifier of the time series to read. The identifier should include the input type (and input name, if required). See the input type appendices for examples of time series identifiers for various input types. None – must be specified. Alias The alias to assign to the time series, as a literal string or using the special formatting characters listed by the command editor. The alias is a short identifier used by other commands to locate time series for processing, as an alternative to the time series identifier (TSID). None – must be specified. IfNotFound Indicates how to handle missing time series, one of: • Warn – generate fatal warnings and do not include in output. • Ignore – generate non-fatal warnings and do not include in output. • Default – generate non-fatal warnings and create empty time series for those that could not be found. This requires that a SetOutputPeriod() command be used before the command to define the period for default time series. Warn DefaultUnits Default units when IfNotFound=Default. Blank – no units. A sample command file to read data from the State of Colorado’s HydroBase is as follows: ReadTimeSeries(TSID="08235350.USGS.Streamflow.Day~HydroBase",Alias=TS1) 356 Command Reference – ReadTimeSeriesList() -1 Command Reference: ReadTimeSeriesList() Read one or more time series using location identifiers from a table Version 10.21.00, 2013-05-17 The ReadTimeSeriesList() command reads one or more time series using location identifiers from a table, an example of which is shown below as a comma-separated value file: # Example list file. Comments start with the # character. # Column headings can be specified in the first non-comment row using quotes. “Structure ID”,”Structure Name” 500501,Ditch 501 500502,Ditch 502 # Invalid ID (see IfNotFound parameter) 509999,Ditch 9999 The command typically is used when reading time series from a single source and can streamline processing in the following situations: • A list of identifiers may have been generated from a database query • A list of identifiers may have been extracted from a model data set TSTool uses the location identifiers in the table with the command parameters and internally creates a list of time series identifiers. The time series are of the standard form: Location.DataSource.DataType.Interval[.Scenario]~DataStore [~InputName] where the brackets indicate optional information. TSTool then queries each time series, which can be processed further by other commands. See also the ReadTimeSeries() command, which performs essentially the same functionality but only reads one time series. Although it is possible to specify a datastore (or “input type”) that reads from files by also using the InputName, this is not generally recommended because the ReadTimeSeriesList() command can only specify one input file name and the file will be reopened for each time series read. Instead, read commands for specific file formats should be used because these commands are typically optimized to read multiple time series from the files. Use the SetInputPeriod() command to set the period to read. 357 ReadTimeSeriesList() Command TSTool Documentation Command Reference – ReadTimeSeriesList() -2 The following dialog is used to edit the command and illustrates the syntax of the command. ReadTimeSeriesList ReadTimeSeriesList() Command Editor The command syntax is as follows: ReadTimeSeriesList(Parameter=Value, …) Command Parameters Parameter Description Default TableID The identifier for the table that provides the list of location identifiers. None – must be specified. LocationColumn The column in the table containing the location identifiers to use in time series identifiers. None – must be specified. DataSource The data source in the time series identifier. For example, if using the State of Colorado’s HydroBase, USGS indicates that data are from the United States Geological Survey. See the datastore and input type appendices for more information on available data types. May or may not be required, depending on the datastore or input type. Refer to the input type appendices. DataType The data type in the time series identifier. Usually required. Refer to the 358 TSTool Documentation ReadTimeSeriesList() Command Command Reference – ReadTimeSeriesList() -3 Parameter Description Default For example, if using the State of Colorado’s HydroBase, DivTotal is used for diversion totals. See the input type appendices for more information on available data types. datastore and input type appendices. Interval Data interval in the time series identifier, using standard values such as 15Minute, 6Hour, Day, Month, Year. None – must be specified. Scenario Scenario in the time series identifier. Usually not required. DataStore The data store (or input type) in the time series identifier. Refer to the datastore and input type appendices or the TSTool main GUI for options. None – must be specified. InputName The input name in the time series identifier, when a file name is required. IfNotFound Indicates how to handle missing time series, one of: • Warn – generate fatal warnings and do not include in output. • Ignore – generate non-fatal warnings and do not include in output. • Default – generate non-fatal warnings and create empty time series for those that could not be found. This requires that a SetOutputPeriod() command be used before the command to define the period for default time series. Warn DefaultUnits Default units when IfNotFound=Default. Blank – no units. 359 ReadTimeSeriesList() Command TSTool Documentation Command Reference – ReadTimeSeriesList() -4 A sample command file to process monthly diversion data from the State of Colorado’s HydroBase database is as follows: # Read monthly diversion total from HydroBase for the structures in the list # file. The data source is set to DWR because data source is saved in # HydroBase. ReadTimeSeriesList(TableID=”Diversions.csv",LocationColumn=”WDID”, DataSource=DWR,DataType=DivTotal,Interval=Month,InputType=HydroBase, IfNotFound=Default) 360 Command Reference – ReadUsgsNwisDaily() -1 Command Reference: ReadUsgsNwisDaily() Read 1+ time series from the USGS NWIS Daily Value web service Version 10.12.00, 2012-08-06 The ReadUsgsNwisDaily() command reads one or more time series from the United States Geological Survey (USGS) National Water Information System (NWIS) Daily Value web service (see the UsgsNwisDaily Data Store Appendix). The command provides parameters to constrain the web service query and also allows the result to be saved as an output file. For example, if WaterML is chosen as the time series format, a WaterML file can be saved and can be read later using the ReadWaterML() command. See also the WebGet() command, which also can be used to retrieve data files from the USGS website. The USGS NWIS web service allows station and time series data type information to be filtered, both as a convenience and to maintain reasonable web service performance. Many of the choices that are available for limiting queries allow 0+ values to be provided. For example, specifying no requested parameter will return all available parameters for a location. Specifying a list of parameters (separated by commas) will return only the requested parameters. USGS codes are used in order to generate a unique time series identifier (TSID). For example, the TSID data type is formed from the parameter code, a dash, and the statistic code. The numerical codes currently are used to ensure uniqueness but in the future the string name may be allowed as an option. In order to have more human-friendly identifiers for time series, one strategy is to request only a specific parameter and statistic and then use the alias to specify a text equivalent to the numeric codes. For example, specify Parameters=00060 (for streamflow discharge) and Statistics=00003 and assign the alias with Alias=%L.Streamflow-Mean. 361 ReadUsgsNwisDaily() Command TSTool Documentation Command Reference – ReadUsgsNwisDaily() -2 The following dialog is used to edit the command and illustrates the syntax. Note that some choices are provided as a convenience. However, full listing of choices (such as all the thousands of streamflow stations that are available) is not provided due to performance issues. Additional query features will be enabled as web service integration is enhanced. ReadUsgsNwisDaily ReadUsgsNwisDaily() Command Editor 362 TSTool Documentation ReadUsgsNwisDaily() Command Command Reference – ReadUsgsNwisDaily() -3 The command syntax is as follows: ReadUsgsNwisDaily(Parameter=Value,…) Command Parameters Parameter Description Default Sites A list of site numbers to read, separated by commas. None – one of the locational parameters must be provided to constrain the query. States A list of state codes (e.g., AL), separated by commas. None – see above. HUCs A list of hydrologic unit codes, separated by commas. See the limitations on the NWIS site for more information. None – see above. BoundingBox A bounding box consisting of west longitude, south latitude, east longitude, and north latitude, separated by spaces. Longitudes in the western hemisphere are negative. None – see above. Counties A list of Federal Information Processing Standards (FIPS) county codes, separated by commas. None – see above. Parameters Data parameter codes for the stations (e.g., 00060 for stream discharge), separated by commas. All available parameters are returned. Statistics Statistic codes (e.g., 00003 for mean), separated by commas. All available statistics are returned. SiteStatus Filter for stations, one of: • All – all stations are returned • Active – only active stations are returned • Inactive – only inactive stations are returned All SiteTypes Site types to return, separated by commas. All available site types are returned. Agency Agency code to return (e.g., USGS). All available agencies are returned. InputStart The start of the period to read data – specify if the period should be different from the global query period. Use the global query period. InputEnd The end of the period to read data – specify if the period should be different from the global query period. Use the global query period. Alias The alias to assign to the time series, as a literal string or using the special formatting characters listed by the command editor. The alias is a short identifier used by other commands to locate time series for processing, as an None – must be specified. 363 ReadUsgsNwisDaily() Command TSTool Documentation Command Reference – ReadUsgsNwisDaily() -4 Parameter Description Default alternative to the time series identifier (TSID). Format The data format for output, one of: • JSON – JavaScript Object Notation (currently used only for downloads but will not result in time series in TSTool) • RDB – tab-delimited format (also see ReadUsgsNwisRDB() command; currently used only for downloads but will not result in time series in TSTool). • WaterML – XML format (also see the ReadWaterML() command). WaterML OutputFile The name of the output file to create. The path to the file can be absolute or relative to the working directory. No output file will be created. 364 Command Reference – ReadUsgsNwisGroundwater() -1 Command Reference: ReadUsgsNwisGroundwater() Read 1+ time series from the USGS NWIS groundwater web service Version 10.12.00, 2012-10-17 The ReadUsgsNwisGroundwater() command reads one or more time series from the United States Geological Survey (USGS) National Water Information System (NWIS) groundwater web service (see the UsgsNwisGroundwater Datastore Appendix). The USGS data are historical manually recorded values and data may be sparse over the full period. The command provides parameters to constrain the web service query and also allows the result to be saved as an output file. For example, if WaterML is chosen as the time series format, a WaterML file can be saved and can be read later using the ReadWaterML() command. See also the WebGet() command, which can be used to retrieve data files from the USGS website. The USGS NWIS web service allows well and time series data type information to be filtered, both as a convenience and to ensure reasonable web service performance. Many of the choices that are available for limiting queries allow 0+ values to be provided. For example, specifying no requested parameter will return all available parameters for a location. Specifying a list of parameters (separated by commas) will return only the requested parameters. USGS codes are used in order to generate a unique time series identifier (TSID). For example, the TSID data type is formed from the parameter code. The numerical codes currently are used to ensure uniqueness but in the future the string name may be allowed as an option. In order to have more humanfriendly identifiers for time series, one strategy is to request only a specific parameter and then use the alias to specify a text equivalent to the numeric codes. For example, specify Parameters=72019 (for depth to water level) and assign the alias with Alias=%L.WaterLevel. Although the NWIS groundwater web service may return date/times with precision to minute, this command treats all data as daily values and returns a daily time series. The daily interval time series therefore may have many missing values, but often is easier to process with other TSTool commands. In the future,