How to... Export Distribution Data to DMAP from a Database or Spreadsheet

These notes give some general guidance about generating the distribution data file from a General Database or Spreadsheet. The DMAP Help system and documentation describe in detail the structure of data files required by DMAP and how to create them.

The Options

Use a database or spreadsheet which has an in-built DMAP interface which automatically produces the species distribution data files required by DMAP.
An Excel Workbook and an Access Database are available for free download (via the Add-on Utilities topic) which contain a ready-programmed interface with DMAP. These can be used, as supplied, or can be used as a 'model' for implementing it in other spreadsheet or database software.
For more information about this method, see: Quick Start with Excel
Export the species distribution data into a comma-delimited text (CSV) file and then let DMAP convert this file to DMAP format. This facility is available in DMAP V5.6 onward.Further information about exporting data in this way is given below.

Exporting data to a CSV file - Outline Procedure

A note about terminology. Both databases and spreadsheets store data in a two-way Table in which the Rows represent the Records and the Columns represent the Fields. The terminology used, however, differs between databases and spreadsheets. Please note the following synonomy when reading this outline procedure:

Table = Spreadsheet = Worksheet
Record = Row
Field = Column

Make sure that each Record (Row) of your database Table or Spreadsheet has a valid entry for the following Fields (Columns): Species Code and Grid Reference. Species Code must uniquely identify each separate species and can be any string of characters but with no spaces. Grid Reference can be a numeric or alphanumeric Grid Reference or a Latitude+Longitude co-ordinate in a format which conforms to that described in the DMAP Help system. In addition, if you wish to specify mapping symbol types, you will need an additional Field (Column), Symbol Code.
Sort the Records on the Species Code Field so that all Records for each individual species are grouped together consecutively.
Export the data to a CSV file. The procedure for doing this will depend on the particular Database or Spreadsheet software you are using. In some cases, you will be able to specify which Fields you wish to export, but in other cases you might need to make a temporary Table containing only the Fields to be exported. The Fields which you should export are Species Code, Grid Reference and (if present) Symbol Code, in that order.

Detailed examples of stage 3: Microsoft Excel (V7.0)

In the following examples, it is assumed that you have set up a DMAP File Group in which the Distribution Data file is named MYDATA.CSV and the Species Names Dictionary file is named MYDATA.NAM. You can of course substitute your own file names instead. The examples should not be used as exact models of the way to do things but should be regarded as starting points and ideas for developing a method to suit your own particular circumstances. The Distribution Data co-ordinates in most of the examples are British National Grid References, but they can of course be any of the acceptable formats which are described in the DMAP Help system, including UTM Grid References or Latitude+Longitude co-ordinates.

Example 1

In this example, two Worksheets are used within a single Workbook. Sheet 1 is used to store the Species Codes and Names. Sheet 2 is used to store the Distribution Data. The data in these Worksheets should be maintained and added to using the standard Excel facilities. On first creation, the Worksheets should be saved in a named Excel Workbook (XLS) file (File|Save As) and subsequent to this using File|Save after adding new data. Before exporting the Distribution Data to a DMAP data file, Sheet 2 must be sorted on the Species Code column.

In Sheet 1, Species Code is stored in Column A and Species Name is stored in Column B. Because of the way these columns will be written to a text file later, it is important that Column A is set wide enough to accommodate the largest Species Code plus at least one space, and the Species Codes must be left justified (Menu item: Format|Cells...Alignment|Horizontal|Left). For example:

A B

1 3 Acer campestre

2 4 Acer platanoides

3 7 Achillea millefolium

	A	B
1	3	Acer campestre
2	4	Acer platanoides
3	7	Achillea millefolium

Export the data in Sheet 1 to MYDATA.NAM as follows:

Make sure that Sheet 1 is the Active Worksheet.
Select File|Save As... then make the following changes in the dialogue box:
Save in: Dmapw (folder)
Save as type: Formatted Text (Space delimited)
File name: "MYDATA.NAM"
Note the use of quotes("...") around the file name to prevent the addition of the extension '.prn' to the saved file name.
After clicking Save, you will get a warning message that only the active sheet will be saved - click OK.

In Sheet 2, Species Code is stored in Column A, Grid Reference is stored in Column B, and Symbol Code is stored in Column C. For example:

A B C

1 3 SN821997 A

2 3 SN853965 G

3 7 SN826934 A

	A	B	C
1	3	SN821997	A
2	3	SN853965	G
3	7	SN826934	A

Export the data in Sheet 2 to MYDATA.CSV as follows:

Make sure that Sheet 2 is the Active Worksheet.
Select File|Save As... then make the following changes in the dialogue box:
Save in: Dmapw (folder)
Save as type: CSV (comma delimited)
File name: MYDATA.CSV
After clicking Save, you will get a warning message that only the active sheet will be saved - click OK.
Exit from Excel or close the current worksheet before starting DMAP.
On exit, you will be asked if you wish to save changes in 'MYDATA.CSV', warning you that the current file format is not Microsoft Excel Workbook. You must answer No to this question because your data in Sheets 1 and 2 are already saved in Workbook format as described earlier.

Example 2

This example demonstrates how to extract the Distribution Data needed by DMAP from a worksheet which contains additional data for each species record. In this example, Species Code is stored in Column A and Grid Reference is stored in Column D of the Excel worksheet. For example:

A B C D

1 Species Code Date Recorder Grid Reference

2 7 23/8/1987 J.W. Bates SN8594

3 7 4/10/1986 A.J. Morton SN8396

4 7 2/9/1985 J.W. Bates SN8894

	A	B	C	D
1	Species Code	Date	Recorder	Grid Reference
2	7	23/8/1987	J.W. Bates	SN8594
3	7	4/10/1986	A.J. Morton	SN8396
4	7	2/9/1985	J.W. Bates	SN8894

The procedure described below copies these two columns onto a new temporary worksheet and then saves the copied data into a CSV file which DMAP can read. This procedure needs to be performed each time you wish to refresh your mapping data, for example, after adding a significant amount of new data to your spreadsheet. To facilitate this, the whole procedure could be recorded as a Macro.

Start Excel and Open your Workbook file.
Select the Species Code column by clicking on A at the top of the column.
Copy the column by selecting Edit|Copy from the Excel menu bar.
Go to a new, empty worksheet, say, Sheet 3.
Click on A at the top of the first column of Sheet 3.
Select Edit|Paste to put the Species Code data in this column.
Go back to Sheet 1.
Select the Grid Reference column by clicking on D at the top of the column.
Copy the column by selecting Edit|Copy from the Excel menu bar.
Go to Sheet 3.
Click on B at the top of the second column of Sheet 3.
Select Edit|Paste to put the Grid Reference data in this column.
Get rid of the column titles (if present) by clicking on 1 at the left end of row 1 and then select Edit|Delete.
The data are now ready to export to DMAP. Select File|Save As then make the following changes in the dialogue box:
Save in: Dmapw (folder)
Save as type: CSV (comma delimited)
File name: MYDATA.CSV
Then click the Save button. You will get a warning message that only the active sheet will be saved - click OK.
Exit from Excel or close the current worksheet before starting DMAP.
On exit, you will be asked if you wish to save changes in 'MYDATA.CSV', warning you that the current file format is not Microsoft Excel Workbook. You must answer No to this question because your data are already saved in Workbook format as described earlier.

Example 3

{ Based on a suggestion from Raino Lampinen, Finnish Museum of Natural History }
The following example does not give as much detail as Examples 1 and 2, so, if you are not very familiar with Excel, it might be best to read through the earlier examples first. Example 3 illustrates some additional features; in particular:

The concatenation of two columns containing separate Eastings and Northings to form co-ordinates in one of the formats acceptable to DMAP. This technique could also be used to concatenate Latitude and Longitude in an acceptable form for DMAP.
The concatenation of two columns containing the Species Code and Species Name to give distribution data which make a NAM file unnecessary.
By concatenating these items with commas, and then saving as a Formatted Text file, this example gets around the problem that in versions of Excel in some countries, the CSV files saved by Excel have semicolons (;) as separators rather than commas.

In this example, Species Code is in Column A, Species Name is in Column B, Easting is in Column C, Northing is in Column D, and Symbol Code is in Column E. For example, the first row might be:

A B C D E F

1 1 Acer platanoides 366 6767 A

	A	B	C	D	E	F
1	1	Acer platanoides	366	6767	A

Enter the following concatenation formula into Cell F1:
=A1&" "&B1&","&""""&C1&","&D1&""""&","&E1
This creates the following text in Cell F1:
1 Acer platanoides,"366,6767",A
Fill all cells in Column F with this formula by dragging the fill handle of Cell F1.
Select Column F and copy it to the clipboard with Edit|Copy.
Select Column A of a new, temporary, worksheet and use Paste Special (Values) to fill it with the concatenated text from Column F of the original worksheet.
Select File|Save As... Save as Type: Formatted Text (Space delimited), specify an appropriate Folder (for example, Dmapw), and File name (for example, "MYDATA.CSV") to save Column A as a CSV file for DMAP. Note the use of quotes("...") around the file name to prevent the addition of the extension '.prn' to the saved file name.
Exit from Excel or close the current worksheet before starting DMAP.

Alternatively, after completing stage 3, the concatenated text could be pasted into a text editor or word processor program and then saved as "MYDATA.CSV", making sure that it is saved as a Text file rather than a Document.

Detailed examples of stage 3: Microsoft Access (V97)

The following example of exporting DMAP Distribution Data assumes that:

You are familiar with creating and using Queries in Access.
Your Database has Fields for Species Code, Grid Reference and Symbol Code, and these Fields hold data in the correct format for DMAP.
These Field Names are SpCode, GridRef and Symbol, but if they are not, you can make appropriate substitutions in the procedure below.

Procedure:

Create a New Query which has the following Fields: SpCode, GridRef and Symbol.
Set Sort:Ascending for the SpCode Field.
Save the Query with a suitable name, for example, DMAP_DIS
Open the DMAP_DIS Query.
With the resulting Query table on the screen, choose:
File|Save As/Export... form the Access Menu.
Select Save Query To an External File or Database.
At the Save Query In dialogue box:
Browse to the C:\DMAPW folder
Save as type: Text Files
File Name: DMAP_DIS.CSV
Save Formatted: NOT ticked
Then click Export and select Yes to file replacement if asked.
At the resulting Export Text Wizard:
Choose: Delimited, then click Next
Choose: Comma, then click Next or Finish.
To see your data in DMAP, ensure that the CSV file which you have exported is named as the Distribution Data File for an appropriate File Group in DMAP (File|File Group Setup menu item).

If you have any comments, suggestions or queries about these procedures, Contact Alan Morton

Home Page