Data Science & Analytics

How to load into Excel

It’s taken me a while to consider the full implications of having a great API to query. Nearly every application has access to REST API calls and this makes for a really compelling way to access data. For all the years I’ve been using Spotfire, I haven’t given much consideration to Excel. But it’s an important application and it too, is improved through live data. In this blog, I want to show you how to get access to data through Excel!


First you’ll need the location of your instance from your administrator. For our demo database you can use, provided by GeoLogic.

This is the reference to all the data that is available inside the tool. For our purposes, we just want to pull out the well headers. That address is

Alright, on to step one where we setup the data source in Excel.

Setup the Data Source in Excel

You’ll need to use the data source functionality of Excel:

  1. From the Data tab, select Get Data > From Other Sources > From Web.
  2. Let add the web link to the dialog box, and we’ll limit it to 10 like below. Click Advanced and then look specifically at the URL parts.
  3. You’ll come up against a dialog like this, this is the Power Query Editor. We’re going to cheat and I’m going to give you the code to directly connect to it. First press Home.
  4. Click on Advanced Editor, take the code below and replace it with the text I put here:
    Source = Json.Document(Web.Contents("" & "?Limit=20")),
data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"wellId", "name", "operatorName", "entityType", "statusCurrent", "statusCurrentDate", "lastProductionDate", "permitDate", "spudDate", "completionDate", "leaseName", "leaseNumber", "wellNumber", "fieldName", "basinName", "lateralLength", "fracStages", "totalProppant", "totalFluidPumped", "perforationUpper", "totalDepth", "measuredDepth", "formationName", "stateName", "countyName", "countryName", "surfaceLoc", "midPointLoc", "bottomHoleLoc", "production", "prod", "eur", "eurLatest", "typeEUR", "typeEURLatest", "typeWeight", "groups", "id", "extra"}, {"petro.wellId", "", "petro.operatorName", "petro.entityType", "petro.statusCurrent", "petro.statusCurrentDate", "petro.lastProductionDate", "petro.permitDate", "petro.spudDate", "petro.completionDate", "petro.leaseName", "petro.leaseNumber", "petro.wellNumber", "petro.fieldName", "petro.basinName", "petro.lateralLength", "petro.fracStages", "petro.totalProppant", "petro.totalFluidPumped", "petro.perforationUpper", "petro.totalDepth", "petro.measuredDepth", "petro.formationName", "petro.stateName", "petro.countyName", "petro.countryName", "petro.surfaceLoc", "petro.midPointLoc", "petro.bottomHoleLoc", "petro.production", "", "petro.eur", "petro.eurLatest", "petro.typeEUR", "petro.typeEURLatest", "petro.typeWeight", "petro.groups", "", "petro.extra"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"petro.completionDate", "petro.leaseName", "petro.leaseNumber", "petro.basinName", "petro.lateralLength", "petro.fracStages", "petro.totalProppant", "petro.totalFluidPumped", "petro.perforationUpper", "petro.totalDepth", "petro.measuredDepth", "petro.formationName", "petro.countyName", "petro.midPointLoc", "petro.bottomHoleLoc", "petro.production", "", "petro.eur", "petro.eurLatest", "petro.typeEUR", "petro.typeEURLatest", "petro.typeWeight", "petro.groups", "petro.extra"}),
    #"Expanded petro.surfaceLoc" = Table.ExpandRecordColumn(#"Removed Columns", "petro.surfaceLoc", {"latitude", "longitude", "type", "coordinates"}, {"petro.surfaceLoc.latitude", "petro.surfaceLoc.longitude", "petro.surfaceLoc.type", "petro.surfaceLoc.coordinates"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded petro.surfaceLoc",{"petro.surfaceLoc.coordinates"})
    #"Removed Columns1"
  1. Press Close & Apply, you’re finished!

Leave a Reply

Your email address will not be published. Required fields are marked *