Forum Discussion

MirkoBotello's avatar
MirkoBotello
Regular Visitor
1 year ago
Solved

Import USDA PSD DATA BASE

Hi community! I have a question

 

I want to import from the FAS-USDA website, a database related to Production, Supply and Distribution of certain agricultural products. I think i can do that via Python Script but i am not being able to find the proper script to connect my archive to that data base. The idea is not having to download those heavy archives every time i want to update the information.

 

I do not know if i have to use APIs. I have two options. Connect directly to the archives (in this case i want two, oilseeds and grains) or get the data by usung APIs (i already have an API key).

 

If someone can help me with the python code or by telling me how can i do that it would be really helpful.

 

Meanwhilr, i will continue investigating.

 

Thanks!

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    Hi MirkoBotello,

    Thank you for reaching out to the Microsoft fabric community forum.

    After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:


    Thank you for reaching out. I understand that you're trying to retrieve Production, Supply, and Distribution (PSD) data from the FAS-USDA API without manually downloading the large datasets each time. Let's review some potential issues and solutions to help you obtain the data successfully.

    Possible Reasons for an Empty Folder:

    Missing Required Parameters - The API may need specific commodity codes (e.g., Oilseeds and Grains), a market year, or a report type to return results.
    API Pagination – If the API returns data in pages, you may need to loop through multiple pages to get the full dataset.
    Invalid API Key or Request Format – Ensure your API key is correct and properly included in the request headers.
    Incorrect Endpoint Usage – The GetAllData endpoint might not return all records unless proper filters are applied.

    If you are still getting empty response, print "response.text" to check for the cause of the error.

    I hope this could resolve your issue, if you need any further assistance, feel free to reach out. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

    Thank you for using Microsoft Community Forum.

4 Replies

  • MirkoBotello's avatar
    MirkoBotello
    Regular Visitor
    1 year ago

    I used this code from chat gpt 

     

    import requests
    import pandas as pd

    # ⚠️ Replace 'YOUR_API_KEY' with your actual key
    API_KEY = 'YOUR_API_KEY'

    # USDA PSD API URL to retrieve ALL records
    url = 'https://apps.fas.usda.gov/PSDOnlineDataServices/api/CommodityData/GetAllData'

    # Headers with the API Key
    headers = {
    'Accept': 'application/json',
    'API_KEY': API_KEY
    }

    # Perform the GET request
    response = requests.get(url, headers=headers)

    # Check if the request was successful
    if response.status_code == 200:
    data = response.json() # Convert response to JSON
    df = pd.DataFrame(data) # Create a DataFrame with the retrieved data

    # Display the first few rows of the DataFrame
    print(df.head())

    # Save the data to a CSV file (optional)
    df.to_csv('usda_psd_data.csv', index=False)
    print("Data saved as 'usda_psd_data.csv'")

    else:
    print(f"Request failed: {response.status_code}, Message: {response.text}")

     

     

    And i got this in POWER BI. An empty fowlder

     

     

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    1 year ago

    Hi MirkoBotello,

    Thank you for reaching out to the Microsoft fabric community forum.

    After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:


    Thank you for reaching out. I understand that you're trying to retrieve Production, Supply, and Distribution (PSD) data from the FAS-USDA API without manually downloading the large datasets each time. Let's review some potential issues and solutions to help you obtain the data successfully.

    Possible Reasons for an Empty Folder:

    Missing Required Parameters - The API may need specific commodity codes (e.g., Oilseeds and Grains), a market year, or a report type to return results.
    API Pagination – If the API returns data in pages, you may need to loop through multiple pages to get the full dataset.
    Invalid API Key or Request Format – Ensure your API key is correct and properly included in the request headers.
    Incorrect Endpoint Usage – The GetAllData endpoint might not return all records unless proper filters are applied.

    If you are still getting empty response, print "response.text" to check for the cause of the error.

    I hope this could resolve your issue, if you need any further assistance, feel free to reach out. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

    Thank you for using Microsoft Community Forum.

    • v-kpoloju-msft's avatar
      v-kpoloju-msft
      Community Support
      1 year ago

      Hi MirkoBotello,

      May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

       

      Thank you.