0

I am trying to create a single file that contains a specific row from a little over 1000 XML (GPX) files. More specifically, I am trying to return row 13, which is the first row that contains GPS locations from multiple files for race events. The XSD for the file is described here: http://www.topografix.com/GPX/1/1/gpx.xsd.

I tried importing the files in Excel but that proved to be a quite manual process.

Here's an excerpt from one of the files:

<?xml version="1.0" encoding="UTF-8"?>
<gpx
  version="1.1"
  creator="Runkeeper - http://www.runkeeper.com"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns="http://www.topografix.com/GPX/1/1"
  xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd"
  xmlns:gpxtpx="http://www.garmin.com/xmlschemas/TrackPointExtension/v1">
<trk>
  <name><![CDATA[Running 6/5/18 4:17 pm]]></name>
  <time>2018-06-05T20:17:57Z</time>
<trkseg>
<trkpt lat="45.424648000" lon="-75.716201000"><ele>57.0</ele><time>2018-06-05T20:17:57Z</time></trkpt>

I am trying to return the contents of the first <trkpt></trkpt> element.

Improve this question
8
  • Do you have experience using PowerQuery? Steps: Open Excel and go to the “Data” tab. Click on “Get Data” and select “From File”. Choose “From Folder” and select the folder containing your XML files. Click on “Combine & Transform Data” under the “Combine” menu button. Select the last node from the left pane. In the query editor, click on “Add Column” and select “Custom Column”. In the formula bar, enter Xml.Tables(File.Contents([FullPath])){13} and click OK. Close and load the query. This will create a single file that contains row 13 from all of your XML files Commented Oct 7, 2023 at 16:39
  • Hi @pcandido! Thank you for your reply. I guess I should be replacing the 'FullPath' in your instructions with the actual path to the folder that contains all the files, correct? When I do so, I get an 'Invalid Identifier' error. The full formula I entered is = Xml.Tables(File.Contents([C:\Users\levesqe\Documents\EL\Perso\Excel\Running\gpx])){13} Does that seem correct syntax? Thanks again! Commented Oct 7, 2023 at 17:30
  • Hi, Eric! Try the same formula but without the bracket [ ] So: Xml.Tables(File.Contents("C:\Users\levesqe\Documents\EL\Perso\Excel\Running\gpx")){13} Commented Oct 7, 2023 at 18:52
  • Now getting the following error message: Formula.Firewall: Query 'gpx' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Any thoughts? Commented Oct 7, 2023 at 19:12
  • Delete everything and start again since it is difficult to guide you if you do not have experience in Power Query. There are steps that are created automatically on the right of the interface. It is possible that you have created one of the steps incorrectly and it warns you of possible errors. Commented Oct 7, 2023 at 19:28
Related questions
1500
Can I concatenate multiple MySQL rows into one field?
781
Import multiple CSV files into pandas and concatenate into one DataFrame
1
Xcode Debugging: Altering the rate of waypoints during location simulation
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.