Home : Q14450 Vars from Lab CSV Utility
Q14450 - INFO: Q14450 Vars from Lab CSV Utility

Q14450 Create Vars from Lab CSV Utility is intended to be a "preprocessor" of CSV files using the Q12351 LIMS CSV Interface.  The Utility will scan the files and create locations and variables for CSV records where a variable is not cross referenced. 

Example:  The Lab CSV file contains routine water distribution data (i.e. Cl2, Bacti results for our known sampling sites) and non-routine samples (customer complaint, new main, etc...).  In WIMS we have setup and cross referenced variables for all the routine samples but not for the non-routine.  The Utility would scan the CSV files, create variables/locations for the non-routine samples.  After the utility is run, the Q12351 Interface runs and imports data for the existing routine data and the NEW non-routine samples. 

Setup:

Fill out the fields (described below), click [Map Columns] button to set which columns correspond to which fields in WIMS.  Then click [Save Settings] and your settings will be saved to the config.ini file.  Save Settings also saves the filter and the state of filtering (i.e. is a filter currently applied).  As this utility is typically used as a scheduled task to autorun prior to the interface run, note that the filter will be applied during the autorun if saved in that state.  We may need to install and setup the interface twice if the CSV files apply to more than 1 facility.  In this case, setup a filter to only see records (rows) for one facility then install another copy of the interface and setup a filter for the second facility. 

WIMS Connection String: A connection string to the WIMS database.  NOTE:  If using the OPSDBA username, set password to ###. 

Source Folder: Folder and File specification for the source CSV files.

Dest Folder:  Folder to place CSV files in after processing is complete.

Parent Location:   The Location ID of where you want to put the new locations.  To find the Location Id, in WIMS go to System Setup>Location Setup.  Click on the location and the ID will be shown in the lower right corner:

 

Frequency:  The variable frequency (Daily, Hourly, etc...) of the new variables.

Type: The variable type (Parameter or Text Parameter) or you can set to detect.  Detect will analyze the Result field and determine if it is a parameter or text.

Interface ID:  The Interface ID to set the new variables to.  To find the interface ID, in WIMS go to System Setup>Entire System>Server Side Interface Setup.  Click on the row for the interface and the ID will be shown in the lower right hand corner.

Set Data Read-Only:  For new created variables will set the Data Read-Only option.  Data Read-Only marks all data for the variable as Read-Only, i.e. it CANNOT be entered, edited, or deleted in WIMS, only interfaces can write data to the variable.

Set Definition Read-Only: For new created variables will set the Definition Read-Only option.  This option marks the variable's setup as Read-Only, i.e. no fields can be changed (name, units, etc...).   Only Super Users or managers with the Allowed to edit Variable's Definition Read-Only Setting can edit the variable.

Filter: Column to use to filter results.  In this example we only want to look at Non-Routine so we set the filter to on Column I (Sample Type) equal to Non-Routine.  Only rows displayed will be considered when adding variables.  You can up to 3 filters and they are applied from left to right.  Example:

  •  
    If Column J equals 1905 or 3014 AND Column Y = SW

 

Map Columns:

Click the Map Columns button to setup which columns correspond to which fields in WIMS.  Example, Column G in the CSV file holds the LIMS Loc. 

LIMS Loc: (Required)  Will be matched to the Vardesc.LIMS_LOC Interface field.  Also, if the variable is not found, the utility will check if the location exists in the Parent and if not will create a location with this name. 

Address: If mapped will populate the Location.SiteAddress Field.

City: If mapped will populate the Location.SiteCity field.

State: If mapped will populate the Location.SiteState field.

Zip: If mapped will populate the Location.SiteZip field.

PWSID: If mapped will populate the Location.PWSID field.

WSFID: If mapped will populate the Location.WSFID (Facility Id) field.

SamplePt ID: If mapped will populate the Location.SAMP_POINT_ID (Sampling Point ID) field.

Analyte: (Required) Will be matched to the VarDesc.LIMS_TEST interface field.

LIMS UD1: (Required if your CSV interface uses the LIMS UD1 field).  Will be matched to the VARDESC.LIMS_UD1 field.

Units: Sets the VARDESC.Units field

Description: Sets the VARDESC.DESCRIPTION field.

Result: (Required if you set the Type to Detect).  Used to determine the variable type.  Used to set the VARDESC.VARTYPE field.

Variable UD1 thru UD9: If mapped will populate the VARDESC.UDx field.

Location UD1 thru UD9: If mapped will populate the LOCATION.UDx field.

Location Detail File: If checked allows a separate file with additional Location information to be joined with the Source File.  You then specify the Location file path.  This can be used when the Source CSV file just has key field(s) to identify the location (no address, etc...) The Locations Detail File can then be matched to those key field(s) and used in the import.  Example, my source file has PWSID and SampPtId.  I have a files (locations.csv) that has the PWSID and SampPtId along with the address for those locations.  I can join/lookup the address for each source record which will allow me to import the address information when creating the new locations.

Example:  Match PWSID and SamplePointId (columns E,G) of the results csv file (shown on left) to the Locations csv file (shown on right) to columns A,D.  This will allow us to import the Address, City, State, etc...  when creating the location. 

 

Using:

1. Click the [Load Files] button.  It will load all files that meet the file specification in the Source Folder setting.

2. Click [Check].  This optional step will highlight in yellow all records (rows) that a new variable will be created for.  NOTE:  If the file contains duplicate records (i.e. Location DS004, Anaylte CL2, Sample Type Routine) for different sampling dates only the first row will be highlighted in yellow.  

3. Click [Create Vars].  Variables will be created.  A summary message will be displayed:

3 new locations added with the 5 variables added to parent Non-Routine (ID 56).

Variable parameter variables are setup with Allow Symbols turned on and decimal places set to Not Fixed.  Interface cross reference is setup:

 

Auto Run:

If you launch Q14450.exe with a command line option it will Load the settings, load the files, Create Vars and Exit the program.  You can use the Windows Task Scheduler.  Notice the 1 after the exe, this tells the utility to auto run.

Tech Notes:

Log files will be created for each run in the log folder.  There is no log cleanup function as part of the utility, therefore users should clear the log folder as needed.

Code Repository Path : $Utilities/Q14450

Code Repository Name : Hach.WIMS.Interfaces

Code Repository Product
: git (sourcetree)

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 6/6/2017 11:51 AM.
Last Modified on 6/3/2019 1:13 PM.
Last Modified by Scott Dorner.
Article has been viewed 1025 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article