Data Import

Data Import from Tab-Delimited Text files

If you have records stored in an existing database you may be able to export the data in a plain text file using a 'tab' character as a delimiter between the fields of each record and a 'newline' character as the delimiter between each record. This is often referred to as a 'tab-delimited file'. MapMate has a 'Text Importer' to read data from tab-delimited text files. 

Details of text-import are available in the MapMate Help system; if you enter the word "importer" into the 'Help' > 'Help Contents' > 'Find' search field it will locate the appropriate section (click here for details on Help) 

Beginners Excel Tips

Excel, or similar spreadsheet programs, can be used for rearranging existing tab-delimited text data files for import into MapMate. The following are some beginners tips that may prove useful in preparing data for import into MapMate.

The data must be rearranged, if necessary, to put the fields in the order required by the MapMate importer and be given the following column titles.

The Taxon name must be the scientific (Latin) name rather than the Vernacular or Common name. The 'VC' column contains the Vice-County Number for the site and the 'Date' column must have a date with a full four-digit year in the 'dd/mm/yyyy' format. The separator between the days, month and year must be the 'forward slash' ('/') character, a 'hyphen' or 'minus' ('-') character is used to separate the first and last date if the record spans a range of dates (e.g. 01/04/1999-02/04/1999 for First to Second of April 1999).

When there is a lot of editing needed on an import file it may be easiest to save the data in Excel format and export to a text file after editing. When the MapMate importer detects errors change them in the Excel-format file and then save as text again to re-import into MapMate. The extra time in saving 'as text' in addition to saving the changes in the Excel file is balanced by the slower re-opening of a text file through the Excel import Wizard and avoids having to specify the date format each time the text file is opened in Excel. 

If the text file is opened and edited in Excel the date column often reverts to an Excel default format with a two-digit year, this can be prevented by defining the column containing the dates as 'Text' rather then leaving it as the default 'General' format in the Excel File Conversion Wizard.

Remember that, because of the row of titles, the row number in Excel is one greater than the record number reported when importing into MapMate so it is easy to be looking at the previous record for a reported error.

It may just be superstition but may be worth making sure that there is some text in the comment field of the last record of the file (even if it is just a dot). This prevents Excel trying to reduce the file size by omitting trailing empty fields in the export text file (missing fields before the end of the line have been known to confuse the MapMate Importer). If the importer has problems with empty fields in the import file it is recommended to fill them with two adjacent 'double quote' characters ("") to signify an empty field.

When editing a long file in Excel, with the field names in the first row, it helps if you highlight the second row (first row of the data) and from the 'Window' menu select 'Freeze panes'. This should keep the row of field labels at the top of the screen as you scroll down so you can always see the name of the column that you are editing.

It can be helpful to sort the records in Excel so that all the records for a particular Taxon or Site are grouped together. Normally the records (lines in Excel) are sorted according to criteria in columns of your choice. However if a column is selected (highlighted) Excel will assume that you want to sort just that column and not the others. If this happens, and the file gets saved before you notice, your data may be irretrievably scrambled (unless you have a backup copy from before the sort). If you request a Sort in Excel and are not presented with a screen asking for the sequence of the columns on which the sort is to be ordered you should cancel the process immediately and make sure that you have either all, or none, of the columns selected. 

When the importer fails to recognise a species due to miss-spelling you can go to the MapMate 'Analysis' queries and select 'Browse Taxa Library' and sort the output on 'Taxa', 'Vernacular' or 'Parent' to try to locate the correct spelling (or changed name). If successful you can copy the name to the Windows Clipboard (using the 'Copy' button at the top of the Analysis - Results form) and paste it into the import file when it is open in Excel. (If the miss-spelling turns out to be in the MapMate Taxon Library use the spelling as required by MapMate to import the records. If you then report the problem to MapMate Support (on the MapMate Web site at www.mapmate.co.uk) the Taxa Library will be updated by a subsequent 'Patch' which will correct your records).

The following tips are from Dr Malcolm Smart:
To format the Date column with a 4-digit year as required by the MapMate Importer; highlight the column, go to the 'Format' menu, select 'Cells', and then 'Custom' from the bottom of the list of available formats. Select 'dd/mm/yy' from the second list that appears and add yy in the box above the list make it 'dd/mm/yyyy'. Then click 'OK'.

If you want to add a comment to every record in an import, (e.g." data checked by ....", "data sent to site owner" or "this survey supported by the following organisations ...") in addition to any existing comment, delete the contents of the 'Comments' column to the clipboard and paste them into a column further to the right (e.g. column O). Then in the original Comments column in cell2 (assuming the first row is used for the labels) enter the formula:-

=O2 & " text"

where text represents the note to be added to the comment field. It needs to be enclosed in quote marks (") and to start with a space to separate it from any previous text. The cell should now display any original comment followed by your text. If this is correct, copy and paste the expression into the remaining cells of the row. This procedure should be done after any rearranging of columns that may be needed to fit the MapMate import specification. The original Comments column needs to be deleted from the tab-delimited text format file so that the importer does not try to include the next column in the comment field. To do this it is necessary to re-open the text output file in Excel and delete the original comment field, column 'O' in this example (remembering to specify 'text' as the format for the Date field in the Import Wizard).