0

I'm trying to figure out an easy or efficient way to convert the timestamps contained in GPX files to a different timestamp. I have data for air quality levels saved in an excel spreadsheet and want to match up timestamps between my air quality data and my GPX file so that I can join tables in ArcMap and overlay air quality levels over the GPX track.

To give you an idea of what I mean, this is what the timestamp looks like in the GPX file:

2017-11-07T12:38:55Z

And this is what the timestamp looks like as saved in the excel sheet for my air quality data:

07/NOV/2017 12:38:55

If I could figure out a way to convert one timestamp to another without going through each row and changing them manually then I could use these timestamps to combine the tables from my GPX file and the excel sheet so that I can overlay my air quality data onto a map.

Is there a tool online, in ArcMap, or an easy way in excel to mass convert timestamps so that the two timestamps I posted above are in the same format?

  • Have you checked the Convert Time Field tool? – GISGe Nov 8 '17 at 8:35
  • I tried that, but it doesn't recognize the timestamp displayed in the GPX file. I also tried removing the "T" and "Z" to see if that would change anything. – Javier Nov 14 '17 at 16:22
  • You could convert the format with Python. Could you add a field to your GPX file to store the formatted value, or could you save your GPX to a gdb and add a field to it? – GISGe Nov 14 '17 at 21:04
1

Assuming both fields are text fields, and assuming one of the tables can be imported in an ArcGIS editable format, you could use a cursor to calculate a field holding the converted date value.

Choose whether you want to convert the GPX date or the Excel date and update ds, old_date and new_date accordingly in the code below. Remove the cursor block 1 or 2 depending on your choice, and copy/paste the code in the Python window of ArcMap.

Make sure the dataset is not open in another application because the cursor places a lock on your data.

import arcpy, calendar, locale

locale.setlocale(locale.LC_TIME, "english") # if your month abbreviations are in English. This will overwrite your regional language settings

# path to your imported gpx or xls dataset
ds = r"C:\Data.gdb\dataset

# name of the current date field
old_date = "date1"

# name of the new date field
new_date = "date2"

# add new date field
arcpy.AddField_management(ds, new_date, "TEXT")

# calculate new date field.:

# 1. Use this code if you want to convert your GPX date format to the Excel date format.
with arcpy.da.UpdateCursor(ds, [old_date, new_date]) as cur:
    for row in cur:
        row[1] = """{}/{}/{} {}""".format(row[0].split("-")[2][0:2], calendar.month_abbr[row[0].split("-")[1]].upper(), row[0].split("-")[0], row[0].split("T")[1][0:-1])
        cur.updateRow(row)

# 2. Use this code if you want to convert your Excel date format to the GPX date format.
with arcpy.da.UpdateCursor(ds, [old_date, new_date]) as cur:
    for row in cur:
        row[1] = """{}-{}-{}T{}Z""".format(row[0].split("/")[2][0:4], list(calendar.month_abbr).index(row[0].split("/")[1].capitalize()), row[0].split("/")[0], row[0].split(" ")[1])
        cur.updateRow(row)
0

One approach is to carefully format each table (the GPX data as a layer in ArcMap, and the Excel table) so that the text in the date fields match exactly (this will work, as shown in @GISGge's answer above).

Another might be to simply create a field in the GPX layer's attributes of type "Date", and then populate it based on the timestamp field. Running it through field calculator, your equation could look like this (ensure "Python" radio button is checked):

datetime.datetime.strptime(![name of GPX time field]!,"%Y-%m-%dT%H:%M:%SZ")

Then, this new field would be filled with date stamps that would be much easier to compare 1:1 via a table join with your Excel table (and might reduce error). You might have to do this again with the table in order to make the join happen, in which case you could simply repeat the new Date field process as a key for the join, and use this to populate the field in Field Calculator:

datetime.datetime.strptime(![name of Excel table time field]!,"%d/%b/%Y %H:%M:%S")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.