4.2 Import Database
Level of expertise required for this Chapter: Advanced; general Map Maker training
1. Create a database file with attribute data in Excel
In Excel, create a spreadsheet and enter (or import) values for the database, which will then be linked to the geographical features (polygons, lines or points).
Have a column (recommended name ‘ID’) corresponding to the ID in Map Maker. Each ID in this column must match one label at the GIS. The spreadsheet must have as many rows (plus the title row) as there are mapping units at the GIS.
Note 1: There can be exceptions from this rule at Map Maker, but this is NOT to be considered at LUPMIS.
Note 2: If a numeric value is missing, it is recommended to enter -99, in accordance with the definition for ‘undefined value’ at File > System set up > Preferences > Miscellaneous > Unspecified data value.
All other field names depend on the task.
The ID column must be text format (in Excel, use Select > right-mouse > Format cells > Number > Text > OK to convert to text). Formulas should be converted to values (Select > right-mouse > Copy > go to new cell > right-mouse > Paste Special > Values > OK; see also Annex 15.2).
Note 3: Check, that the spreadsheet is 'clean' and does not have empty columns, rows, empty values, or columns without name in the top row.
- - - - -
2. Save this file as an XLS file. (In MS Office 2007: ‘Save As’ > MS Excel 97-2003 Workbook operation)
- - - - -
3. Import this database file to Map Maker
In Map Maker: Right-mouse > Project manager > Project manager window: Select the layer which matches to the database to be linked > Data link > Choose database > Select file type: Excel spreadsheet > Select folder and file > Open > Sheet1 > OK > Back in the Project manager window, the ‘link column’ in the database should point to ‘ID’ (or No) > OK
From here, you can follow instructions in Chapter 4.3 to display this database in various alternative ways.
- - - - -
You can also add a database / spreadsheet to the existing attribute database in Map Maker: Have the DRA file in the live layer, then: Main menu > Edit > Live layer data > Merge data table with live data > Select file type (can be DBF, MDB, XLS or csv file) > Select folder and database file, as prepared earlier > OK > Choose table window: Select Sheet1 > OK > Choose link column window: Select id > Leave both boxes ticked > OK > Pick columns to import window: Select the fields you want to import, except id > OK >
- - - - -
You have now created a DBF file with the database (‘attribute data’), which has the same file name as the master DRA file. The two files (DRA and DBF) are not automatically assigned to each other.
4. Display imported database
In Map Maker: Right-mouse > Project manager > Project manager window: Select the layer just linked with the database > Data link > On hit > Tick: Make layer hit-able… > Database > OK
With the Data-tools from the toolbar left, you can now hit the object, and it will display the content of the database of this feature:
- - - - -
If you want to keep this link (graphical DRA file with attribute data DBF file) permanent, it is the best to:
1. Copy this layer to the live layer,
2. Re-import the database (unless you have loaded the database already with the layer): Have the DRA file in the live layer, then: Main menu > Edit > Live layer data > Merge data table with live data > Select file type (can be DBF, MDB, XLS or csv file) > Select folder > Select database file, as prepared in the previous step > OK > Choose table window: Select Sheet1 > Leave both boxes ticked > OK > Choose link column window: Select id > Leave both boxes ticked > OK > Pick columns to import window: Select the fields you want to import, except id > OK >
3. Save it (through Save live layer as …), which creates both the DRA file and the DBF file.
- - - - -
From here, you can retrieve these attribute data in various formats (see for example Chapter 4.3).
For some retrieval operations (see examples in Chapters 4.3 and 6.9), you need the data in the DRA file (in fields label, style or even id). It is recommended, to use:
A) Field style for ‘small’ numbers to identify classes or colours: To transfer from DBF to DRA file: In live layer mode > Right-mouse > Live layer actions > Basic operations > Set styles by data > OK > Choose data column window: Select field to be transferred > OK
B) Field label for any text or ‘large’ numbers: To transfer from DBF to DRA file: In live layer mode > Right-mouse > Live layer actions > IDs and labels > Amend labels and IDs > OK > Amend labels and IDs window > Set display label to database > Select source field > OK
Note 4: If the data formats are not the same (text, numbers), the ‘Set display label to database value’ will not show the source field. You then have to either convert the format (in live layer > Main menu > Edit > Live layer data > Edit data > Select column to be converted > Edit columns > Change column definition > Adjust field type > OK > OK) or go through an intermediate copy process (in live layer > Main menu > Edit > Live layer data > Edit data > Select field ‘Spare’ (to where it should be copied > Column > Calculate > Select source field > Apply formula > Table > Check, if it has been copied > OK)
There is also a ‘Set display label to external database value’ option. Test it.