Last modified by Maptitude Support - 9 months ago
179 Views
2 min read

Why isn’t my data being recognized as Latitude/Longitude Coordinates?

When you are importing data into Maptitude to create a pushpin map, one of the options for locating the data is to use Latitude/Longitude coordinates already present in your data. To do this, you simply need to match up these fields with the X and Y options in the Matching fields:

However, sometimes you may try to match these fields up in this way and the Longitude/Latitude fields you are looking are not available in the list:

Here, I have a field in my source file called Longitude, but it won't let me pick it from this list. This article discusses why these fields may be missing and what we can do to fix it.

Why can’t I select the field I want?

When Maptitude is looking for a field to use as the Longitude or Latitude field, it is only looking for fields that are numeric, i.e. they only contain numbers. You can see in the above example that it would let me choose ID or QTR1 Sales as the X field, because both of these fields only contain numbers.

The issue is that if the Excel file contains even one row where the Longitude field has text in it instead of a number, that field is now considered a Text field and cannot be used as a coordinate field. This is the most common reason that the field you want to use will not be showing up here.

Another reason may be that you have your data in one field, e.g. (-105.271755, 40.021697) instead of two separate fields. For that issue, you can follow the same steps as those used in this article to split the one column into two.

What do I do if my data has text in it?

Similarly to when you are trying to get Maptitude to read a field as a Date, it can be hard to tell which row(s) in Excel is/are the source of the issue. We can use the VALUE function from Excel to quickly and easily identify all the cells in a given column that are stored as text:

  1. Open the Excel file that contains your data. You can see in my example it is very obvious where the errors are but if there were thousands of rows this would be much harder
  2. Insert a new column after each of your Longitude and Latitude fields
  3. In the first cell of the column after Longitude, type =VALUE( and then click in the first cell of the Longitude field and then type ). Hit Enter

  4. Drag down to copy the formula to the rest of the rows
  5. Repeat for the field next to Latitude

If the entry in the initial field was already a number, then the value in the new field will be exactly the same. If it were stored as text in some way, the new field will just say #VALUE

  1. Sort the newly created fields from Largest to Smallest, which will bring all the problem records to the top
  2. For each record where the new field says #VALUE, you will need to either:
    1. Delete the row
    2. Fix the entry into a numeric value (you can check this has been done since the #VALUE should disappear)
    3. Completely clear the cell, which will keep this record in the excel file but will not place it on the map
      1. Note, don’t fill the cell with just 0 since this will place the point at the (0,0) coordinate on the map
  3. Once all the rows are fixed for Longitude, repeat the same for Latitude
  4. Now all the records are fixed, you can delete the extra fields you created and save the file
  5. You can import into Maptitude, and you should be able to select Longitude and Latitude as the X and Y field now that they are numeric