Grants.gov has been undergoing a series of changes to improve the overall user experience. Some functionalities may be temporarily unavailable following our October release. Our team is working to re-establish these functionalities as quickly as possible.
If you’ve been relying on the “Export Detailed Data” functionality and found it temporarily unavailable, you can still get what you need into a spreadsheet software. In this blog post, we’ll walk you through the essential steps to extract and load an XML (Extensible Markup Language) extract of Grants.gov data into MS Excel, so that you can obtain the data you need in the meantime. This process may vary slightly depending on your specific version of Excel, but the general steps should remain consistent. Other data tools may have similar XML import features.
Step 1: Download the XML File
Grants.gov creates an XML file of all funding opportunities in the Grants.gov database daily. You can download a file of all opportunities in Grants.gov through the “XML Extract” link in the footer–bottom left–of the Grants.gov website.
Identify the date for the extract you wish to use in the right column of the table. Click the file name ending in “.zip” in the left column of the same row. A ZIP file folder (compressed file) with the XML file you need will download to your computer.
Step 2: Extract the XML File
After you’ve downloaded the ZIP file, you’ll need to extract the XML file. If you are using Excel for Windows, you can save it to a folder location of your choice:
- Locate the ZIP file in your downloads folder or the destination you chose.
- Right-click on the ZIP file.
- Select “Extract” or “Extract All.”
This will create a folder with the same name as the ZIP file, containing the XML file you need.
Step 3: Import XML Data into Excel
Now, it’s time to import the XML data into Microsoft Excel:
- Open Microsoft Excel.
- Click on “Data” in the top menu.
- Select “Get Data.”
- On a PC (shown below): Choose “From File” and then “From XML.” On Mac: In the first page of the “Chose Data” dialogue, chose the Import data from an XML file.
This will open a dialog box where you can navigate to the folder where you extracted the XML file. Select the XML file and click “Import.” This is a large file, so it may take some time before the next dialog box appears. For PC users (screenshot below) the dialogue box is titled Navigator. For Mac users: the dialog box is simply called “Choose Data.”
Step 4: Use Excel Choose Data/Navigator to choose the data you want (Forecast and/or Synopsis)
Users can choose to load all data or portions of it. Both Opportunity Forecasts and Synopsis data can be selected to load into the form together or as separate files. Select the query(ies) you want to access and click the “Transform Data” button. Note: If you click Load at this point, the imported data will be difficult to filter—and this file includes a decade of opportunities!
Step 5: Use Excel’s Power Query Editor to Load the data into Excel
After you select Transform data in the Navigator dialog box, the data will appear in Excel’s Power Query Editor. This step is necessary because Excel by default changes the data type, which deletes leading zeros that you’ll need. If you selected both the Forecast and Synopsis data in the previous step, you’ll need to repeat this for both of the queries. In the screenshot below from Excel for Windows, notice navigation to the other synopsis/forecast data (query) on the left panel.
- In the Query Settings menu (on the right in screenshot above), delete the default Applied Step, Changed Type. In this version of Excel delete by clicking on the X to the left.
- Click “Close and Load”.
After the data is loaded, you can choose which Queries to access through a pop-out menu on the right side of the spreadsheet. This may take some time to load, as the queries contain a large amount of data. Double click the query you wish to access and the populated excel sheet will open.
These will appear as separate sheets in your Excel workbook.
Step 6 (optional) – Convert the date fields so you can filter out unwanted rows from the data
There are various ways to do this. Here is a relatively easy one:
- Select a column that contain dates you would like to filter with. In the Synopsis query, the date columns are labeled PostDate, CloseDate, and/or LastUpdatedDate.
- In the Data menu, select Text to Columns. The Convert Text to Column Wizard will appear.
- Click Next and Next. In the third step, under Column Data Format, select the Date option.
Data in this column will now appear as a date in Excel.
Step 7 – Save your query and start filtering!
Most field names are very similar to the Export Detailed Data feature, but they do not contain spaces. If you are interested in forecasted opportunities, look for EstimatedSynopsisCloseDate, rather than Estimated Application Due Date.
The Online Help Guide article, “XML Extract” provides a description of each field and possible values. However, fields with more than one value are simply expressed in the Excel file as “[Table].” These fields include Funding Instrument Type, Category of Funding Activity, CFDA Numbers, and Eligible Applicants.
While the “Export Detailed Data” functionality may be temporarily unavailable on Grants.gov, you can still access and work with the data you need by following these steps to extract XML data and load it into Microsoft Excel. This workaround ensures that you can continue your grant application and management tasks.
To learn more about our upcoming enhancements and releases, read our latest blog, “Upcoming Releases: Improving Your Grants.gov Experience.”
Grants.gov Community Blog 