MP-Desktop Auto Importer Setup

How to setup MP-Desktop Auto Importers

Overview

The Auto Importer is a highly configurable import process, which consists of folder watchers that monitor for incoming files to process. Importers can be configured to support a number of file types and layouts. This is the core mechanism for importing routine lab data, logger files or any other consistent and known file types. 

Within MonitorPro Desktop the user can set up auto imports.  As a part of Auto Import setup, a path to a folder and expected import file layout is entered. The MonitorPro Service will be installed which runs the auto imports (and the associated tasks such as the Calculator, Scheduled Task and Monitoring Scheduler).

• If it finds a new file in a mapped folder, it will attempt to import it
• If it can import the file then it will move to a process folder, then into a folder called ‘Imported’.
• If it cannot import the file (e.g. the format is not recognised) then it will move it to a folder called “not imported”.
• Where files are of the correct format to enter the database, but the data is incorrect in some way (e.g. invalid dates, physical limit breaches, typos etc.), the file will be imported to a distinct area of the database, called the Import Processor. From here the user can resolve any issues.  

The MP-Service will be permanently watching each import folder and attempts to import each file as soon as it is saved. It is also able to import multiple files concurrently (limited to 5 by default), as well as running the other automated functions of MonitorPro. 

Creating an Auto Importer

Create or locate the folder in Windows Explorer where data files are to be placed.

Typically, there will be a number of import folders, each accepting different layouts of data file, with different rules and pre-process scripts assigned to each are required. 

Import folders to have the most value will be located on network or shared drives, not local computers. It's important to understand that the account that runs the MonitorPro Service needs to have access to the these folders to import the files. As part of the setup of MonitorPro, a member of IT will need to confirm and allow the correct permissions.

When creating import folder paths it's also important to use full path names and not mapped drives (as a D:\ for examples), the path will read \\SERVER\ for example.

Then create an Auto Import in MonitorPro Desktop and navigate to the Control Panel\Auto Imports.

The Auto Import Template tells the importer what format the files are in, where the files are located, and other options (see below).

Select the type of file layout expected to be placed in the folder created in step one, e.g. Grid (Variable Header). See the Auto Importer Introduction for details on the types of files. 

Double click to add a new Auto Import.

General Tab

Name: Enter a meaningful name for the importer

Location: The folder path where the importer will look for new files when run.

Import Type: Select the layout of the data file expected to be placed in the folder, for example, 'Grid Variable Header' where the variables form the header row of the file.  See the section on data file import formats for further details.

Defaults Tab

The Defaults tab allows you to specify default values for key information if it is not present in the file as well as further details about the file format.

Defaults

Date 

For multi-country support the date format can be set, most commonly to allow for US-based mm/dd/yyyy formats.

Column Separator

Specifies the column delimiters for the incoming file (default is a comma).

Incoming data files are often .csv format. csv often is understood to be 'comma separated' meaning that in the file new columns are defined by a comma as a separator. This can be seen if the file is opened in Notepad for example.

However, csv can mean 'character separated' with a different character used to separate columns, for example, semi-colon or tab.  This is common, especially in countries that use a decimal comma instead of a decimal point.

String Delimiter

Defines what should be treated as a text string within the file.  'Wrapping' the data within a file is a common approach as it stops characters, such as a comma or semi column being read as a column separator. Without a string, delimiter files could be misread. This is only very rarely changed from double-quotes.

Record Comment

An entry here will be added to every data readings comment field

Behaviour

The behaviour tab allows users to specify certain rules for each importer. A default selection of options is set when creating a new importer, which represents the most common/typical scenarios. 

 

Auto-create new objects in the database where no match found

If the importer cannot match a name within the file to a name within the database it checks this option to see what to do next. If checked, the importer will create the object it cannot resolve. 

Automatically commit all valid records to database

Typically users will want valid records in a file to be imported, and only those which are not, and need resolution to be held in the Import Processor. If there is an error when importing data, the importer checks this flag to see if it should do so, or alternatively hold back the entire contents of the file. 

The remaining sub-options of this are:

Do not Automatically commit duplicate records to the database - Data which has the same Location, Variable, Data Source, Date/Time, Sample Type, Sample Ref and Lab Ref is considered a unique record. If data is imported which is seen as a duplicate, this setting controls how it is managed. Typically users would want duplicate records to be held in the Import Processor for review as this may indicate an issue, or simply the data has been imported before. 

Retain the complete file in the Import Processor - This setting will retain the entire file in the Import Processor if any records need resolution. This is typically only used if data is normally very consistent and any problem with the file might indicate a wider issue for which holding the entire file back is prudent. 

If any Columns can't be resolved, place complete file in the Import Processor - Similar to the above but only headers which not resolved will retain the entire file, not just individual data resolutions.

Clear any uncommitted records for these files from the Import Processor once done - This setting is used to clear any remaining records from the Import Processor which do not automatically resolve. This setting can be used to discard unwanted data from a file which isn't required for import. 

Clear any duplicate records for these files from the Import Processor once done - As above this setting will clear any valid, but duplicate records from the Import Processor.

Automatically approve any records committed via this auto import

MonitorPro has a mechanism for setting data to a state of 'unapproved' on import. This means data will not be automatically present in standard Datasets if in a state of unapproved. Data can be manually approved or automatically approved based on set rules and workflow, to then show in standard Datasets and onward reporting. Unless this workflow is in place, this should be ticked to approve data on import. 

Auto-detect variable & units over multiple header rows

Tick this box if the data file has variable names split over more than one row and is on by default.

Assume blank entries in appropriate columns are the same as the previous row
This setting will copy down records from above in a file to blank rows below. This is only typical for a small number of logger files where data is provided in a single column but assumed for all the rows of the file. 

Embedded Files

Non-data files can also be imported into MonitorPro via the Auto Importer. This can be done with no linking, and the file(s) lately associated with objects or data in the database manually. To make the process more automated certain rules can be used to help link these non-data files (typically images or pdf documents) to objects in the database.

If you plan to use a variety of these methods then it would be advisable to create some separate import folders, named suitably to guide users as to the rules applied to each.

Link the file to a batch of data brought in with the same name (but a different extension)

A non-data file can be imported with a matching name to the data file being imported. In this scenario, the file will be linked with the batch (file) of data. 

Sample Point

This option is used to match non data files to Sample Points by naming the file the same as the Long or Short name of a Sample Point in MonitorPro

Sample Reference

This option will use the Sample Reference in the non-data file name to match with the data including the corresponding Sample Reference.

 

Lab Reference

This option will use the Lab Reference in the non-data file name to match with the data including the corresponding Lab Reference. 

Import Macro

The Import Macro tab can contain code to pre-process files which do not fit a standard MonitorPro format and convert them to a standard format to import. 

This can be the case with certain loggers, which although flexible the importer cannot resolve natively even with the settings mentioned previously in this section. EHS Data have a library of these macros for a number of common logger types. Please contact your Account Manager or support@ehsdata.com for options.