Import data, Export Data, Display database definition
In this topic:
Import data
You can use this function to load data from text files into the tables of a database. The text files can be in either 'free' or 'fixed' format. In a free format text file, the fields to load are separated by a specified delimiter, such as a comma. In a fixed format text file, the fields to load are in specific columns which have a fixed length.
If you have any non-numeric data that you want to load into a numeric field in your database, you must first establish a numeric equivalent for them by inserting an appropriate translation record for them into the translation table of the database before loading. See Translation Table.
Prerequisites: A database is open.
To run this function: Choose Database > Database > Import data, or Design > Blast design > Database > Import data, or...
- In the Function Chooser, type LOAD DATABASE, and press ENTER.
Fields on the Select format for load/unload form
| Field | Description |
|---|---|
| Database Name | Read-only. The name of the database. |
| Format File Name |
The name of the format file. If you type a new name, after you click Apply, and confirm the creation of the file, the Select database tables to include in format form is displayed. If the format file already exists, the Load tables from text files form is displayed. |
| Report file name | The name of a report file to hold any errors or information messages that occur during the import. |
| Format | The file type and file extension of the report file. |
After you click Apply, if you typed the name of a new format file, the Select database tables to include in format form is displayed. Otherwise, the Load database tables from text files form is displayed.
Fields on the Select database tables to include in format form
| Field | Description |
|---|---|
| Table Name | Read-only. The names of each table in the database. |
| Include | Whether or not to include the table in the format file. |
| Format |
|
| Delimiter | Only available for FREE fields. The delimiter is the character that separates fields in the text file that you are about to load. |
| Space Fill |
Only available for FREE fields and only relevant when outputting.text files from a database.
|
| Text Qual. |
Only available for FREE fields. The text qualifier is placed around any text and memo fields in the database, allowing you to have special characters (for example line breaks) in a text field. It is strongly recommended that you use a text qualifier at all times, but is only essential if the text field contains either line breaks, or the character defined as your delimiter. For the qualifier to be correctly read into the database, the qualifier must be doubled. For example, the following is a valid text file, as output by Surpac and some other database products. It uses a comma delimiter and a double quote text qualifier. The following field value : This is a test of "double" quotes Will be output to a text file out of Surpac as: "This is a test of ""double"" quotes" When this field is read back, the doubled quotes ("") will be converted back to a single quote (") and so the data is not changed. If the data did not contain the double quotes, however (for example the following) : "This is a test of "double" quotes" The data would be read into Surpac as This is a test of quotes because it stops reading at the first quote, and begins again at the second one. For some field values, this could cause validation problems. For this reason, the qualifier must match what appears in the data. Another reason that the qualifier chosen when loading a text file must be correct, is that nothing may be loaded from the text file into the database. Loading to the database only begins when the text qualifier is first encountered. |
When you click Apply, the Select fields to include in format form is displayed.
Fields on the Select fields to include in format form
| Field | Description |
|---|---|
| Table Name | Read-only. The names of each table in the database. |
| Field Name | Read-only. All the field names of the selected tables. |
| Include | Whether or not to include the field in the format file. |
| Column |
For a FREE format field, this field shows the column number in the text file. The column before the first designated delimiter in the text file is column one. The column that follows the delimiter is column two, and so on. For a FIXED format field, this field shows the start position of the field in the text file. The first position is column one with the column numbers incremented for each character position on the line. |
| Length |
Length is writeable only for FIXED format fields. It is the length of the field from the start column. |
| Format | Read-only. Shows whether the field is FREE format or FIXED format. |
Click Apply to display the Load database tables from text files form.
Fields on the Load database tables from text files form
| Field | Description |
|---|---|
| Perform overlapping sample check | After time-independent interval tables are loaded, an overlapping sample check is performed to find any overlapping samples. You can choose whether or not to perform this test to improve database load time. It is recommended that you allow the overlapping sample check to occur unless you are certain that your data contains no overlapping samples. The time saving is only minimal if you do not perform the check. |
| Maximum errors allowed during load | You can nominate the number of errors that can occur during the load before the load terminates. This is recommended because a large number of errors would indicate problems in the format file, your text files or the data itself. All errors are logged in an associated database log file, and rejected records are written into a reject file. |
| Table Name | Read-only. The name of the table to load the data into. |
| Text File Name | The name of the text file to load into the database table. You can leave this field blank if you do not have a text file to load. This feature enables you to reuse format files in which there are more tables included than you have text files. |
| Load Type |
The type of load.
An insert only load is performed. This means only records which do not exist in the database table are loaded. All duplicate and existing records in the text file are rejected. An update only load is performed. With this style of load, only records which already exist are updated. Non- existing records are rejected. An insert and update load is performed. In this mode, records that already exist in the database are updated and records which do not are inserted. |
Output
The text files are imported into the database. A <databaseName>.log file is displayed showing the results of the import. A .dsc format file is created. Any records that were rejected are written to a <databaseName>.rej file.
Export data
You can use this function to unload data from your database into text files. This is useful for checking your data, making backups, and for transferring data to another database or to a different computer system.
Prerequisites: A database is open.
To run this function: Choose Database > Database > Export data, or Design > Blast design > Database > Export data, or...
- In the Function Chooser, type UNLOAD DATABASE, and press ENTER.
Fields on the Select format file for load/unload form
| Field | Description |
|---|---|
| Database Name | Read-only. The name of the database you are unloading data from. |
| Format File Name |
The name of the format file. If you type a new name, and confirm the creation of the file, the Select database tables to include in format form is displayed. If the format file already exists, the Unload tables to text files form is displayed. |
| Report file name | The name of a report file to hold any errors or information messages that occur during the export. |
| Format | The file type and file extension of the report file. |
After you click Apply, if you typed the name of a new format file, the Select database tables to include in format form is displayed. Otherwise, the Unload tables to text files form is displayed.
Fields on the Select database tables to include in format form
| Field | Description |
|---|---|
| Table Name | Read-only. The names of each table in the database. |
| Include | Whether or not to include the table in the format file. |
| Format |
|
| Delimiter | Only available for FREE fields. The delimiter is the character that separates fields in the text file that you are about to load. |
| Space Fill |
Only available for FREE fields and only relevant when outputting.text files from a database.
|
| Text Qual. |
Only available for FREE fields. The text qualifier is placed around any text and memo fields in the database, allowing you to have special characters (for example line breaks) in a text field. It is strongly recommended that you use a text qualifier at all times, but is only essential if the text field contains either line breaks, or the character defined as your delimiter. For the qualifier to be correctly read into the database, the qualifier must be doubled. For example, the following is a valid text file, as output by Surpac and some other database products. It uses a comma delimiter and a double quote text qualifier. The following field value : This is a test of "double" quotes Will be output to a text file out of Surpac as: "This is a test of ""double"" quotes" When this field is read back, the doubled quotes ("") will be converted back to a single quote (") and so the data is not changed. If the data did not contain the double quotes, however (for example the following) : "This is a test of "double" quotes" The data would be read into Surpac as This is a test of quotes because it stops reading at the first quote, and begins again at the second one. For some field values, this could cause validation problems. For this reason, the qualifier must match what appears in the data. Another reason that the qualifier chosen when loading a text file must be correct, is that nothing may be loaded from the text file into the database. Loading to the database only begins when the text qualifier is first encountered. |
When you click Apply, the Select fields to include in format form is displayed.
Fields on the Select fields to include in format form
| Field | Description |
|---|---|
| Table Name | Read-only. The names of each table in the database. |
| Field Name | Read-only. All the field names of the selected tables. |
| Include | Whether or not to include the field in the format file. |
| Column |
For a FREE format field, this field shows the column number in the text file. The column before the first designated delimiter in the text file is column one. The column that follows the delimiter is column two, and so on. For a FIXED format field, this field shows the start position of the field in the text file. The first position is column one with the column numbers incremented for each character position on the line. |
| Length |
Length is writeable only for FIXED format fields. It is the length of the field from the start column. |
| Format | Read-only. Shows whether the field is FREE format or FIXED format. |
Click Apply to display the Unload database tables to text files form.
Fields on the Unload database tables to text files form
| Field | Description |
|---|---|
| Table Name | Read-only. The names of the tables in the database. |
| Text File Name |
The name of the text file to unload data into. You can leave this field blank if you do not want to unload a particular table. This feature enables you to reuse format files where there are more tables included than you want to unload at a particular time. |
Click Apply to display the Define query constraints form. This is displayed so that you can constrain the data to unload if you want. If you do not wish to constrain the data leave the Define query constraints form blank.
If you are unloading the collar table, the constraint is applied to the collar table to determine which drillholes to unload. If you are not unloading the collar table, the Define query constraints form is displayed for each of the tables which you are unloading. The constraints applied to each table have no relationship to each other. For example, any constraints you make to the survey table have no effect on the constraints that you might apply to the assay table.
Fields on the Define Query Constraints form
| Field | Description |
|---|---|
| Table name | Read-only. The name of the table you opened is displayed at the top of the form. Take special note of the table name because it shows you which of the database tables the constraints will be applied to. |
| Field name | The name of the field on which you want to impose a constraint. If you leave Field name blank, all records in the table are selected. |
| Operand |
The mathematical operation that defines the constraint. The operands are:
|
| Constraint Value |
The value for the constraint. Press TAB to add an additional row, or right-click in the left column to add or remove constraint rows. When you have multiple constraint rows, Surpac returns only the records that pass all the constraints. However, you can use the hole_id field and the = operator to return records that pass only one of the constraints. In that situation, an OR operator is used between the constraints. For example, the following constraint would return two records:
Examples: For a surveying example, to select all the survey stations with a specific status, you could add a constraint: status = C The Constraint value for this constraint is C. To select drillholes or survey stations in a particular area, you could add three constraints:
To select all of the holes where the collar is on a specific bench of an open pit, you could enter two constraints similar to these:
|
| Load |
Loads a constraint from the .dbc file you choose. |
| Save | Saves the constraint to a .dbc file so you can reload it later. |
Note: Normally, a set of constraints is suitable for only the table on which it was created, or for tables that have the same or a very similar structure.Output
Data is unloaded to text files from the database. The Message window displays messages to inform you of the progress.
Display database definition
You can use this function to display the details of all tables and fields in the database on a single data entry form. This is useful when the database was not originally created in Surpac and you are not familiar with its structure, or when you have forgotten what tables and fields are in the database.
Prerequisites: A database is open.
To run this function: Choose Database > Database > Display database definition, or Design > Blast design > Database > Display database definition, or...
- In the Function Chooser, type DISPLAY DATABASE DEFINITION, and press ENTER.
Fields on the Display database definition form
| Field | Description |
|---|---|
| [table and field list] | The top part of the form lists all the fields in the database, their data types and the other details that collectively form the database schema. |
| Save Database Definition |
|
| Report File name | The name of the report. |
| Report File Format | The file format, and file extension, of the report. |
Output
If Save Database Definition is selected, a report is created that shows the database schema.