↑ Top
Table of Contents

Spreadsheet Editor (T15)

The Spreadsheet Editor enables you to

  • View a dataset as rows and columns of values (in spreadsheet format),
  • Directly edit the data,
  • Specify the format for values displayed,
  • Search and replace data using formulas,
  • Create new fields of vector datasets, or new grid datasets,
  • Convert a dataset/grid to another format via the SaveAS function,
  • Delete data directly or according to some condition,
  • Manipulate ’group by’ arrangements (lines in line datasets and rows of cells in grid datasets).

For a practical introduction to the Spreadsheet Editor, see Introduction to the Spreadsheet Editor (G04) and Editing with the Spreadsheet Editor (G11).

For details about the expressions and functions available to INTREPID tools, see INTREPID expressions and functions (R12)

Using the Spreadsheet Editor

Warning: This tool directly modifies data in its original file on disc as you work. It does not load a copy of the data into memory that you can save or abandon after editing. Ensure that you have an adequate backup copy of your dataset before using the Spreadsheet Editor!

Warning: If you are carrying out other processes on a dataset in a multitasking environment, you must not attempt to edit the dataset using the Spreadsheet Editor. See Multitasking environments and data integrity below for details.

To use the Spreadsheet Editor:
  1. If you wish to preselect the dataset for editing before starting the Spreadsheet Editor, select it in the Project Manager window (See “Selecting datasets and files” in INTREPID Project Manager (T02)).
  2. Choose Editing > Spreadsheet Editor in Project Manager or use the command dbedit.exe.
  3. INTREPID displays the Spreadsheet Editor window. If you preselected a dataset in the Project Manager, go to step 4.

  4. Open the vector dataset or grid datasets that you wish to edit. Use Open from the File menu. (See Specifying the dataset to edit for detailed instructions.)
  5. INTREPID will open the selected dataset and display the first few columns and rows of the first group of the dataset. Below is the sample magnetic line dataset – Ebagoola_S from Queensland.
  6. sprline.png

    You can

    • (If you have opened a grid dataset) Open other grid datasets that have the same projection and origin, number of rows & columns as the current one, then do arithmetic between grids.
    • Directly edit data by selecting (clicking) the cell containing the data and editing the alterations required in the Edit Cell text box;
    • Use menu options to
    • Move different parts of the dataset into the window (i.e., move through the spreadsheet)
    • Perform other editing functions, including access to special purpose macros/algebras
  7. If you are editing a vector dataset and wish to edit a different dataset, go to step 3. If you are editing a grid dataset and wish to edit a different one, you must exit and launch the Spreadsheet Editor again.
  8. Choose Quit from the File menu when you have finished. You do not need to save the dataset, as all of your editing operations act directly on the dataset files.

Tip: If you are editing a grid or line dataset, INTREPID displays one group at a time in the Spreadsheet Editor window. The group that INTREPID is displaying is the current group.

Rows, columns and groups

The Spreadsheet Editor displays data in rows and columns. In addition, it organises data into groups, displaying one group at a time. The meaning of ‘row’, ‘column’ and ‘group’ depend on the type of dataset you are editing. This section explains how these terms relate to each of the four types of INTREPID dataset.

In addition, INTREPID defines the current row as the row you last clicked in. This is required by some of the spreadsheet functions, for example Split.

See “Vector dataset file and directory structure” in INTREPID database, file and data structures (R05) for a full description of dataset structure, including the concept of ‘group by’ fields.

Grid datasets

In a grid dataset, a spreadsheet row represents one cell in the grid.

If the grid has several bands, the Spreadsheet Editor has one column for each band. If you have opened two or more grid datasets (with the same projection and origin), the Spreadsheet Editor has one column for each band of each open grid dataset.

A spreadsheet group represents one row of cells in the grid(s). Thus the cells in a single row of the grid(s) are shown down a column of the spreadsheet. This illustration shows a four band grid dataset. (The radiometrics Newcastle demonstration grid, showing K,Th,U and Total Count).

It is possible to several grids open simultaneously, so you can perform arithmetic between grids. Each grid must have exactly the same number of rows & columns.

sprmbgrd.png

Point datasets

In a point dataset, a spreadsheet row represents a data point and each column represents a field of the dataset.

If the dataset has a "group by" field, each value of the ‘group by’ field defines a group in the dataset and is represented as a group in the Spreadsheet Editor tool window. For example, in a gravity dataset the ’group by’ field is the station number.

The illustration contains part of an Euler points dataset.

sprpoint.png

Line datasets

In a line dataset, a row represents a data point and each column represents a field of the dataset. In a line dataset one ‘group by’ field is the line number. Each group in the Spreadsheet Editor tool window contains the data of one traverse line.

sprline.png

Polygon datasets

Polygon datasets are really a special case of a line dataset, having only one line and therefore only one group. INTREPID sometimes adds a ’group by’ field, Name, containing a name for the polygon. This does not need to be the same as the dataset filename. If a polygon dataset does not have a Name field, you can easily create one.

sprpoly.png

Summary of Editing operations

Loading datasets Save and close any currently open dataset. Open a dataset and display it in the Spreadsheet Editor window.

Quit Close any dataset that is open and then close the Spreadsheet Editor window.

Direct editing Alter the data directly using keyboard and mouse.

Navigating Rows, Columns, Groups Choose a section of data to view.

Split group/Join group Divide a group into two, or combine two adjacent groups into one

Find Search for the next data point where a logical condition is met

Replace Replace or create data values throughout the dataset according to a condition specified by you.

Reorder columns Change the order of the columns in the dataset.

Create new Columns Create new fields in a table dataset, or a new grid, if working on a grid, or a voxet, if working on a voxet etc.

Format Columns Set column width and number of decimal places.

Delete Row Group or Field Delete the current Row or Group, or delete a Column.

Conditional deletion of Rows or Groups Delete rows or groups according to a condition specified by you.

Some Spreadsheet Editor operations are intended for use only with certain dataset types. The following table lists the operations and their target dataset types.

Operation

Grid

Point

Line

Polygon

A row is

corresponding cells in each grid or band

a data point

A column is

cells of one row of the grid(s)

a field

A group is

a row of cells

a group

a line

the dataset

Open several datasets

yes

Navigate rows/columns

yes

yes

yes

yes

Navigate groups

yes

yes

Direct editing

yes

yes

yes

yes

Format column

yes

yes

yes

yes

Reorder columns

yes

yes

yes

yes

Split/Join groups

yes

New column (grid/band/field)

yes

yes

yes

yes

Find

yes

yes

yes

yes

Replace

yes

yes

yes

yes

Delete row

yes

yes

yes

Delete group

yes

Delete column (grid/band/field)

yes

yes

yes

yes

Conditional delete row

yes

yes

yes

Conditional delete group

yes

Specifying the dataset to edit

You can specify the dataset to edit using either of the following methods:

sprmfile.png

If you use Open Dataset or Open Grid from the File menu, INTREPID displays an Open dialog box. Use the directory and file selector to locate the dataset you require. (See “Specifying input and output files” in Introduction to INTREPID (R02) for information about specifying files.)

Vector datasets do not require any aliases for use in the Spreadsheet Editor.

Opening several grid datasets

You can open more than one grid dataset. INTREPID displays each band of each grid dataset in a separate column, and automatically aligns the datasets.

Warning: Grid datasets loaded together must have the same projection and origin.

spr2grid.png

Navigating Rows, Columns and Groups

You can navigate around the dataset (i.e., locate and view different sections) using

  • The scroll bars and Next Group / Prev Group buttons
  • The Navigate dialog box.
sprnavigdia.png
To navigate rows and columns using the scroll bars

Use the horizontal scroll bar to view different columns, and the vertical scroll bar to view different rows.

The scroll bars work in the conventional way:

  • Click the scroll arrows to move one row or column at a time.
  • Click the scroll channel (that the scroll box slides along) to move one screenful of rows or columns at a time.
  • Drag the scroll box to position the required rows or columns in the current group under the Spreadsheet Editor window.
To navigate groups using the group navigation buttons

Use << Prev Group and Next Group >> to view and edit other groups in the Spreadsheet Editor window.

To navigate Rows, Columns and Groups by number.
  1. Choose Navigate from the Edit menu. Notice certain database only options are disabled when working with grids.
  2. sprmedit.png

    INTREPID displays the Navigate dialog box

    sprbnavi.png
  3. Type the numbers of the Group, Row and Column that you require.
  4. Choose Apply. INTREPID displays the group you require. The Navigate dialog box will remain open.
  5. Perform any operations you require with the current view of the dataset.
  6. If you wish to navigate to another position in the dataset, change focus to the Navigate dialog box and go to step 2.
  7. When you have finished with the Navigate dialog box, choose Close.

Multitasking environments and data integrity

The Spreadsheet Editor is designed for editing very large datasets. It therefore does not load a whole copy of the dataset into memory for the purposes of editing. When you edit data INTREPID saves the change immediately to the original copy of the dataset on disc.

If you are carrying out several editing processes on a dataset in a multitasking environment, it is easy to lose data integrity – to destroy your data. When INTREPID writes a change to the disc it may clash with changes being written from the other process.

If you confine your activities to viewing the dataset (i.e., carry out read-only processes) with the Spreadsheet Editor when another process is occurring, your data will be safe.

When the Spreadsheet Editor opens a dataset it notes parameters such as the dataset’s size. If another process is changing the dataset you may need to reopen it in the Spreadsheet Editor occasionally to update these parameters to view the whole dataset in its changed form. To do this, simply use Open from the File menu to open the dataset again.

Fields associated with lookup tables – Expert Option

Some vector dataset fields are associated with a lookup table process. The values or reported values result from applying an original or actual value to a lookup table. There are two types:

Fields with field values lookup files specified in their headers

If your vector dataset field has an associated field values lookup file in its header (.vec) file it will contain original data values but INTREPID will report the results from the lookup table. Contact our technical support service for further information if you wish to edit such a field with the Spreadsheet Editor.

See “Field header (.vec) information” in INTREPID database, file and data structures (R05) for details about these fields.

Fields imported using a DDF with a field values lookup file

When INTREPID imports a field using a DDF with a field values lookup file, the field will contain the results of the lookup, not the original data from the import input file. See The INTREPID DDF format (R08) for details.

Directly editing the data

You can directly edit the data from a cell of the spreadsheet using the Edit Cell text box near the top left corner of the Spreadsheet Editor tool window.

To directly edit data
  1. Navigate so that you can see the data to be edited on the screen.
  2. Select (click) the item of data. INTREPID displays it in the Edit Cell text box near the top left corner of the Spreadsheet Editor tool window.
  3. For compound data types (See “Data types in INTREPID datasets” in INTREPID database, file and data structures (R05) ), INTREPID shows the values that make up the field separated by spaces. If there are several scalar types in the field, such as field strength and components, INTREPID collects the data of each scalar type between square brackets. For example, in an Observed moving platform gravity field, the field strength and three components would appear as

    [M 32.2500][V –9983.0 45325.045 64213.6754]

  4. If you wish to delete a digit or character: Click immediately to the right of the digit or character. INTREPID displays a vertical bar | to show the insertion point. Press backspace to delete the character.
  5. If you wish to insert a character: Click the position where you wish to insert the character. INTREPID displays a vertical bar | to show the insertion point. Type the character(s) you require.

    Repeat this step as required. If the insertion point is in the correct position for a number of operations, you do not need to position it between these operations.

  6. When you have finished editing the cell, press enter or return. INTREPID displays the new value in the body of the spreadsheet.

Notes:

  • You can assign null to a cell by typing the word null into it.
  • If you change the value of a ’group by’ field in a vector dataset, the whole group will take this new value (by definition).

Deleting the current row – Non-Grids only

To delete the current row
  1. Click anywhere in a row to select it for deletion. The selected row is now defined as the current row.
  2. Chose Delete Current Row from the Edit menu.

Deleting a field

To delete a field from a vector dataset
  1. Choose Delete Field from the Edit menu. INTREPID displays the Delete Field dialog box.
  2. sprbdelf.png
  3. Select (click) the name of the field you wish to delete, then choose OK. INTREPID displays a confirm dialog box, then deletes the field if you choose Yes.

Column Display Order

You can change the display order of the columns in the Spreadsheet Editor window.

To reorder the columns:
  1. Choose Reorder Columns from the Format menu.
  2. sprmform.png
    sprbreor.png

    INTREPID displays the Column Display Order dialog box.

  3. Select (click) the first field to be displayed in the Current Order list box, then choose >>. INTREPID will transfer the field name to the New Order list box. Repeat this process with the rest of the fields until their names have all been transferred to the New Order list box.
  4. If you make a mistake or change your mind, transfer field names back to the Current Order list box until any remaining field names are in the correct order. (For each name, select (click) it and choose <<.) Transfer the field names to the New Order text box in the correct order.

  5. Choose OK. If you choose Cancel, INTREPID will not carry out any column reordering

Format – compound data types graphical display

The Spreadsheet Editor displays some compound data types graphically. See “Representing compound data types graphically” in INTREPID database, file and data structures (R05) for an explanation.

Format – column width and decimal places

You can set the display width and number of decimal places for any column. Note that the number of decimal places refers to display format only. This operation does not affect the precision of the data.

To set the format for columns:
  1. Choose Format Column from the Format menu.
  2. sprmform.png

    INTREPID displays the Column Format dialog box.

    sprbform.png
  3. Select (click) the field you wish to format in the Column list box.
  4. Enter the required column width (in characters) and the number of decimal places for the column display in the corresponding text boxes.
  5. Choose OK. INTREPID will apply the settings to the column.

Splitting and joining groups

If you are editing a traverse line dataset you can

  • Split a group to make two groups or
  • Combine two groups to make one

You may wish to split a group, for example, if you have two traverse lines, one in each direction, that are stored as a single line, and you wish to separate them.

You may wish to join two groups, for example, if you have a single traverse line which has been stored in two parts as separate lines.

sprmedit.png
  1. To split a group to make two groups:
  2. Click anywhere in the row at which you wish the new group to start. The selected row is now defined as the current row.
  3. Choose Split Group at Current Row from the Edit menu. INTREPID will create a new group starting at the current row, and display the new group.

Both groups will retain the same ’group by’ field value. You can change one or both ’group by’ field values by direct editing if required.

To join two groups to make a single group:

Warning: The value of any ’group by’ fields in the second group will be destroyed by this operation.

  1. Display the second of the two groups.
  2. Choose Join Group to Previous Group from the Edit menu. INTREPID will join the groups and assign the value of the group by field in the first group to the whole new group.

Spreadsheet functions and expressions

The Spreadsheet Editor uses expressions in searching, replacing and creating new fields. See INTREPID expressions and functions (R12) for a full description of the syntax of the expressions and the functions and operations available.

Search conditions are expressions with a logical value (i.e, the value ’true’ or ’false’).

Replacement value expressions are expressions used to specify the new value of a field or grid band in a Replace operation. The data type of the expression must match the data type of the field or grid band.

Referring to grids or grid bands

If you are referring to grid cell values in an expression or as a target for a replacement value, use the name of the grid itself as the field name in the expression. For a multiband grid add a colon followed by the band number.

Examples:

raw_grid < 2016

K_Th_U_grid:1 > 1723.56

Example of Search condition

You may be searching for all tie lines. Tie lines typically have a line type of 4. A search condition to locate tie lines would be

linetype == 4

Warning: Do not confuse conditional == operations with assignment = operations.

Example of replacement value expressions

This example rotates the direction of traverse lines from a bearing of 22° to a bearing of 0° (North/South). The expressions convert the original X and Y field data to new rotated X and Y fields.

Target field

Replacement value expression

rotatedX

X * cos(22) – Y * sin(22)

rotatedY

X * sin(22) + Y * cos(22)

Expressions involving characters

Search conditions involving characters (strings) may only use:

  • Field or grid band names,
  • Literal strings (enclosed in double quotes) for example, "Zone A"
  • The operators:
  • == (equal to)
  • != (not equal to)
  • && (and)
  • || (or)

Replacement value expressions involving characters (strings) may only consist of a literal string (enclosed in double quotes), for example, "Zone A".

Examples:

Location != "Zone A"

Location == "Zone A" || Location == "Zone B"

Search conditions

The Spreadsheet Editor has three operations that involve automatic searching using a search condition.

Find simply locates data in the dataset matching a search condition.

Replace changes values in the dataset according to a search condition.

Conditional Delete deletes rows or groups that match a search condition.

Search conditions must be INTREPID expressions with logical type values (See INTREPID expressions and functions (R12)).

Each search operation has a corresponding dialog box with an If or Search For text box for specifying the search condition.

You specify a search condition by typing it into the If or Search For text box or by loading it from an INTREPID formula file.

The current search condition

If you specify a search condition as part of a Find, Replace or Conditional Delete operation (see corresponding sections below), it becomes the current search condition. If you go on during the current Spreadsheet Editor session to use one of the other search operations, INTREPID will automatically insert the current search condition in the new search operation dialog box.

For example, if you perform a Find operation using the search condition mlevel_mag < 1964, then go on to perform a Replace operation, INTREPID will automatically insert the condition mlevel_mag < 1964 in the Replace dialog box.

You can, of course, delete or edit the condition in any of the dialog boxes. If you apply the edited condition to the dataset by actually performing a search with it, it will become the current search condition.

Loading and saving search conditions

You can store search conditions as INTREPID formula files.

When you are preparing for a search you can load an expression stored in a formula file into the If or Search For text box of the search operation dialog box.

If you have specified an search condition that you wish to use in future you can save it as an INTREPID formula file.

Formula files are block-structured text files with the extension .frm. See “Formula files” in INTREPID expressions and functions (R12) for further details.

To save a search condition into a formula file
  1. Specify the search condition in the search operation dialog box.
  2. Choose Save Formula in the search operation dialog box or its File menu.
sprmfilf.pngsprformb.png

INTREPID displays a Save As dialog box. Use the directory and file selector and the File Name text box to specify the name and location of the formula file. (See “Specifying input and output files” in Introduction to INTREPID (R02) for information about specifying files.) Omit the .frm extension – INTREPID adds it.

To load a formula file to use as a search condition
  1. Display the dialog box for the search operation.
  2. Choose Load Formula in the search operation dialog box.

INTREPID displays an Open dialog box. Use the directory and file selector to specify the formula file (extension .frm) you require. (See “Specifying input and output files” in Introduction to INTREPID (R02) for information about specifying files.).

INTREPID displays the condition in the If or Search For text box.

Find

You can search the dataset for a value in a field or any condition that you can express in the form of an INTREPID expression.

To search for a condition.
  1. If you wish to commence the search from a particular position in the dataset, navigate to and click the corresponding cell. You can search forwards or backwards from this point.
  2. Choose Find from the Edit menu. INTREPID displays the Find dialog box.
  3. sprmedit.pngsprbfind.png
  4. Complete the text boxes as required. Note the hints for comparing field values eg fid == 394192, linetype > 2 etc
  5. Choose Forward and/or Backward as required. The Find dialog box will remain open.
  6. Perform any required operations with the current view of the dataset.
  7. If you wish to search for another instance of the data, change focus to the Find dialog box and go to step 3.
  8. When you have finished searching, choose OK.

Find dialog box elements

Search for Use this to specify the search condition. See Search conditions for details.

Between – and – Use this to restrict your search to a range of groups. If you wish to restrict the range, enter the starting and ending group numbers in the corresponding text boxes.

Notes:

  • Group numbers simply refer the groups in the order that they occur in the dataset, and always start from 1.
  • When INTREPID first displays the dialog box, the range text boxes contain the default start and end specifications. These refer to the start and end of the whole dataset.
  • You can use Between – and – to refer to rows in a grid dataset or lines in a traverse line dataset.
  • For a traverse line dataset, you must enter the group number rather than the value of the ’group by’ field.

Forward / Backward Choose the corresponding button to search forwards or backwards through the dataset.

If you have selected a start point in the spreadsheet (See step 1 above) and it is within the ’Between – and –’ range, searching will commence at this position.

If you have not selected a start point in the spreadsheet (See step 1 above) and the currently displayed group is within the ’Between – and –’ range, searching will commence at the first row and column of the current group.

If the current group is outside the Between – and – range, searching will commence at the start of the first group or the end of the last group in the ’Between – and –’ range.

After you choose Forward or Backward and INTREPID displays the data found in the search, the Find dialog box remains on the screen allowing you to carry out further searches. To perform further searches, change focus to the Find dialog box and choose Forward or Backward again.

OK When you have finished with the Find dialog box you can dismiss it using OK.

Replace

You can search the dataset using a search condition and make changes to values depending on whether the current dataset row satisfies the search condition.

If you wish, you can specify one value for the new field value if the search condition is true and a different one if it is false.

You can omit the search condition (default value is ’true’) and thus change the field value for all data in the dataset or ’Between – and –’ range.

If you specify changes to a field or grid that does not currently exist, INTREPID will automatically create a new field or grid with the name specified. (It prompts you to confirm the operation and specify format and data type for the new field – See Creating a new field or grid.)

To use Replace (example 1)

In this example we want to set the linetype field according to the value of the azimuth field

  1. From the Edit menu, choose Replace.
  2. INTREPID displays the Replace dialog box.

    sprbrepl.png
  3. Complete the text boxes as required.
    1. If azimuth < 45 && azmiuth > -45
    2. Then linetype = 2
    3. Else linetype = 4
  4. Choose OK.
To use Replace (example 2)

The following example shows how to convert a character line type field (linecode) to a numeric one (linetype). In this case you would need also to change the line type field setting in the dataset description. Note the double quotes around the character field value.

  1. From the Edit menu, choose Replace.
  2. INTREPID displays the Replace dialog box.

    SPRBREPL2.png
  3. Complete the text boxes as required.
    1. If linecode == "L"
    2. Then linetype = 2
    3. Else linetype = 4
  4. Choose OK.

Replace dialog box elements

If Use this to specify the search condition. See Search conditions for details.

Then – = – Use the first text box to specify the field or grid or grid band to be changed if the search condition is true and the second text box to specify its new value as an INTREPID expression.

Else – = – Use the first text box to specify the field or grid or grid band to be changed if the search condition is false and the second text box to specify its new value as an INTREPID expression.

Between – and – Use this to restrict your Replace operation to a range of groups.

If you wish to restrict the range, enter the starting and ending group numbers in the corresponding text boxes.

Notes:

  • Group numbers simply refer the groups in the order that they occur in the dataset, and always start from 1.
  • When INTREPID first displays the dialog box, the range text boxes contain the default start and end specifications. These refer to the start and end of the whole dataset.
  • You can use ’Between – and –’ to refer to rows in a grid dataset or lines in a traverse line dataset.
  • For a traverse line dataset, you must enter the group number rather than the value of the ’group by’ field.

OK Choose Ok to perform the Replace operation and close the Replace dialog box.

Creating a new field or grid using Replace

To create a new field or grid using Replace
  1. For a vector dataset, specify a field that does not currently exist as the field to be changed in the Then or Else text box.
  2. For a grid dataset, specify the name(s) for the new grid(s) in the Then and/or Else text boxes.

  3. INTREPID will prompt you to confirm that you are creating a new field, then display the Create Field dialog box. Follow the instructions in section Creating a new field or grid.

When you have specified the new field, INTREPID will create it and assign values using the Relace operation.

Creating a new field or grid

Using the Spreadsheet Editor you can

  • Add a new field to a vector dataset
  • Create a new single band grid (usually derived from the currently open grid)

With the current version of INTREPID you cannot add a new band to a grid or vector dataset field using this tool.

To create a new field
  1. Choose Create New Field from the Edit menu. INTREPID displays the Create Field dialog box.
  2. sprbnewf.png
  3. Enter and select the field properties.
  4. Choose OK. INTREPID will add the field to the dataset.

Field Name Use this text box to enter the name of the new field

Data Type Use these option buttons to select the type of data for the field. You can choose from the following

Option button

Description

Signed16BitInteger

Integer (2 byte)

Signed32BitInteger

Integer (4 byte)

IEEE4ByteReal

Real (4 byte)

IEEE8ByteReal

Real (8 byte)

Logical

Logical

Character

Character(s)

Byte

Byte (0..255)

Vector

3D Vector

Gradients

Gradient vector

Components

Field components vector

Complex

Complex number

Tensor

Tensor

Date

Date

Geological Structure

Structural object (Foliation)

Quaternion

Quaternion object(3d complex)

Observation Object

Observation object

See “Data types in INTREPID datasets” in INTREPID database, file and data structures (R05) for more information about this topic.

Field measurement coordinate convention (East North Down)

For vector & tensor fields (gravity and magnetics amongst the many possible), the positive measurement direction for a component or a gradient in each of the 3 orthogonal directions is always defined, and as we manipulate these measures in conjunction with other spatial data, the convention used must be recorded to achieve the correct results downstream.

Width Use this text box to specify the width (in characters) of a character type field. INTREPID ignores the value in this text box if you specify a numeric field.

’Group By’ Field Turn on this check box if the value of the field is to be the same for all records within a group. If this is a ’group by’ field INTREPID will only store its value once for each group. (In a line dataset, one ’group by’ field is the line number field.)

Initial Value Use this text box to enter the initial value of this new field for all records. You can use any INTREPID expression that is compatible with the data type specified. For logical fields specify the values 1 for ’true’ and 0 for ’false’.

Create new field example – Coordinate Rotation

You may wish to create a new set of X and Y coordinates that have a 30° anticlockwise rotation.

You could create two new fields XR and YR,based on the original X and Y fields, where XR becomes X * cos(30) + Y * sin(30)

YR becomes Y * cos(30) – X * sin(30)

You can create these fields using Create a New Field from the Edit menu.

Create new field example – Line Type field

INTREPID uses a line type field to distinguish between acquisition lines and tie lines. In some survey data the line type is determined by the line numbering convention, and there is no separate line type field. You can use the Spreadsheet Editor to create a line type field.

Since the value of the line type field depends on the value of another field, you need to set its values using the Replace operation. You can create the field first without setting an initial value then use Replace, or use Replace directly to create the field and set its value.

Here is a Replace settings example for this operation, where tie lines have numbers greater than 7000 and type 4, and acquisition lines have type 2.

If line > 7000
Then linetype = 4
Else linetype = 2

See “Traverse line numbers and types” in INTREPID database, file and data structures (R05) for more information about traverse line types and numbers.

If you intend to use this line type field with tools such as the Gridding tool, assign the new field to the LineType alias. See “Vector dataset field aliases” in INTREPID database, file and data structures (R05) for more information about aliases.

Create new field example – Quality control

This example explains how to measure sections of invalid data. You may wish to identify survey data taken outside the acceptable altitude range. To do this you can a field which has value 1 if the survey height is outside the range and 0 if it is inside. You can then use the contig function to report the size of any section of invalid data.

In our example the ideal survey height is 100 m and you wish to identify data pojnts whose altitude is more than 50 m higher or lower than this level. We wish to locate areas where the survey height was out of range for more than 10 data points in a row.

  1. Use Replace to create a field (htvalid) with values as follows
  2. If abs(height - 100) > 50
    Then htvalid = 1
    Else htvalid = 0

  3. Use Find to locate points for which contig(htvalid) > 10 (or create a field with the value contig(htvalid).

Create new field example – Polygon name field

It is sometimes convenient for a polygon dataset to have a field containing its name or description. For example, the INTREPID Hard Copy Composition tool can automatically include the contents of a ’group by’ field as a label for a polygon dataset in a composition (See “Including or editing regions of interest – polygon datasets” in Including datasets in a map composition (T45b)).

You can easily create a name field for a polygon using the Create New Field. Create a new field with the following properties:

Property

Value

Type

Character

’Group by’

On

Width

Sufficient to contain the name

Initial Value

The name you require for the polygon.

Create new field example – Tensor FreeAir

The following is taken from the cookbook tensor examples – creating a T_FA field from the existing database component fields, using a formula. This is a Bell Geospace dataset, so the field convention is Left handed, END.

SPRBNEWF2.png

Conditional deletion of rows or groups

You can automatically delete rows or groups from a dataset depending on a search condition.

To use conditional delete
  1. Choose Conditional Delete from the Edit menu.
  2. INTREPID displays the Conditional Delete dialog box.

    sprbdelc.png
  3. Specify the condition, range and delete option according to your requirements, then choose OK.

If Use this to specify the search condition. See Search conditions for details.

Delete Row / Delete Group Select the option according to your requirements. If the condition is satisfied anywhere in the row or group, then the row or group will be deleted. You can use Delete Group with line datasets only. We recommend that you do not use Delete Row with grid datasets because you may lose geolocation of subsequent rows.

Between – and – Use this to restrict your Conditional Delete operation to a range of groups.

If you wish to restrict the range, enter the starting and ending group numbers in the corresponding text boxes.

Notes:

  • Group numbers simply refer the groups in the order that they occur in the dataset, and always start from 1.
  • When INTREPID first displays the dialog box, the range text boxes contain the default start and end specifications. These refer to the start and end of the whole dataset.
  • You can use ’Between – and –’ to refer to rows in a grid dataset or lines in a traverse line dataset.
  • For a traverse line dataset, you must enter the group number rather than the value of the ’group by’ field.

Help

You can use the Help menu to display help text on the topics shown in the menu illustration below.

sprmhelp.png

Exit

Choose Quit from the File menu to close the current dataset and exit from the Spreadsheet Editor.

Using task specification files

You can store sets of edit instructions for the Spreadsheet Editor in task specification files. You can only use these files in batch mode. Unlike most other INTREPID tools, the Spreadsheet Editor has no facility for loading and saving task specification files in interactive sessions.

From version 5.0, a new task specification language based on Google protobuf technology is also available, featuring better documentation and error reporting.

To use a task specification file for a batch mode Spreadsheet Editor task

Type the command dbedit.exe with the switch –batch followed by the name of the task specification file.

For example, if you had a task specification file called surv329.task in the current directory you would use the command

dbedit.exe –batch surv329.task

HISTORY file not updated by the Spreadsheet Editor

Unlike most INTREPID tools, when you perform editing operations the Spreadsheet Editor does not record an ’audit trail’ of operations in the project’s HISTORY file. See “Task and hard copy specification and HISTORY files” in Introduction to INTREPID (R02) for further information about HISTORY files.

Task specification file notes and example

For full instructions on creating and editing task specification files see INTREPID task, HISTORY, report and log files (R06).

Data types for syntax

The syntax table in this section describes the assigning of values to keywords. The table immediately following describes the data types of these values

<path>

Filename with directory specification

<field>

Name of field or grid band

<ord>

Group or row number, using the order in which groups or rows occur in the dataset.

<datatype>

Name of field data type.

The following data types are available:

Signed16BitInteger

Integer (2 byte)

Signed32BitInteger

Integer (4 byte)

IEEE4ByteReal

Real (4 byte)

IEEE8ByteReal

Real (8 byte)

Logical

Logical

Character

Character

Tensor

Tensor(384 byte)

FieldGradients

Vector(24 byte)

FieldComponents

Vector(24 byte)

Geology3D

Vector(24 byte)

Complex

Real (16 byte)

Date

Date (16 byte)

Byte

Byte (0..255)

<expression>

An INTREPID expression. This must be placed in double quotes " " if it is not part of an <assignment>.

<assignment>

Assignment of an INTREPID expression to a field or grid band, using the format "<field> = <expression>". This must be placed in double quotes " "

Task specification syntax

Statement

Description

Default

IntrepidTask

Start of task specification file

dbedit

Identification as Spreadsheet Editor task

Action {

Open dataset definition

Type : OpenField;

Name : <path>";

Name of dataset

}

Action {

Create field definitiona

Type : CreateField

Name : "<field>’;

Name of new field or grid

Dtype : <datatype>;

Data type of new field or grid

IEEE4byteReal

GroupBy : <yes|no>;

Is this a ’group by’ field?

no

Width : <number>;

No of characters in field
(Character field only)

0

Initial: <expression>;

Initial value of field or grid cells

null

}

Action {

Replace definition

Type : Replace;

IfAction: <expression>;

Condition (logical expression)

trueb

ThenAction : <assignment>;

Action if true

no actionc

ElseAction : <assignment>;

Action if false

no action

FromGroup : <ord>;

Starting group for operation, -1 denotes off, 0 is first group

first

ToGroup : <ord>;

Last group for operation,-1 is off, 0 denotes first group

last

FromSamp : <ord>;

Starting row for operation,-1 is off, 0 denotes first group

first

ToSamp : <ord>;

Last row for operation,-1 is off, 0 denotes first group

last

}

Action {

Delete field definitiond

Type : DeleteField;

Name : <field>;

Name of field or grid

}

Future versions of INTREPID will provide a wider range of operations in the task specification language

}

a To create a new multiband grid, specify an existing multiband grid as the initial value.

b If a ThenAction statement is present, then you must include an IfAction statement. A ’blank’ (always true) If statement can take the form

IfAction : " ";

c If an ElseAction statement is present, then you must include a ThenAction statement. A ’blank’ (no action) ThenAction statement can take the form

ThenAction : " ";

d Not recommended for grids.

Task specification example

# # Example V6.0 task file - spreadsheet editor # # Shows various arithmetic operations performed as batch tasks. # The in-built list of supported function is long, apart from normal maths # includes many to convert/create compound geophysics signal types from their parts eg tensors # Usage: fmanager -batch spreadsheet_examples.task # # Open an Intrepid dataset, and keep it open for subsequest actions IntrepidTask { FileManager { Action: CopyTable Input: "${tutorial}/Intrepid_datasets/EBA_DBs/ebagoola_S..DIR" Output: "./ebagoola_S..DIR" } } IntrepidTask { dbedit { Action { Type: OpenField;# actually opens the database with all the fields Name: "./ebagoola_S..DIR" } Action { Type: SaveAs Save: "./out..DIR" } } } IntrepidTask { dbedit { Action { Type: OpenField# actually opens the database with all the fields Name: "./out..DIR" } # create a new data field, specifying a field name, data type and initial value Action { Type: CreateField Name: "newz1" Dtype: DT_R4 GroupBy: false Initial: "Null" } # use the Replace function to perform an operation on a field Action { Type: Replace ThenAction: "newz1 = raw_mag - 1970" } # use the Replace function with an "If Then Else" construct Action { Type: Replace IfCondition: "newz1 > 0" ThenAction: "newz1 = raw_mag" ElseAction: "newz1 = Null" } # some examples of use of different operators Action { Type: CreateField Name: "diff_abs" Dtype: DT_R4 GroupBy: false Initial: "diff_abs = abs(raw_mag - smooth_mag)"# using an in-built function call } Action { Type: CreateField Name: "diff_max" Dtype: DT_R4 GroupBy: true Initial: "diff_max = groupmax(raw_mag)" } Action { Type: CreateField Name: "diff_min" Dtype: DT_R4 GroupBy: true Initial: "diff_min = groupmin(raw_mag)" } Action { Type: CreateField Name: "random_values" Dtype: DT_R4 GroupBy: false Initial: "Null" } Action { Type: Replace ThenAction: "random_values = 100*randomrepeatable(1)" } # special for string data, and setting the limited display width Action { Type: CreateField Name: "Company_Report" Dtype: DT_CH Width: 5 GroupBy: true Initial: "Company_Report_old" CoordinateSystemType: LOCAL } } }

Frequently asked questions

Q : In “Specifying input and output files” in Line correction and tie levelling (T30) the footnote says that a ’fake’ fiducial field may be generated, so that a unique Fiducial number for each point in the entire dataset is calculated and generated. What’s the INTREPID function in the Spreadsheet tool which will accomplish this?

Use %Group (returns current traverse line) and %Row (returns current data point in current travese line) inbuild variables.

If, for example, the largest number of data points in any one traverse line was less than 1000, then the following would generate an effective fiducial field:

NewField = %Group * 10000 + %Row

or, if you want all fiducial values to have 7 digits

NewField = 1000000 + %Group * 10000 + %Row

Check the total number of Groups, of course.

Q : I used to use $Group and $Row or $Samp, but now the documentation talks about %Group and %Row or %Samp. What is going on?

We recommend that you use % now.

A % works in interactive mode, and also when you save formulae and reload them, and when you run batch tasks.

A $ works only in interactive mode but is misinterpreted by our parser when it is re-loading saved formulae or executing in batch mode.

Q : Why does the parser misinterpret the $?

$ is used in batch tasks the signify a variable which is substituted from a list or from the environment.

Q : Is the DIFF4 operator in spreadsheet the same as the 4th diff operator in the profile editor?

A: No. The NDIFF4 operator in spreadsheet is the equivalent of the profile editor 4th diff operator. These operators normalises the output, scaling it to reduce the amplitude of the 4th difference. By contrast, the DIFF4 operator does not perform any scaling.