Wondering how others have used Excel with Arcgis Online, since this is a common query from clients. I've been using Power Query ( see this excellent walk through) wth Arcgis and it's very easy to setup.
Thanks,
PART 2 Create your temporary token
1) To receive your token for your application replace CLIENTID and CLIENTSECRET with your own.
https://www.arcgis.com/sharing/rest/oauth2/token/?client_id=CLIENTID&client_secret=CLIENTSECRET&grant_type=client_credentials
2) New query, from web and enter the url with your IDs.
3)choose advanced > ok
4) Choose EDIT
5) Select Data Source Settings
6) Change Source and select open file as JSON.
7) press advanced editor, we'll now edit the code so that it points to our tables. modify to code to
let//Custom Variablesaccess = Excel.CurrentWorkbook(){[Name="AGOL_ACCESS"]}[Content],client_id = access{0}[Value],client_secret = access{1}[Value],
Source = Json.Document(Web.Contents("https://www.arcgis.com/sharing/rest/oauth2/token/?client_id=" & client_id & " &client_secret="& client_secret & "&grant_type=client_credentials"), 65001),
#"Converted to Table" = Record.ToTable(Source)in #"Converted to Table"
By having variables you'll now be able to reuse this excel table for other application tokens, as it points to the table that you previously created.
Next you'll need to get the service URL for your feature layer...
PART 3 coming...
Hello Jean-Yves!
Most of the customers I speak with use ArcGIS Maps for Office. This may not do everything you need, but I'd definitely consider taking a look, as it gives pretty seamless flow between ArcGIS Online and Excel.
Check out: ArcGIS Maps for Office
You can also check in with your Account Manager for more details on this product in relation to purchasing it for your account if you find that it's what you need.
Enjoy!
Rachel G
Esri Support Services
Thanks Rachel,
Yes agreed Arcgis Office is quite useful indeed. However in our case we are having to update data tables from multiple surveys and image attachments built with Collector and survey123. By using PowerQuery you can access the arc Rest to access tables and attachments without having to do much coding at all, pretty nifty.
Wanted to add that with Excel powerquery you may also show the URL to your hosted feature attachments.
1) Create the Query to your attachment table
2) Customize a column;
= SERVICEURL & parentObjectId & "/attachments/" & attachmentID & Token
You can then open all your attachments using the hyperlink from your custom column (note that your token will need to be constantly refreshed).
BONUS: With a little VBA you can then download all your photos to a local folder using the attachment URL. (modified from excel - Convert code for 32 bit to 64 bit - Stack Overflow )
*update: Added a line to add images that are not already present in the folder, reducing downloading time.
Option Explicit Private Declare Function URLDownloadToFile Lib "urlmon" _ Alias "URLDownloadToFileA" (ByVal pCaller As Long, _ ByVal szURL As String, ByVal szFileName As String, _ ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long Dim Ret As Long Sub Download_image_structure() Dim ws As Worksheet Dim LastRow As Long, i As Long Dim countupdate As Long Dim strPath As String Dim FolderName As String Dim fs Set fs = CreateObject("Scripting.FileSystemObject") FolderName = "W:\IMAGES\STRUCTURE\" '<-- folder where images will be saved '~~> create folder if it does not exist If Len(Dir(FolderName, vbDirectory)) = 0 Then MkDir FolderName End If '~~> name of sheets with image to download Set ws = Sheets("STRUCTURES_PHOTO") LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row For i = 2 To LastRow '<~~ 2 because row 1 has headers strPath = FolderName & ws.Range("F" & i).Value If Not fs.FileExists(strPath) Then '<-- if image does not exist in folder then skip to next Ret = URLDownloadToFile(0, ws.Range("I" & i).Value, strPath, 0, 0) countupdate = countupdate + 1 End If Next i MsgBox countupdate & " added images" '<-- how many image added to folder End Sub<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
Hi, we're super interested in this also - ArcGIS Maps for office doesn't deliver exactly what we're after. unfortunately the linked post (to rwalker.info) is no longer available - any chance you could share that content? Rashan Walker, is that your blog?
Just saw this, I'll put together the how-to to help you out.
Brilliant! thanks heaps! I've managed to map out the JSON endpoint to the attribute data with a bit of trial and error, but it'd be great to validate if i'm doing it effectively/correctly. The main challenge I'm having is for authenticated services; I haven't worked out how to connect to anything but a publicly shared endpoint
Hello, I am also very interested in the description how to connect from excel to ArcGIS. Will you publish it here?
Best regards!
Still travelling but I'll try to summarize best I can here:
PART 1: CREATE TOKEN
d.Give your application a name, then click "Register"
e. Note the Client ID and Client Secret
IN EXCEL: Insert three Tables
Enter the Client ID and Client Secret you just copied from the application you just created.
I like to name my tables;
From Top to bottom: AGOL_ACCESS, Token_auto, Hosted_FS0
PART 3 Fetching data from the service url and loading it into Excel
Since your survey could be setup in multiple different ways, I'm going to be using the "Nested Repeats" sample from Survey123 connect. The "Nested Repeats" survey is setup where one campground may have many campsites, where campsites may have many equipment, where equipment may have a picture attached.
Layer(0) -Campground
Layer(1) -campSite
Table(2) - campsite_equipement
Table(2) attachments (images)
1. Create sample survey from survey123 connect
2.Once you've published your survey to arcgis online, you should now see your survey under content in a folder named "Survey-Nested Repeats". Press on the feature layer.
3.For this feature layer we can observe that there are 2 layers, and 1 table. You'll notice that each layer and table have a service url. Press on the NestedRepeats Service URL.
4. You'll now find yourself in the ArcGIS REST Services Directory. If you scroll down the page you'll note that under relationships we can see that there is a relationship to the layer campSite (1), as you would expect...
5. At this point you can copy the service URL for the Campground layer. which should look something like this:
Copy up to "FeatureServer/0/" and Save it for later.
6. Scroll to the bottom and click on query. From this table you're able to query your layer. To query all data enter "objectid>0" for Where and wildcard "*" for Out Fields, and "JSON" for Format.
Press Query (Get) ----You should then see all your collected information for that layer in the form of JSON. This is what we want in excel!
7. Now we'll need to recreate this query in Excel. paste the URL you copied at #5 into the table Hosted_FS0. and just like the Query we just did enter objectid>0 for Where and * for Field.
8. Add a new Query (Refer to part 2) and modify code in advanced editor. Once modified press done.
let//Custom VariablesHosted = Excel.CurrentWorkbook(){[Name="Hosted_FS0"]}[Content],fsURL = Hosted{0}[Value],whereClause = Hosted{1}[Value],fieldsOut = Hosted{2}[Value],
Token_auto = Excel.CurrentWorkbook(){[Name="Token_auto"]}[Content],token = Token_auto{0}[Value],
//Get Data Source = Json.Document(Web.Contents(fsURL & "query?where=" & whereClause & "&outFields=" & fieldsOut & "&returnGeometry=false&f=pjson&token=" & token), 65001)in Source
*The variables in your Hosted_FS0 tables and your token will be merged and the resulting URL will be used to query your survey data.
9.We'll need to "drill down" the Json in order to properly present the data. Right Click on list then click "into Table"
Right click on "List" for features then click "drill down"
Right click on List (column header) and click "To Table" - press ok
click on arrow button, unclick "Use original column name as prefix", click ok
click on arrow button, unclick "Use original column name as prefix", You'll note that these are the columns from your survey. Press ok.
10. If you added campgrounds then you should see your campground data. Exit of the power Query Editor and Keep your changes
*(You'll note that the time is in unix so you'll need to convert it see my other post here How do you convert epoch dates in Excel, Power Bi (query), Access from geodatabase.
11.Your new sheet will now show a new table with all your survey data. You can do this for all your feature layers and tables.
Fantastic! Thank you
It worked like a charm! I skipped some steps, like creating the tables, and used manual entries for the variables; but worked perfectly.
But ... I hit the feature query limit. I know that there is no limit when querying the objectids, so you can query them first, and then you can query progressively in blocks until getting all the data. mmmmm... it will take me a while to figure out that. Any suggestions?
Thank you again for sharing all this information.
Best regards
Hi!
There are some forums that request you to show some sort of research and effort to solve your issue before asking for help. So, this is what I tried; that worked, but I understand it is not an efficient way to do it. Please bear in mind that this is my first experience with Excel Power Query :O)
//Custom Variables
.
whereClause = "some_field is not null",
//(remember that I skipped the table creations, so I needed to include here the actual values)
Source = Json.Document(Web.Contents(fsURL&"query?where="&whereClause&"&outFields="&fieldsOut&"&orderByFields=objectid&returnGeometry=false&f=pjson&token="&token),65001),
whereClause = “some_field is not null and objectid > 55000“,
This is because I looked for the last objectid from the first query, remember that it is ordered by objectid. This created another worksheet with the next 10,000 block of data.
This created the Append1 query you can see in the list; and it worked quite well for my experience with Power Query.
I ran your original process changing the where clause to:
whereClause = "worked_by is not null",
and adding &orderByFields=objectid&returnIdsOnly=true to the source line:
Source = Json.Document(Web.Contents(fsURL&"query?where="&whereClause&"&outFields="&fieldsOut&"&orderByFields=objectid&returnIdsOnly=true&returnGeometry=false&f=pjson&token="&token),65001),
then converted into table and drilled down etc. etc. etc.
let
Query7=Excel.CurrentWorkbook()
{[Name="Query7"]}[Content],
objectid = Number.ToText(Query7{9999}[Objectid]),
whereClause = "worked_by is not null and objectid > ",
Source = Json.Document(Web.Contents(fsURL&"query?where="&whereClause&objectid&"&outFields="&fieldsOut&"&orderByFields=objectid&returnGeometry=false&f=pjson&token="&token),65001),
for the second query.
Then:
objectid = Number.ToText(Query7{19999}[Objectid]),
objectid = Number.ToText(Query7{29999}[Objectid]),
objectid = Number.ToText(Query7{39999}[Objectid]),
objectid = Number.ToText(Query7{49999}[Objectid]),
objectid = Number.ToText(Query7{59999}[Objectid]),
for the remaining queries (two to six, respectively, as the first and second ones where already correctly configured). I have a Query0 because I implemented the whereClause = “some_field is not null and objectid > 55000“, as my first query, and missed the first 10,000 points where objectid > was not needed.
Now, I only need to refresh the objecids query, and then the Append1 query to gather all the data at once. Again, not efficient, but working :O)
So, what when my data layer increases over 100,000 features, or over 200,000? Do I create another four, five, ten more queries and append them all? I don’t think so. But looping around in Power Query seems a scary thing, as Power Query is not a conventional programming language, and remember that this is my first experience with it. Any help will be greatly appreciated.
Hi Martin,
I'm not convinced I understand what you're trying to accomplish, but if I understand correctly you have an issue on the maximum records you can query?
Have you tried modifying your maxRecordCount parameter?
How To: Update the maximum record count for feature services in ArcGIS Online
Hello There!
Thanks for taking care.
I am not the administrator to perform such a change; I am just an end user,
without ArcMAP. We just get the data and prepare some statistics and
graphics.
The layer has over 60,000 features we are interested in (from nearly half a
million), and I can only query 10,000 features at a time. So, I thought
that a loop would do the trick, but it seems that this is not the case with
Power Query (I think a loop can easily be configured in Python from ArcMAP,
but ...).
I am actually good with the explained approach, and I just need to add a
new query from time to time; so it is not a big deal. I am pretty happy
with your tutorial on how to access the data; I was just looking for some
efficiency improvement.
Best regards,
Hello all,
I'm struggling to figure out which values i need to input for the Advanced Editor Script. so far i have entered the folowing Variables in red(see below):
let//Custom VariablesHosted = Excel.CurrentWorkbook(){[Name="Hosted_FS0"]}[Content],fsURL = Hosted{0}[https://services1.arcgis.com/E5n4f1VY84i0xSjy/arcgis/rest/services/service_6c410d1aeeec49f4be7a0b951cbf81de/FeatureServe… ]Token_auto = Excel.CurrentWorkbook(){[Name="Token_auto"]}[Content],],whereClause = Hosted{1}[objectid>0],fieldsOut = Hosted{2}[*],
token = Token_auto{0}[868JC4M971rlJUGHDKjd...],
//Get DataSource = Json.Document(Web.Contents(fsURL & "query?where=" & whereClause & "&outFields=" & fieldsOut & "&returnGeometry=false&f=pjson&token=" & token), 65001)inSource
Am i missing anything here or have i entered the correct inputs for this script? Thanks
Hello Tory!
What I see for this code is that there is a missing comma (,) after the fsURL statement; and an extra "]," after the Token_auto = statement.
Please take a look and let us know. We are very tankful with this solution, as we use it quite commonly, and works like a charm. Hope this helps.
Best regard,
Hi Martin, thanks so much for your response!
I have made a few changes based on your comment, but what i'm getting now is the error message 'Invalid identifier' for the fsURL. I double checked my format for the URL and it seems to be the same as the example listed in this forum.
Any advice you could provide would be greatly appreicated!
Cheers
Hey Tory!
I hope we get this working, as it worked for me, and I would like to give back a little.
May I ask if your are showing the values of the table into the statements for easy understanding of your post, or are they in the table and the statements really have [Value] in the code as in:
whereClause = controltable{1}[Value],?
I ask because, if they are in the table and the URL has quotes in there, take them out (just as it is in your previous post code, where the URL has no quotes). Otherwise, as I changed the code for myself, I really have no entries in any table for the URL, so my statement is:
fsURL="https://arcgis.p.../FeatureServer/0/",
To test it, you can get rid of the tables temporarily and write the code as:
let//Custom Variables//Hosted = Excel.CurrentWorkbook(){[Name="Hosted_FS0"]}[Content],fsURL = "https://services1.arcgis.com/E5n4f1VY84i0xSjy/arcgis/rest/services/service_6c410d1aeeec49f4be7a0b951cbf81de/FeatureServer/0/",//Token_auto = Excel.CurrentWorkbook(){[Name="Token_auto"]}[Content],],whereClause = "objectid>0",fieldsOut = "*",
token = "868JC4M971rlJUGHDKjd...",
as these variables are really all strings being concatenated in the Source statement.
Take a look and let us know :O)
Martín
Exciting News Martin!
I have got the script to work and it is providing the table of attributes as hoped:
The only issue now is when i go to 'Close & Load' i am getting the error message 'Download failed' with no detail. Even though its showing all the rows for the survey.
If there's any advice you have for this step it would be great. Thanks so much for all your help, it has been much appreciated!
Don't worry about my previous email, i have worked out the process and it is operational!
Thanks for all your help, this is definitely a game changer for my work!
GDay Martin,
Did you ever find a solution to pagination? There is a youtube video we managed to follow here which works: How to do Pagination without knowing the number of pages (Part 2) in Power Query | List.Generate - YouTube
The problem we are now having is that the token fails intermittently, so when we expect 180K records we may get 40K, or 120K, or 180K. Anyone know why the token could be dropping out on us?
Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
