DMM - Importing Historical Chemistry

Historical Chemistry can be imported into ESdat either from an ESdat Data Interchange File, as a list of Samples and Results in Excel, or as a Crosstabbed Table in Excel (such as you will often encounter if data has been presented in a Spreadsheet ie Samples on the down or across axis and analytes on the other).

Lists of Samples or Results will often be used if importing Historical Chemistry from other database systems (including if received via the ESdat Data Interchange File); while the Crosstab Table is often the best choice when importing data stored in Excel.   The best choice to use is the one that best matches how your Historical Chemistry is arranged.

Lists of Samples and Results

The Chemistry Samples Table contains one entry per Sample.  The Chemistry Results for that Sample are stored in the Chemistry Results table.  Import Templates for these Tables are generated as shown below.

CrossTabbed Chemistry

You should use the CrossTabbed Chemistry Import if importing data from a Chemistry Table in Excel with Analytes across the top and sample information down the page.   Clicking on the "Crosstabbed Chemistry" button opens a form to select sample fields that you wish to include in the import.  

You can then generate either an online import worksheet for the data to be pasted directly into, or download an Excel Import Template for populating with your data and then uploading.

The sample fields to be shown in the import can be changed in the online data grid.

If generating a blank Excel Import Template it will look as below.  Analytes can be posted across (replacing the ChemName values).  Units and the EQL can also be specified.   The ChemCode values are used to uniquely identify Analytes even where different names are used.  The ChemCode can be left blank if unknown and assigned later in the import process.  Five example Analyte columns are provided by default, but as many as are needed can be used.

Once populated the Excel Import Template will look as below.

An Excel Import Template can be loaded into ESdat using the "Populated Import Template" button on the New Import screen.

A populated online import worksheet will look as below.

In addition to populating from a Excel Import Template Data can be pasted.

Assigning ChemCodes

The Analytes can be modifed by clicking the Analytes "Edit" button.  You will need to do this even if the data is populated from Excel as you will need to assign the unique ChemCode to identify each Analyte being imported.

The following form will be shown.  If you need to populate the Original Chemical Name column you can populate multiple values by right clicking in a cell and selecting paste (or transpose paste)

The ChemCode Assign function automatically looks up and matches ChemCodes from the ESdat Chemistry Reference list. If an automated match is not found the chem codes can be ChemCodes and assigned.  Where possible ESdat uses the CAS (Chemistry Abstract System) Numbers as ChemCodes (they will look like 7440-70-2), but where CAS Numbers don't exist (Such as for TPH fractions) ESdat has it's own standard codes.

All samples imported into ESdat also need a unique identifier called a Sample Code.  When importing historical data you may not have a unique value for each sample.  A simple formula containing a combination of the sample location, sample date and sample type will generally provide a unique value, however you can use whatever value you like, as long as it is unique.

Once you are happy the data looks correct you can Validate and Import it as with other Online Import Worksheets.

Special Fields in Chemistry Samples

The names of the fields in Chemistry Samples are largely self-explanatory.   Some fields have special uses and are described below.

The SampleCode field is used to match records in the Chemistry Samples table and the Chemistry Results table.   It must be unique for each Sample within a Project.  Typically a combination of Location and Date/time is unique. 

Certain fields can only be assigned specific values.  Where this is the case drop-downs will be provided (in the browser interface, not available if populating an Excel Import Template). 

The following Fields require a record already exist in the Tables shown below.  A drop-down of existing values will be provided.

 Other Fields such as Matrix Type will be limited to valid List values, and a drop-down of these will be provided.