Select to view content in your preferred language

Exporting Hosted Feature Layer to CSV

1380
2
Jump to solution
03-14-2023 06:48 AM
TheGamer
Occasional Contributor

Hi everyone, I was wondering if there is a way to get the data from the hosted feature layer (using attribute filter) through request URL module in ArcGIS API Javascript using either a request URL or something similar to this:

TheGamer_0-1678801588622.png

I'm basically trying to get the JSON format of the data and then convert it to CSV and allow users to download data when using the application.

 

@UndralBatsukh @JoelBennett @Sage_Wall @ReneRubalcava 

 

0 Kudos
1 Solution

Accepted Solutions
UndralBatsukh
Esri Regular Contributor

Hi there, 

You can use the esri/request to get the data in geojson format (provided that it is your data). I am sure there are different libraries or approaches of converting geojson to csv.

document.getElementById("queryButton").addEventListener("click", ()=>{
  let url = "https://services.arcgis.com/V6ZHFr6zdgNZuVG0/arcgis/rest/services/Landscape_Trees/FeatureServer/0/query?where=1=1&f=pgeojson";
  esriRequest(url, {
    responseType: "json"
  }).then(function(response){
     // The requested data
     let geoJson = response.data;
     console.log(geoJson)
  });
});

 

View solution in original post

2 Replies
UndralBatsukh
Esri Regular Contributor

Hi there, 

You can use the esri/request to get the data in geojson format (provided that it is your data). I am sure there are different libraries or approaches of converting geojson to csv.

document.getElementById("queryButton").addEventListener("click", ()=>{
  let url = "https://services.arcgis.com/V6ZHFr6zdgNZuVG0/arcgis/rest/services/Landscape_Trees/FeatureServer/0/query?where=1=1&f=pgeojson";
  esriRequest(url, {
    responseType: "json"
  }).then(function(response){
     // The requested data
     let geoJson = response.data;
     console.log(geoJson)
  });
});

 

JeffreyWilkerson
Occasional Contributor III

I don't think there's a way to do this directly out of an ArcGIS REST web service. I utilized a separate libraries to support doing this, namely:

<!--Libraries for importing and exporting Excel data-->
<script type="text/javascript" src="//unpkg.com/xlsx/dist/shim.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.6/xlsx.full.min.js"></script>
<script type="text/javascript" src="//unpkg.com/blob.js@1.0.1/Blob.js"></script>
<script type="text/javascript" src="//unpkg.com/file-saver@1.3.3/FileSaver.js"></script>

Following the logic of this page from 'Ferry Ren' https://www.cnblogs.com/zhenggaowei/p/11732170.html, I built a procedure to pull the data from the REST endpoint into a JSON string, and then exported it into an Excel file.

The main thing to keep in mind is that if you have more than 2000 records to be exported, you need to keep pulling them until you get them all. I 'borrowed' the concept of using a recursive function from Gavin Rehkemper (https://gavinr.com/arcgis-javascript-query-all-features/ ).  It looks something like this:

// Recursive function - Handles calling the service multiple times if necessary.
const _getAllRecsRecursive = (layerURL, featuresSoFar, sWhere, relID) => {
    // Have to use queryTask instead of queryFeatures as the related tables can't be turned into feature layers.
    if (relID < 0) {
        let aQuery = new Query();
        aQuery.start = featuresSoFar.length;
        aQuery.num = 2000;
        aQuery.where = sWhere;
        aQuery.outFields = ["*"];
                
        return query.executeQueryJSON(layerURL, aQuery).then(function (results) {
            // If "exceededTransferLimit" is true, then make another request (call this same function) with a new "start" position. 
            // If not, we're at the end and we should just concatenate the results and return what we have.
            if (
                results.exceededTransferLimit &&
                results.exceededTransferLimit === true
            ) {
                return _getAllRecsRecursive(
                    layerURL,
                    [...featuresSoFar, ...results.features],
                    sWhere,
                    -1
                );
            } else {
                return Promise.resolve([...featuresSoFar, ...results.features]);
            }
        });
     }
     else {
        let aRelQuery = new Query(); //RelationshipQuery();
        aRelQuery.start = featuresSoFar.length;
        aRelQuery.num = 2000;
        aRelQuery.where = sWhere;
        aRelQuery.outFields = ['*'];
        aRelQuery.relationshipId = relID;

        return query.executeRelationshipQuery(layerURL, aRelQuery).then(function (results) {
            // If "exceededTransferLimit" is true, then make another request (call this same function) with a new "start" position. 
            // If not, we're at the end and we should just concatenate the results and return what we have.
            let curFeatures = [];
            for (let aRecIdx of Object.keys(results)) {
                let aRelSet = results[aRecIdx];
                for (let aFeature of aRelSet.features) {
                    curFeatures.push(aFeature);
                }
            }
            if (results.exceededTransferLimit &&
                results.exceededTransferLimit === true) {
                return _getAllRecsRecursive(
                    layerURL,
                    [...featuresSoFar, ...curFeatures],
                    sWhere,
                    relID
                );
            } else {
                return Promise.resolve([...featuresSoFar, ...curFeatures]);
            }
        });
    }
};

function getAllRecs(layerURL, sWhere, relId) {
    return _getAllRecsRecursive(layerURL, [], sWhere, relId);
}

// Export to excel
let btnExport = document.getElementById("btnExport");
let dateNow = Date.now();
btnExport.onclick = function () {
    // Create a new Excel Workbook
    var workBook = {
    SheetNames: ["Testing"],
    Sheets: {},
    Props: {
        Title: "Title for Testing",
        Subject: "Testing",
        Author: "Your name, leave blank, or ?",
        CreatedDate: dateNow
    }

    // Pull data until there is no more, storing data until needed later
    let outResults = [];
    let outWhere = "1=1"; // modify this to support subset of total recs
    getAllRecs(restURL, outWhere, -1).then((results) => {
        for (let aRec of results) { 
            let curAtts = aRec.attributes;
            // Make any necessary attribute modifications here...
                // I have routines here to fix dates from Epoch dates, remove edit fields, etc.
            // Push results to results list
            outResults.push(curAtts);
            //console.log(curStopAtts)
            stopObjectIds.push(curStopAtts[fldBusStopOID]); //"OBJECTID"]);
        }
    })

    // Set up Excel workbook
    var wopts = {
        bookType: 'ods',
        bookSST: false,
        type: 'binary'
    };

    // Pull in workbook sheet.
    try {
        workBook.Sheets["Testing"] = XLSX.utils.json_to_sheet(outResults, { dateNF: 'mm/dd/yyyy;@', cellDates: true });

        // Export spreadsheet using unique name built from current date/time.
        let today = new Date(Date.now());
        let cDate = (today.getMonth() + 1) + '_' + today.getDate() + '_' + today.getFullYear();
        let cTime = today.getHours() + "_" + today.getMinutes() + "_" + today.getSeconds();
        let sToday = cDate + '_' + cTime;
                                   
        let exportFileName = 'BSR_' + sToday + '.xls'; //'BSR_' + sToday + '.xls'; //'BSR_' + sToday + '.ods'; //'BSR_' + sToday + '.xlsx';

        let wbOut = XLSX.write(workBook, wopts);
        saveAs(new Blob([changeData(wbOut)], { type: "" }), exportFileName);
    } catch (error) {
        console.log(error);
    }
};


  Hopefully that all makes sense. I took out a lot that I have in there as I'm cleaning data as it gets pulled, and I use it to export out related data as well (hence the 'relID' variable in the getAllRecs and _getAllRecsRecursive procedures). Also, it pushes out an ODS file (Open Document Spreadsheet). I was able to push it out to a CSV file, but then Excel always complained that it wasn't in the correct format, so I changed it to ODS, which is obviously not the 'correct' format, but when it is opened in Excel (no problems there) the user understands why the error pops up that it's not in the correct format.  

Would love to know if there's an easier way to do this, but this has been operational for over a year now.