Introduction

CSV Wide Format Exporter
Introduction

The UDG1000 CSV Wide Format Exporter is used to summarize data from UDG Input parameters and log data to a Comma Separated Values (CSV) file. For example, the 5 second data from the DO, pH, and Temperature Input Parameters can be exported as minutely averages, hourly maximums, etc…

The Exporter will create wide format CSV files on a scheduled basis. The file can be automatically generated hourly or daily. A service (Hach UDG1000 Wide CSV Writer) is configured to perform this action for you based on your settings.

Note: Because this exporter works with a service, administration rights are necessary to elevate permissions (User Account Control) when controlling the service is needed.

Example Wide format CSV viewed in Excel:

Copyright© 2014 Hach.com, All rights reserved.


Pre-requisites

CSV Wide Format Exporter
Pre-requisites

1. UDG 1000 Software installed on PC.

2. Disk space available to store CSV files.

3. UDG 1000 input parameters are setup and collecting data.

4. Have administration rights in order to install, start and stop service

Copyright© 2014 Hach.com, All rights reserved.


Setup

CSV Wide Format Exporter
Setup

1. Add a new Exporter. From the UDG1000 Main Menu go to Output > Exporters and click Add.

2. Select “CSV Wide Format Exporter” from the list. Click Add.

3. Enter a name for the exporter. Click Save.

4. Configure the exporter:

Exporter Configuration

The Exporter Configuration section allows you to set up when a CSV file will be generated, where it will be located, and how the header rows will look.

When to Export: Specifies automatic file generation as either Hourly or Daily. The time field will be the time of day for daily automation and will be number of minutes after the hour for hourly automation (the hour portion of the time is ignored, so 08:15 would run 15 minutes after each hour). Default is Daily and 23:59 (11:59 PM local time). Time is always in local time to the service and UDG 1000.

File Location: The location where the wide formatted CSV files will be created on the computer. Note: you can navigate to a shared resource or a network drive as long as the UDG Data Converter service is changed to include a user name and password that has access to that resource. Right click on the service and select the Log On tab to add the user account (click the Apply button and then restart the service):

Output Header 1 – 4: Specifies what, from the parameter Output Cross Reference information, is displayed in the header rows (up to 4) for each column. The default is ID (GUID), Location, Units, and Parameter Name. The options are:

ID: Unique identifier (GUID) for the output parameter
Location: User specified field for each parameter
Units: User specified field for each parameter that will initially default to input Units
Parameter: Name of the parameter from the Input configuration
Blank: A blank row is created in the header
Off: Omits that row from being used on output

Service Status and Control

A service (Hach UDG1000 Wide CSV Writer) will install the first time this exporter is saved. There will be only one service for any number of exporters of this type, so if another CSV Wide Format Export is added to UDG 1000, the service status window will update to the current state of the service (i.e., it will be green if the service is already installed and running). The service installs automatically on the same computer as UDG 1000, do not attempt to alter this.

Logs – brings up File Explorer where the Hach UDG1000 Wide CSV Writer service logs information. More about the service can be found under Technical Notes.
Refresh – this button will refresh the status box just to the right of the button.
Status box shows the current state of the Hach UDG Data Converter Service

- Service not installed, it will when you click the Save button.
- Service is installed and running.
- Service is installed and stopped.

Start – this button starts the service if it is installed and stopped. It will request elevated permission.
Stop – this button stops the service if it is installed and started. It will request elevated permission.

5. Click Save. This might request elevated permissions one or more times, depending on the current state. It will update the configuration and then install and start the service, if necessary. NOTE: there is no need to reboot the computer or restart the application for this step, however if the service is already installed you will need to click Stop and Start buttons to activate your changes.

Once everything is properly configured, the Exporter Configuration display will update the service status:

6. From the UDG1000 Main Menu go to Output > Cross References

7. You will be prompted for:

Frequency and Statistic specify how often and what to calculate. The above example will calculate the Hourly Average and label the data as the start time of each hour.

Example 1: Output the Hourly average for DO

· DO input parameter setup with a polling frequency of 5 seconds.

· Create an output cross reference with a Frequency of Hourly, Statistic of Average, and Store Time of Start Date

· At the end of an hour, the UDG 1000 will calculate the average of the 5 second readings for the previous hour and store the result in a temporary file (Protobuf file).

· The exporter service (i.e. Hach UDG1000 Wide CSV Writer service), at the time specified in the Export Configuration, will read in all Protobuf files and create the wide format CSV file.

8. You can add an Input Parameter multiple times to an export in order to get different statistics and frequencies into the export.

The following screen shows how to fill in the values for Location and Units. As we mentioned, initially the Units are filled in from the input parameter but can be changed. In order to alter these fields, select the parameter on the right window, and click the Edit button.

You can change any field on this form except Parameter, Id, and Name (to change the Parameter and Name, go to the Input > Parameters section of UDG 1000, and make changes in the right side window). After making changes, click the Save button to save your changes.

The Units field defaults to the Input > Parameter Units and can be changed to anything. Note: it does not do any unit conversion, to do that click on the Scaling/Filtering tab and add a scaling factor:

Copyright© 2014 Hach.com, All rights reserved.


CSV Wide File Format

CSV Wide Format Exporter
CSV Wide File Format

The CSV files exported are named yyyy_MM_dd__hh_mm.csv. The fields are the time and date when the file was created and are as follows:

· yyyy – four digit year

· MM – two digit month (01 – 12)

· dd – two digit date (01 – 31)

· hh – two digit hour (00 – 23)

· mm – two digit minute (00 – 59)

Any data exported is imported to that filename by the Hach UDG1000 Wide CSV Writer service. Therefore, the file date does NOT imply it contains data for that day, only that the data exported when the service ran. As mentioned, the date and time in the filename is when the service created the CSV.

Each export can have the following header fields (depending on Exporter Configuration):

ID – A GUID (globally unique identifier) for the output cross reference. The GUID is assigned by the Wide CSV Writer exporter when the Output Cross Reference is created and cannot be changed. NOTE: If importing this data into another system, it is suggested that you use the GUID to link instead of the Name as the name might not be unique.

Location – The Location field from the Header Information on the output cross reference, e.g. Influent, Effluent or 501 Main St.

Units – The value in the Units fields

Time Stamp – The date time in local time or UTC (GMT) as specified on the Output > More Settings > Timestamp Preference.

Values – Whether these are Double, Integer or String they are converted to a string for the CSV file.

Example file:

The following file was setup with 3 header rows: Location, Units, and Parameter. Note column 1 is always displays the date-time stamp for the row. Date time field will be formatted according to the local computers’ regional settings.

Copyright© 2014 Hach.com, All rights reserved.


Technical Notes

CSV Wide Format Exporter
Technical Notes

1. What is the standard work flow?

a. The work flow starts with the UDG 1000 CSV Wide exporter creating temporary files (Protobuf files) and is found in a ‘cache’ folder under the “Application Data” folder:

C:\ProgramData\Hach Company\UDG\ExportData\UdgWideCsv

This is the location of the Application Data folder:

The Cache folder contains a unique folder for each exporter:

C:\ProgramData\Hach Company\UDG\ExportData\UdgWideCsv\cache\[GUID]

The [GUID] is assigned to the specific exporter and is the name of the folder (the UdgWideCsv.custom.config file, shown above, contains all the GUID’s and specifics of each exporter).

b. The Protobuf files are created when an Output cross reference calculates its summary. A Protobuf file is created for each output cross reference calculated summary. There can be one or many summary data points and parameters in each Protobuf file.

c. At the specified time and interval (either Hourly or Daily), Hach UDG 1000 CSV Writer service reads all available Protobuf files, creates the CSV file, and deletes the Protobuf files.

Example: Two output cross references (one 15 minute, and one Hourly summary)

Also notice that the Store Time is Start Date, which can be Start, End, or Constant Date.

Export is scheduled to run Hourly at 7 minutes after each hour.

The following actions occur:

Time | Action
8:15 | Protobuf file xxx created for Rio Grande DO 8:00 to 8:15 average of 3.87
8:30 | Protobuf file yyy created for Rio Grande DO 8:15 to 8:30 average of 3.81
8:45 | Protobuf file zzz created for Rio Grande DO 8:30 to 8:45 average of 3.95
9:00 | Protobuf file aaa created for Rio Grande DO 8:45 to 9:00 average of 3.91
9:00 | Protobuf file bbb created for Rio Grande Temp 8:00 to 9:00 average of 14.2
9:07 | Hach UDG 1000 CSV Writer service reads all available Protobuf files (xxx,yyy,zzz,aaa, and bbb) and creates CSV file.

CSV file created (UDG1000W_20140611_0907.csv):

Remember, the cross reference configuration called for Start Date, so the value is place in the slot at the beginning period.

2. How can I manually generate a CSV Wide File?

a. Import data of some kind into the database, if you just need to test use the Historical Function import plug-in to import simulated data.

b. Configure a Wide CSV Writer exporter plug-in, use Hourly and set the time to 3 - 5 minutes from now.

c. Click Save, this will install the Hach UDG 1000 CSV Writer service that creates the CSV Wide Format files. If the service is installed, Stop and Start the service to read the new configuration.

d. Select Cross Reference to the data that was imported.

e. Select Manually Export and export data, this will create the Protobuf file.

f. If the scheduled time to run has passed before finishing the previous step repeat steps ‘b’ and ‘c’ above.

3. What is the ‘Id’ in the Header for? This is a unique identifier (GUID) that is created when you create a cross reference. The Id can be mapped to the output (if you configured the exporter to show Id on the output).

4. What happens if I point two exporters to the same output folder and for the same time? The Wide CSV Writer exporter is designed with “last one wins”. So if you have matched up the output folders to the same location, one of two things will happen:

a. The service will overwrite one export CSV with the other one.

b. The service will sense the file is in use and abort the export, and log a message

5. Need to install the service manually. The service can be installed from UdgDataConverterSetup.msi found under the folder of the exporter. The exporter is found under the location of the Hach UDG 1000 installation (typically this is C:\Program Files (x86)\HACH Company\UDG1000\Plugins\Exporters\d982f39c0bea448f9249934bd69ea972). Double click on the file to run it, but you must have administration privileges to install.

What is this service and why is it there? The Hach UDG 1000 is designed to export data when the data is available, not on a scheduled time. The Wide CSV Writer plug-in that is part of Hach UDG 1000 takes these summarized data points and writes them to temporary files. The service runs on a specified schedule that you configured when you created an exporter. The service takes all these files and creates the CSV Wide Format files when you specify.

This is the service that will consume the Protobuf files and create the CSV Wide Format files:

The Name under Services is Hach UDG1000 Wide CSV Writer, and the Service Name is UDG1000WideCSVWriter.

6. How can the service be removed? You must me logged in with administrative privileges. Open a Command Prompt as Administrator (right click on Command Prompt and select Run as Administrator). Type in the following command to complete remove the service:

sc delete UDG1000WideCSVWriter

7. How can the service be uninstalled?

a. Remove the service from Windows Services first (#5).

b. Next, open Uninstall a program and select UDG1000WideCSVWriter

c. Click Uninstall to remove the program.

8. The service was uninstalled but still remains in Windows Services. If the service was uninstalled before it was removed from Windows Services, it will need to be reinstalled manually (#4) and then remove the service from Windows Services (#5). Finally, uninstall the program (#6).

Copyright© 2014 Hach.com, All rights reserved.