---
title: "Import Buffer Modifications"
canonical: "https://servicedesk.maxgeo.com/space/DS/1437270032/Import%20Buffer%20Modifications"
format: markdown
---
From version 3.2 onwards, selected **UPDATE,** **SQL UPDATE** and **SQL Delete** functions are supported in DataShed5 when using buffer modifications on a saved layout.
Buffer Modifications allow users to transform buffer‑table data using a controlled subset of SQL‑style expressions. The following section outlines the functions that are supported, those not supported, and examples demonstrating correct usage.
**Import Buffer Modifications**
Once you’ve created a non-assay import layout and saved it, you can set up buffer modifications for it by clicking the “**Import Layout Configurations”** button on the Import Layout window.
Select the **Layout Type**. This will bring up a list of existing import layouts for the selected type.
Select the layout you wish to modify in the Drop Down list on the left.
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.
- 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.
Whenever you add or modify one of these records, you should press **Enter** on the keyboard, so the change is committed to the grid before clicking on the **Save** button to save the entire set of buffer modifications.
> ℹ️ **Note:** All **operators** require a leading and trailing space.
> ℹ️
> ℹ️ **Correct Format:** Prospect = Prospect + '-' + Hole_ID WHERE Hole_ID LIKE 'H_%'
> ℹ️
> ℹ️ **Incorrect Format:** Prospect=Prospect+'-'+Hole_ID WHERE Hole_ID LIKE 'H_%'
**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 not 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** - is supported.
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. Both text and numbers have to be in quotes.
Example
| | | |
| --- | --- | --- |
| **FieldDB** | **Action** | **Expression** |
| | SQL Delete Where | Orig_Grid_ID <> 'MGA94_54' |
## Update To
- When using constant text or dates in functions, the text/dates need to be enclosed in double “ “ or single ' ' quotes.
Example: ISNULL(Orig_Grid_ID, **‘MGA94_51’**)
or DATEDIFF(day, **"01/01/2025"**, **"24/04/2025"**)
- Field or column names do not need any brackets. Always refer to other columns using their **FieldDB** name, not the **From Field** name.
- When using any functions where dates are involved, always apply a date mask when loading a file with the saved import layout, regardless of it being a CSV or Excel file.
- Functions can be combined in complex statements such as
IIF(ISDATE(Date_Started),CONVERT(date,Date_Started),GETDATE())
- Depending on the **Sequence** number, the effects of functions are cumulative, building on the previous.
> ℹ️ **Example:** If I set up a buffer mods where I concatenate the DataSet, Hole_ID and 5 spaces as the Lease_ID (sequence value of 1) and then set up another buffer mods on the Comments column which references Lease_ID and TRIMS it (sequence value of 2), then I get the full string in Lease_ID but in Comments, I get the results of Lease_ID and then I trim that so that I end up with the concatenated string minus the five spaces.
### Update To - Supported Functions
| | | | | |
| --- | --- | --- | --- | --- |
| **Type** | **Function** | **Expression Example** | **Result** | **Comment** |
| Condition | ISNULL | ISNULL(Prospect, Hole_ID)
ISNULL(Orig_Grid_ID, "MGA94_51") | string or number | replaces a Null value with something |
| Condition | IIF | IIF(Orig_Azimuth>10, Orig_Azimuth-10, Orig_Azimuth)
IIF(ISDATE(Date_Started),CONVERT(date,Date_Started),GETDATE()) | string or number | tests a condition and updates based on result |
| Inspect | ISDATE | IIF(ISDATE(Date_Started), Date_Started, "InvalidDate") | true or false | tests if the value is recognized as a date(with date mask) |
| Inspect | ISNUMERIC | ISNUMERIC(Max_Depth) | true or false | tests if the value is recognized as a number |
| Convert | CONVERT | CONVERT(int, Max_Depth) | int, decimal,
nvarchar, date | converts one format to another |
| Date | GETDATE | GETDATE() | date | gets the current date and applies the date mask |
| Date | DATEADD | DATEADD(interval, number, date)
DATEADD(year, 1, GETDATE())
DATEADD(month, 2, Date_Started) | date | MUST use date mask
Add a time period to a date |
| Date | DATEDIFF | DATEDIFF(interval, date1, date2)
DATEDIFF(day, "01/01/2025", "24/04/2025") | number | (date2 - date1) MUST use date mask
calculates the difference between dates |
| Number | ROUND | ROUND(Max_Depth,1) | decimal | rounds a number to a specified decimal |
| Text | CONCAT | CONCAT(string1, string2, ...., string_n)
CONCAT("Hello",Hole_ID,"Good Bye"
CONCAT(DataSet, Hole_ID) | string | join 2 or more string values |
| Text | LEFT | LEFT(string, number_of_chars)
LEFT(DataSet, 2) | string | extracts a specified number of characters starting on the left |
| Text | RIGHT | RIGHT(string, number_of_chars)
RIGHT(DataSet, 2) | string | extracts a specified number of characters starting on the right |
| Text | SUBSTRING | SUBSTRING(string, start, length)
SUBSTRING(DataSet, 2, 2) | string | extracts a specified number of characters starting at a specified location and length |
| Text | LTRIM | LTRIM(Comments) | string | removes all leading spaces |
| Text | RTRIM | RTRIM(Comments) | string | removes all trailing spaces |
| Text | TRIM | TRIM(Comments) | string | removes all leading and trailing spaces |
| Text | REPLACE | REPLACE(Lease_ID,"PL","ABC") | string | replaces any string value with another string value |
### Update To - Unsupported Functions
The following functions or expression types either do not execute, return incorrect results, or are explicitly flagged as unsupported.
| **Category** | **Unsupported Features** | **Notes** |
| --- | --- | --- |
| **ISDATE on literals** | ISDATE(‘01/01/2025’), ISDATE(‘2025-01-01’) | Literal date checks not supported. |
| **Advanced SQL clauses** | WHERE EXISTS, WHERE NOT EXISTS | Not supported for buffer targeting. |
| **Bitwise operations** | &, |, ^ | Not Supported. |
| **Scalar subqueries** | (SELECT MAX(...) ...) | Not supported in SET clauses. |
## SQL Update To
- The SQL Update tool has 3 uses:
- Set one field equal to another field in the buffer table or a constant
- Use the Where clause to filter records to apply the SQL Update to
- Use the CASE WHEN statement to apply various values to replace
- **Limits:** The SQL Update can’t be used in combination with Functions from Update To
- Field or column names should not be enclosed in square brackets. Always refer to other columns using their FieldDB name, not the From Field name.
- Text values and dates need to be enclosed in **single** quotes
- When using any SQL statements where dates are involved, always apply a date mask when loading a file with the saved import layout, regardless of it being a csv or Excel file.
### **Supported SQL UPDATE Constructs**
- Simple assignments: Prospect = 'Alpha' WHERE Prospect = 'Unknown'
- Arithmetic: Max_Depth = Max_Depth + 10
- Ranges: ... Plan_Azimuth = (Plan_Azimuth * 1.1) - 5 WHERE Plan_Azimuth BETWEEN 0 AND 360
- String concatenation with + or CONCAT()
: Comments = CONCAT('Hole ', Hole_ID) WHERE Comments IS NULL
: Prospect = Prospect + '-' + Hole_ID WHERE Hole_ID LIKE 'H_%'
- CASE statements (simple and searched): Prospect = CASE WHEN Prospect LIKE 'PL%' THEN 'Pipeline' WHEN Prospect LIKE 'AB%' THEN 'AlphaBeta' ELSE Prospect END WHERE Prospect IS NOT NULL
- LIKE, NOT LIKE: Prospect = 'GroupAC' WHERE Prospect LIKE '[A-C]%'
- IN, NOT IN: DataSet = 'Other' WHERE DataSet NOT IN ('SA','NT','QLD')
- DATEADD, DATEDIFF filters: Comments = 'Old' WHERE DATEDIFF(day, Date_Started, GETDATE()) > 365
- ISNULL as replacement in SET: Plan_Grid_ID = ISNULL(Plan_Grid_ID, 'MGA94_51') WHERE Plan_Grid_ID IS NULL
### SQL Update - Supported Functions
| | | | | |
| --- | --- | --- | --- | --- |
| **Type** | **Function** | **Expression Example** | **Result** | **Comment** |
| Condition | ISNULL | ISNULL(Prospect, Hole_ID)
ISNULL(Orig_Grid_ID, "MGA94_51") | string or number | replaces a Null value with something |
| Condition | IIF | IIF(Orig_Azimuth>10, Orig_Azimuth-10, Orig_Azimuth)
IIF(ISDATE(Date_Started),CONVERT(date,Date_Started),GETDATE()) | string or number | tests a condition and updates based on result |
| Inspect | ISDATE | IIF(ISDATE(Date_Started), Date_Started, "InvalidDate") | true or false | tests if the value is recognized as a date(with date mask) |
| Inspect | ISNUMERIC | ISNUMERIC(Max_Depth) | true or false | tests if the value is recognized as a number |
| Convert | CONVERT | CONVERT(int, Max_Depth) | int, decimal,
nvarchar, date | converts one format to another |
| Date | GETDATE | GETDATE() | date | gets the current date and applies the date mask |
| Date | DATEADD | DATEADD(interval, number, date)
DATEADD(year, 1, GETDATE())
DATEADD(month, 2, Date_Started) | date | MUST use date mask
Add a time period to a date |
| Date | DATEDIFF | DATEDIFF(interval, date1, date2)
DATEDIFF(day, "01/01/2025", "24/04/2025") | number | (date2 - date1) MUST use date mask
calculates the difference between dates |
| Number | ROUND | ROUND(Max_Depth,1) | decimal | rounds a number to a specified decimal |
| Text | CONCAT | CONCAT(string1, string2, ...., string_n)
CONCAT("Hello",Hole_ID,"Good Bye"
CONCAT(DataSet, Hole_ID) | string | join 2 or more string values |
| Text | LEFT | LEFT(string, number_of_chars)
LEFT(DataSet, 2) | string | extracts a specified number of characters starting on the left |
| Text | RIGHT | RIGHT(string, number_of_chars)
RIGHT(DataSet, 2) | string | extracts a specified number of characters starting on the right |
| Text | SUBSTRING | SUBSTRING(string, start, length)
SUBSTRING(DataSet, 2, 2) | string | extracts a specified number of characters starting at a specified location and length |
| Text | LTRIM | LTRIM(Comments) | string | removes all leading spaces |
| Text | RTRIM | RTRIM(Comments) | string | removes all trailing spaces |
| Text | TRIM | TRIM(Comments) | string | removes all leading and trailing spaces |
| Text | REPLACE | REPLACE(Lease_ID,"PL","ABC") | string | replaces any string value with another string value |
| Number | MODULUS | Max_Depth = Max_Depth % 10 WHERE Max_Depth IS NOT NULL | int | calculates the remainder after dividing one number (the dividend) by another (the divisor) |
| Condition | CASE | Lease_ID = CASE Lease_ID WHEN 'PL-123' THEN 'ABC' ELSE Lease_ID END WHERE Lease_ID IN ('PL-123','ML999') | string or number | a conditional logic tool used to evaluate conditions and return a specific value based on the first condition met |
| Condition | NOT IN set | DataSet = 'Other' WHERE DataSet NOT IN ('SA','NT','QLD') | string or number | exclude rows that match any value in a specified list or subquery |
| Condition | IN set | DataSet = 'Core' WHERE DataSet IN ('SA','NT','QLD') | string or number | specify multiple possible values for a column |
| Condition | LIKE | Prospect = 'GroupAC' WHERE Prospect LIKE '[A-C]%' | string or number | used with the `WHERE` clause to search for a specified **pattern** in a column, using wildcard characters. |
### **Unsupported SQL UPDATE Constructs**
| **Category** | **Unsupported Features** | **Notes** |
| --- | --- | --- |
| **Advanced SQL clauses** | WHERE EXISTS, WHERE NOT EXISTS | Not supported for buffer targeting. |
| **Bitwise operations** | &, |, ^ | Not Supported. |
| **Scalar subqueries** | (SELECT MAX(...) ...) | Not supported in SET clauses. |
### **Supported SQL DELETE Constructs**
| **Category** | **Supported Operators** |
| --- | --- |
| **Equality, inequality** | LIKE, NOT LIKE: Prospect NOT LIKE 'Alpha%' |
| **IN / NOT IN** | DataSet IN ('NT','SA') |
| **BETWEEN** | Max_Depth BETWEEN -5 AND 0 |
| **DATEDIFF** | DATEDIFF(month, Date_Started, GETDATE()) > 12 |
| **Pattern matching with ESCAPE** | Comments LIKE '%!%%' ESCAPE '!' |
### Unsupported:
- EXISTS / NOT EXISTS deletes - (Cannot reference BufferLookup or other relational tables.)