GMS:Import From Database: Difference between revisions

From XMS Wiki
Jump to navigationJump to search
No edit summary
No edit summary
 
(16 intermediate revisions by the same user not shown)
Line 1: Line 1:
The "Database Import Wizard" allows users to access data stored in a database and import it.  The wizard is invoked by selecting the '''''Import from Database''''' command from the ''File'' menu.  The data types that can be imported by the '''Database Import Wizard''' are the same data types that are supported by the [[GMS:Text Import Wizard|"Text Import Wizard"]].
The ''Database Import Wizard'' allows accessing data stored in a database and importing it.  The wizard is invoked by selecting the '''Import from Database''' command from the ''File'' menu.  The data types that can be imported by the ''Database Import Wizard'' are the same data types that are supported by the [[GMS:File Import Wizard|''File Import Wizard'']].


The ''Database Import Wizard'' has four steps:
The ''Database Import Wizard'' has four steps:
== Step 1 - Connecting to a Database ==
== Step 1 – Connecting to a Database ==
Step 1 of the wizard lets you set up a connection to a database.  To connect to a database either on the same computer or on a network press the '''Connect to Database''' button.  Once a database connection is created, a path to the database and the different tables in the database are displayed.  When a table is selected its columns are displayed along with the number of rows in that table.
Step 1 of the wizard sets up a connection to a database.  To connect to a database either on the same computer or on a network press the '''Connect to Database''' button.  Once a database connection is created, a path to the database and the different tables in the database are displayed.  When a table is selected its columns are displayed along with the number of rows in that table.


== Step 2 - Querying Information from a Database Table ==
== Step 2 – Querying Information from a Database Table ==
Step 2 allows you to create, copy, delete, and import queries that retrieve data from a database.  To help in writing the queries, the tables in the database are displayed, as well as the columns in the selected table.
Step 2 gives the option to create, copy, delete, and import queries that retrieve data from a database.  To help in writing the queries, the tables in the database are displayed, as well as the columns in the selected table.


Some SQL Basics
===SQL Basics===
The query is an SQL (Structured Query Language) statement.  The SQL statement is entered in the '''Query SQL statement''' edit field.  An example of an SQL statement would be: “SELECT x, y, z, toluene FROM multipledatasets”.  This statement means that columns x, y, z, and toluene from the table multipledatasets will be retrieved from a database.  SQL statements are case sensitive.  SQL statements also require brackets around table or column names that have spaces.  For example, to query data from a table titled “x coordinate” in the SQL statement it would be written as [x coordinate].  A full explanation of SQL is beyond the scope of this document.
The query is an SQL (Structured Query Language) statement.  The SQL statement is entered in the ''Query SQL statement'' edit field.  An example of an SQL statement would be: “SELECT x, y, z, toluene FROM multipledatasets”.  This statement means that columns x, y, z, and toluene from the table multipledatasets will be retrieved from a database.  SQL statements are case sensitive.  SQL statements also require brackets around table or column names that have spaces.  For example, to query data from a table titled “x coordinate” in the SQL statement it would be written as [x coordinate].  A full explanation of SQL is beyond the scope of this document.


You can write a short description for each query in the '''Query description''' edit field.  The '''New''' button creates a new query that has a default name, description, and SQL statement.  The '''Copy''' button creates a copy of the currently selected query.  The '''Delete''' button deletes the currently selected query.
A short description for each query can be written in the ''Query description'' edit field.  The '''New''' button creates a new query that has a default name, description, and SQL statement.  The '''Copy''' button creates a copy of the currently selected query.  The '''Delete''' button deletes the currently selected query.


The queries you define are saved automatically by GMS in a file called gmsquery.ini, located in the folder where GMS is installed.  The ''' Import''' button allows you to import a list of queries from any file that follows the same format as the gmsquery.ini file.
The defined queries are saved automatically by GMS in a file called gmsquery.ini, located in the folder where GMS is installed.  The ''' Import''' button allows importing a list of queries from any file that follows the same format as the gmsquery.ini file.


== Step 3 - Viewing the Results of the Query ==
== Step 3 – Viewing the Results of the Query ==
Step 3 displays the results of the database query.  Only the first 20 rows are displayed in a spreadsheet.  If the results are not what you wanted, you can go back to Step 2 and modify the query.
Step 3 displays the results of the database query.  Only the first 20 rows are displayed in a spreadsheet.  If the results are not what were wanted, go back to Step 2 and modify the query.


== Step 4 - Assigning Column Types ==
== Step 4 – Assigning Column Types ==
The first 20 lines of the file are displayed in a spreadsheet according to the file outline specified in step 1.  This step lets you pick what kind of data you are importing (see [[GMS:Text Import Wizard Supported File Formats|Supported File Formats]]).  A "no data flag" can be specified for the file.  This is a number that, when encountered in the file, tells GMS to mark the value as "NULL" or "no data".   
The first 20 lines of the file are displayed in a spreadsheet according to the file outline specified in step 1.  This step selects what kind of data to be imported (see [[GMS:File Import Wizard Supported File Formats|Supported File Formats]]).  A "no data flag" can be specified for the file.  This is a number that, when encountered in the file, tells GMS to mark the value as "NULL" or "no data".   


The data in the columns are identified by selecting the type in the combo box at the top of each column in the spreadsheet.  If a row of headings exists, GMS will automatically select the proper type if it recognizes the heading.  Otherwise they are Not Mapped by default.  The available column types changes depending on the GMS data type selected.  Certain column types must be mapped for each file format before the user can progress to the next step in the wizard.  The name of each column is changed by editing the Header cell.
The data in the columns are identified by selecting the type in the combo box at the top of each column in the spreadsheet.  If a row of headings exists, GMS will automatically select the proper type if it recognizes the heading.  Otherwise they are "Not Mapped" by default.  The available column types changes depending on the GMS data type selected.  Certain column types must be mapped for each file format before progressing to the next step in the wizard.  The name of each column is changed by editing the header cell.


After the data have been imported, the [[GMS:Coordinate Transformation]] tools can be used to transform and translate the data.
After the data have been imported, the [[GMS:Coordinate Transformation|coordinate transformation]] tools can be used to transform and translate the data.






{{Navbox GMS}}
{{Navbox GMS}}
[[Category:Importing Data]]
[[Category:Importing Data|Data]]

Latest revision as of 20:47, 9 October 2017

The Database Import Wizard allows accessing data stored in a database and importing it. The wizard is invoked by selecting the Import from Database command from the File menu. The data types that can be imported by the Database Import Wizard are the same data types that are supported by the File Import Wizard.

The Database Import Wizard has four steps:

Step 1 – Connecting to a Database

Step 1 of the wizard sets up a connection to a database. To connect to a database either on the same computer or on a network press the Connect to Database button. Once a database connection is created, a path to the database and the different tables in the database are displayed. When a table is selected its columns are displayed along with the number of rows in that table.

Step 2 – Querying Information from a Database Table

Step 2 gives the option to create, copy, delete, and import queries that retrieve data from a database. To help in writing the queries, the tables in the database are displayed, as well as the columns in the selected table.

SQL Basics

The query is an SQL (Structured Query Language) statement. The SQL statement is entered in the Query SQL statement edit field. An example of an SQL statement would be: “SELECT x, y, z, toluene FROM multipledatasets”. This statement means that columns x, y, z, and toluene from the table multipledatasets will be retrieved from a database. SQL statements are case sensitive. SQL statements also require brackets around table or column names that have spaces. For example, to query data from a table titled “x coordinate” in the SQL statement it would be written as [x coordinate]. A full explanation of SQL is beyond the scope of this document.

A short description for each query can be written in the Query description edit field. The New button creates a new query that has a default name, description, and SQL statement. The Copy button creates a copy of the currently selected query. The Delete button deletes the currently selected query.

The defined queries are saved automatically by GMS in a file called gmsquery.ini, located in the folder where GMS is installed. The Import button allows importing a list of queries from any file that follows the same format as the gmsquery.ini file.

Step 3 – Viewing the Results of the Query

Step 3 displays the results of the database query. Only the first 20 rows are displayed in a spreadsheet. If the results are not what were wanted, go back to Step 2 and modify the query.

Step 4 – Assigning Column Types

The first 20 lines of the file are displayed in a spreadsheet according to the file outline specified in step 1. This step selects what kind of data to be imported (see Supported File Formats). A "no data flag" can be specified for the file. This is a number that, when encountered in the file, tells GMS to mark the value as "NULL" or "no data".

The data in the columns are identified by selecting the type in the combo box at the top of each column in the spreadsheet. If a row of headings exists, GMS will automatically select the proper type if it recognizes the heading. Otherwise they are "Not Mapped" by default. The available column types changes depending on the GMS data type selected. Certain column types must be mapped for each file format before progressing to the next step in the wizard. The name of each column is changed by editing the header cell.

After the data have been imported, the coordinate transformation tools can be used to transform and translate the data.