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...
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?
Hi Martin,
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!
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!
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,
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
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.
Best regards
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.
Fantastic! Thank you
Hi, thanks for this, it’s fantastic! Really appreciate the detail. I’ve managed to follow to this point, if you can post the third part you will make my (and my teams’) day/week/year!
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
Hello, I am also very interested in the description how to connect from excel to ArcGIS. Will you publish it here?
Best regards!
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
Just saw this, I'll put together the how-to to help you out.
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?
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>
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.
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
Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
