如何从SODA API拉取数据至Excel并提取total_asmt字段

Based on your scenario—where you have an Excel table with house_number and street_name pairs, and need to fetch the total_asmt value from the Edmonton SODA API—here are two reliable methods to get this done:

Method 1: Using Excel Built-in Functions (Quick for Small Datasets) #

This method works well if you have a small number of rows and are using Excel 365/2021 (or older versions with a bit more string manipulation).

Assume your data is structured like this:

• Column A: house_number (e.g., 10008)
• Column B: street_name (e.g., 103 STREET NW)

1. Construct the API URL (in cell C2):
   Use ENCODEURL to properly format spaces and special characters in the street name:

   ="https://data.edmonton.ca/resource/3pdp-qp95.json?house_number="&A2&"&street_name="&ENCODEURL(B2)
   

   Drag this formula down to apply to all rows.

2. Fetch JSON data (in cell D2):
   Use WEBSERVICE to call the API and retrieve the JSON response:

   =WEBSERVICE(C2)
   

   Again, drag down for all rows.

3. Extract total_asmt value:

   • For Excel 365/2021 (cleaner with TEXTBEFORE/TEXTAFTER):

     =IFERROR(TEXTAFTER(TEXTBEFORE(D2,"""}"),"total_asmt"":"""),"No data found")
     

   • For older Excel versions (string manipulation):

     =IFERROR(MID(D2,FIND("total_asmt":"",D2)+13,FIND("""}",D2)-FIND("total_asmt":"",D2)-13),"No data found")
     

   The IFERROR wrapper handles cases where the API returns no matching data.

Method 2: Using Power Query (Robust for Large Datasets) #

Power Query is better for larger datasets because it parses JSON natively, handles errors gracefully, and can be refreshed easily.

1. Load your data into Power Query:
   Select your table, go to the Data tab, click From Table/Range.

2. Add a custom column for the API URL:

   • Go to the Add Column tab → Custom Column.
   • Use this formula to build the encoded URL:

     = "https://data.edmonton.ca/resource/3pdp-qp95.json?house_number=" & [house_number] & "&street_name=" & Uri.EscapeDataString([street_name])
     

   • Name the column API_URL and click OK.

3. Fetch and parse JSON data:

   • Add another custom column with this formula to retrieve and parse the JSON response:

     = let
         jsonResponse = Json.Document(Web.Contents([API_URL])),
         totalAsmt = if List.Count(jsonResponse) > 0 then jsonResponse{0}[total_asmt] else null
       in
         totalAsmt
     

   • Name this column total_asmt and click OK. This checks if the API returned any data, then extracts the value from the first (and only) object in the array.

4. Clean up and load back to Excel:

   • Remove the API_URL column if you don't need it.
   • Go to Home → Close & Load to bring the data back into your Excel workbook. You can refresh the data anytime by right-clicking the table and selecting Refresh.

Key Notes: #

• Rate Limits: Be mindful of the SODA API's rate limits. If you have hundreds of rows, consider adding a delay in Power Query or batch requests to avoid being blocked.
• Error Handling: Both methods include checks for missing data, but Power Query gives you more control over handling edge cases (like invalid addresses).

内容的提问来源于stack exchange，提问作者aajiwa