ArcGIS Online attribute table to Excel – the most rapid export
Each layer uploaded or produced in ArcGIS Online has its attribute table. It’s the same as in other GIS tools like QGIS or ArcGIS Pro. If so, the user can export it to the spreadsheet and save it later as a .csv or .xslx file, for example. The classical exporting method is through the layer Overview in ArcGIS Online (Pic. 1), which gives you the file with coordinates.
Pic. 1 The classical way of exporting the AGOL layer to Excel.
The way I would like to show you is much quicker, although the received data doesn’t have coordinates unless you provide them separately by the other occasion.
- Get the link (including the token) to your ArcGIS Online layer by following the steps below (Pic. 2). The link can be obtained by visiting the ArcGIS REST Services Directory (2) and copied to the clipboard by mouse right-click -> Copy link address option (3).
Pic. 2 Getting the layer link with the token, where: 1 – Enter the layer details; 2 – View layer URL in ArcGIS REST Service Directory; 3 – Copying the layer to the clipboard.
- When you paste your layer link to the notepad or elsewhere, it will look as follows:
https://services.arcgis.com/LlhmDFF7e4n75dSf/arcgis/rest/services/your_layer_name/FeatureServer/16?f=pjson&token=your_token
This link is insufficient for getting the attribute data content outside ArcGIS Online. You can read about the key layer properties instead. Considering all records, the following query must be added to the link:
/query?where=1%3D1&outFields=*&returnGeometry=false&resultRecordCount=0&f=json
3. Finally, your link should look as below:
https://services.arcgis.com/LlhmDFF7e4n75dSf/arcgis/rest/services/your_layer_name/FeatureServer/16
/query?where=1%3D1&outFields=*&returnGeometry=false&resultRecordCount=0&f=json&token=your_token
The red part of the string marks the necessary elements which must be replaced. Doing it everytime might be quite tedious. Therefore, I am proposing the Excel function, which will prepare the link whenever the new layer is considered. Assuming that your layer link with the token has been copied to cell A3, the formula would look like this:
=SUBSTITUTE(A3,"?f=pjson","/query?where=1%3D1&outFields=*&returnGeometry=false&resultRecordCount=0&f=json")
Then, the output link can be copied and used in the URL connection. Preferably, I would use the Paste as Values option for another cell as the first to make sure that we are not copying the formula body instead.
4. Go to Data -> From Web and paste the prepared URL for your layer (Pic. 3). The Basic option, as default, can stay.
Pic. 2 Adding data to Excel from Web.
When the link is pasted, click OK and wait a while. Finally, the PowerQuery is opened (Pic. 3). There are several things to look at. When the window is opened, you can spot the list of variables fetched from ArcGIS Online Rest API. For us, the most important are features (1). You must click on the “list” hyperlink, which will be expanded underneath (2). Now, your window includes a big list of Records, although we don’t know anything about them. The To Table option is required for extracting them (2). Meanwhile, look at the name of your PowerQuery window, which reflects exactly the query included in your layer link and described above (2A). The To Table options are set as default and we don’t need to change them. By clicking OK you are redirected to the next step, in which worth looking at is the “expand” icon (3). By clicking on it, the new box is opened (3A), in which we can specify what columns do we want to expand. Because all of them are bound into one called attributes, we should leave it as it is. However important is the “Use original column as prefix” box, which should be untick. After confirmation, the list stays visually the same (4), but once the expand icon is clicked again, we receive the box with list of all the columns available in the layer attribute table. When we confirmed it with the OK button, all the data comes out nicely!
Pic. 3 Loading ArcGIS Online data to PowerQuery: 1 – Expanding features list; 2 – Converting records to table; 2A – The query used in the layer link; 3, 3A – Expanding attribute columns; 4 – List of columns to expand; 5 – Ready attribute data in PowerQuery editor; 5A – The “Applied steps” log; 5B – PowerQuery main bar for the data transformation.
By the occasion you can take a look at the history of your changes in the Applied Steps box (5A). If something went wrong, you can back easily to the certain step and repeat or change the process. Basically, at this stage there are options for transformation of your data by using the toolbar on the top (5B), about which I am going to write in the future. So far lett’s assume, that the data is ready to go! In this event, go to the main bar -> Home -> Close & Load. When your data is large, you will need another while for loading it. At the end, everything should come nicely as Excel spreadsheet, in which all the columns are ready for filtering. The Excel spreadsheet name derives from the query we added to the layer link (Pic. 4).
Pic. 4 The ArcGIS Online attribute table data is finally displayed as the Excel spreadsheet.
The primary difference of this method with the classical one described at the very beginning of this article is lack of geometry. You can use this output for producing some reports, etc. About the reports you will have another text in the nearest future.
Mariusz Krukar
Links:
- https://help.cadasta.org/docs/kb/arcgis-pro/exporting-excel-and-csv-files/
- https://help.instantatlas.com/instantatlas-integration-with-power-bi/
Forums:
