---
title: "Import Drilling Data v3.0.3"
canonical: "https://servicedesk.maxgeo.com/space/DS/1436385302/Import%20Drilling%20Data%20v3.0.3"
format: markdown
---
**For assay import please see the Assay section**
> Macro (toc)
Import Layouts are used to load data into database tables and can be created to automatically reformat any file to be imported so that it matches the field headings in the database. Import Layouts are constructed by the System Administrator or User Plus roles.
# Any File Format
The supported formats for *Any* file format are Excel spread sheets and CSV files. The field headings of the file to be imported must be matched against the database field headings on each import.
To import data in Any File format, select the File Import Data option from side menu. For example if you wish to import data from an Excel file select the **Any Excel Spreadsheet **option**.**
# Loading Data into Import Buffer
1. Choose **Import Data** from side menu.
2. Choose the Import Layout.
3. Click **Load File**.
4. Select the destination table.
5. Select the DataSet that you wish to import the data into if not in the file itself
6. Select the Date Format in data file eg DD/MM/YYYY or MM/DD/YYYY
7. Click **Select File** and browse for the file to be loaded.
8. Select the Worksheet Name from the drop down list.
When the Worksheet is selected the mapping panel will populate.
Source file columns with the same name as destination table columns will match automatically.
Select the correct destination column name for mismatched source file columns. Columns left unmatched will be ignored.
Delete unused columns so they’re not displayed in the saved layout when it’s being reused.
1. The **Append DataSource column** check box is ticked on by default which populates the Data Source field with the file name. Untick it to use a DataSource column in the file being imported.
2. Click Save to retain the mapping for future use. You will be prompted to add a layout name. The layout can be edited in Import Layout Configurations
3. Click Load
**NOTE:**
**Please note any fields not mapped need to be deleted so they aren’t remembered once you save the layout**
The buffer table will display the data from the source file. It is possible to edit the data in the buffer; however, the changes made will not be made in the original file.
1. The data will be separated into the two tabs on the Buffer DataGrid; Append Buffer to Database and Merge Buffer to Database. The Append tab will contain new data records while the Merge tab will contain updated data for records already existing in DS5.
2. The data in the buffer can be exported using the Export icon.
3. From the Append tab, click Append Data to insert new records
4. From the Merge tab click Merge Data to update existing records. The merge function will take a little more time to complete as there’s more processing involved with the Merge function.
# Append/Merge Data
Data loaded into the database will be either appended to the table or merged with data already in the table.
**Append: Add new records to a specific table in the database.**
** Merge: Updates specific fields in a table to records already in the database. For example, updating the planned collars in the Collar table to the survey pickup coordinates. The primary key columns are used to match the new data with the existing records.**
## View Errors
When the Append Data or Merge Data buttons are clicked the data in the buffer that can be loaded will clear from the buffer.
Data that cannot be loaded will be highlighted with a red triangle against the row.
Clicking on the red triangle will display the error message.
**NOTE:**
**After every import open the appropriate table and DataSet in DataShed5 and view the imported records to confirm they are correct.**
## Import Buffer Modifications
Once you’ve created a non-assay import layout as described above and saved it, you can set up buffer modifications for it by clicking the Import Layout Configurations button on the Import Layout window.
This will bring up a list of all existing import layouts.
Select the layout you wish to modify in the tree on the left and then click the Buffer Modifications button
The Buffer Modifications Grid will display, showing the Destination Table Name as configured when you created the Layout and below it, all of the Field Mappings using the Action “Rename To” even if the field is not being renamed.
**General Notes:**
- Any fields which were excluded during the initial Layout creation will not be available for buffer modifications.
- If the Buffer Modifications need to be processed in a particular order, enter sequence numbers.
- The first thing the import process does, is to rename the columns in the buffer. Actions are applied **afterward** so if you want to apply any actions on a field, you need to use the FieldDB name and NOT the original column name as it appears in the file being imported or the action will fail.
- While every effort has been made to ensure that there is no case sensitivity, it’s recommended to use the FieldDB name in the second Action column exactly as it’s shown in the buffer modifications grid without differences in case, as these expressions might be case sensitive and any discrepancy can cause the expression to not be applied.
**Action columns:**
There are two columns called “Action” in the buffer modifications grid which do different things.
The first Action column (located between the From Field and the FieldDB columns) refers to column/field manipulation and can
- Create a new field or
- Rename an existing field
- Be left blank for data manipulations (see below)
The second Action column refers to data manipulation actions and there are four options:
- Blank (or do nothing)
- Update To
- SQL Delete Where
- SQL Update
We will refer to them as Action1 and Action2 columns respectively.
**Action1**
As mentioned, the default value for the Action1 column is **Rename To** and the value of the Field DB column to which it is renamed is selected from a drop-down so there is little room for error here.
Use **Create New Field** by clicking on the + button (top-left of the grid) to create a whole new field which doesn’t exist in the input file and which HAS to be assigned to an existing database destination table column.
- This will add a new record in the grid.
- The From Field in this case is left blank
- Choose the destination field to which this value will be written in FieldDB
- In the second Action column, you need to use either the Update To or SQL Update options
The 3rd option is to add a new record with the + button and leave this Action1 column blank but only when you want to specify data manipulations in the Action2 column.
**Action2**
**Update To** is a simpler form of **SQL Update. **Both do the same thing, i.e. update the column to a constant value only.
**Important****:** both of these tools are very basic and intended to be used only to apply a single, constant value to the entire column. They can't do any advanced manipulation! If you need to do that kind of manipulation you should be modifying the file you are importing and no attempting to re-create the data in the buffer.
**SQL Delete Where** is used only to remove entire records from the buffer where they satisfy a single criterion.
Multiple Criteria using AND - not supported at all
Multiple Criteria using OR – can be achieved by having multiple SQL Delete statements as in the following example:
**Action2 Syntax**
Note the difference in syntax between Update To on the one hand and SQL Update/SQL Delete Where on the other.
Update To
In the Expression column you simply type the value you want to be applied without any quotes or other operators, regardless of whether it’s a number or text value.
Example
| | | |
| --- | --- | --- |
| **FieldDB** | **Action** | **Expression** |
| Hole_Type | Update To | AC |
SQL Update
Here you need to specify
- the Column to be updated
- use the = operator
- specify the value in quotes for text or without quotes for number columns
Example
| | | |
| --- | --- | --- |
| **FieldDB** | **Action** | **Expression** |
| Hole_Type | SQL Update | Hole_Type = ‘AC’ |
SQL Delete Where
Here you need to specify
- the Column to use for the selection
- An operator such as = / <> / > / < / Is Null / Is Not Null
- The value to be matched. Again for text it has to be in quotes and for numbers not
Example
| | | |
| --- | --- | --- |
| **FieldDB** | **Action** | **Expression** |
| | SQL Delete Where | Orig_Grid_ID <> 'MGA94_54' |
# Check Configuration(s)
- Click on **Check Config** button. This applies a basic check of the configuration of each type of Import Layout. The absence of any errors does not necessarily mean that the Import Layout will work successfully but it does mean that the Layout contains all the information required by DataShed5 to import the file.
- Select the **Check Config** button to check the configuration of the layout that has just been configured.
- Select the **Check Config for all** button to check the configuration of all import layouts.
- To save the Config description and error messages, select the **Save to File** button. Select the directory to store the DataShed5 Diagnostic file (*.dsd) and press the Open button. This will save the file and it can now be viewed as a text file.
When the Import Layout has been correctly configured and there are no more error messages, go to the Import Data form and check the Import Layout by importing a typical file that the layout is based on. The Import Layout that you have created is now visible in the drop down list.
**NOTE:**
**Create Import Layouts for all regularly used files to import as the short time taken to set up Import Layout will save a lot of time when importing these files in future.**
**POTENTIAL PROBLEMS**
The most common form of error is when a file to be imported does not contain the field headings specified in the Import Layout. In creating an Import Layout, DataShed5 will be looking for those exact field headings each time and will generate an error if not found.