How to Import a Remote XML file in Airtable
In this guide, we'll learn the difference between a local XML file on your computer and a remote XML file from an API. We'll also look at how to import both local and remote XML files into Airtable. Finally, you'll learn how to set up automatic imports, so that our Airtable table is kept up to date with any changes in the remote XML file.
What is XML?
Many online services & APIs make data available in XML format. XML stands for eXtensible Markup Language. It's a markup language and file format for storing and transporting data. It can be read by computers because it follows an agreed-upon set of formatting rules. It can also (just about!) be read by humans. Here's an example:
<note>
<to>Harry</to>
<from>Potter</from>
<heading>Anouncement</heading>
<body>You're a wizard Harry!</body>
</note>It doesn't actually do anything, unlike a programming language like, e.g., JavaScript. It just contains the data. XML files will always have the filename extension. `.xml`.
What's the Difference Between Local and Remote XML Files?
It's important to understand if you want to import a local or a remote XML file to Airtable, since the way you import them is different.
Local XML files are simply files stored on your computer. You can import them to Airtable by using the XML import extension from Airtable. For more details on how to do this, check out this Airtable support article.
Remote XML files are different to local files in that they are stored on a different computer to your own. They are hosted on a remote server in the same way a website is. Like a website, they are accessible using a URL. This URL will end in '.xml'. E.g. here is an example XML file from the U.K. government: http://ratings.food.gov.uk/OpenDataFiles/FHRS111en-GB.xml. Government services as well as older APIs often use the XML file format.
Now, let's learn how to import remote XML files within Airtable!
Install the Data Fetcher Extension
Add Data Fetcher to your base from the Airtable marketplace. This is a free extension that lets us import remote CSV, XML and JSON data within Airtable.
After you have added Data Fetcher to your base, you will need to create a free Data Fetcher account. Enter a password and click 'Sign up for free' or click 'Continue with Google'. Signing up for an account keeps your details secure. It's separate from your Airtable account.
How to Import a Remote XML File in Airtable
Once you've signed up for a Data Fetcher account, you will land on the home screen. Click 'Create your first request' to go to the create request screen, where you will enter the details of our remote XML file.
On the create request screen in Data Fetcher, select 'Custom' for the Application.
Enter a Name for your request, e.g. 'Import XML' This will help you remember what this request does when you back to Data Fetcher in the future.
Next, enter the URL of your remote XML file. As mentioned above, this should end in `.xml`.
For this example, we will set the URL to http://ratings.food.gov.uk/OpenDataFiles/FHRS111en-GB.xml. This is an open data XML file for food ratings from the UK government.
Select the Output Table & View to the table and view in your Airtable base that you want to import the remote XML file into.
Click 'Save & Run'.
Next, you need to configure how the available fields in the remote XML file should map to fields in the output table. The Response field mapping modal will open, where you can do this.
For each field name in the XML file, you can either import it or filter it. For an imported field, you can either map it to an existing field or create a new field in your output table. You can also set the field type for each new field. Once you are happy with the response field mapping, click 'Save & Run'.
Data Fetcher will create the fields in the output table for any fields you mapped to 'New fields'. It will then run the request again and the remote XML file will be imported to Airtable! You should now see the XML file's data in your output table and view.
If your XML file has a unique field that identifies each row of data (e.g. an 'Id' field), you can use this to ensure changes in the remote file are always synced to Airtable when you run the request. To do this, click 'Advanced settings' and then scroll down to 'Update Based on Field'. Select the unique field as the 'Update Based on Field'.
How to Import Remote XML File to Airtable Automatically
Every time you click 'Run' on the create request screen, the XML data will be imported to Airtable. To avoid having to do this, you can use Data Fetcher's scheduled requests feature. This means the data will be automatically imported on the schedule that you select, e.g. once per hour.
This is a paid feature, so you need to upgrade. In Data Fetcher, scroll to Schedule and click 'Upgrade'.
A new tab will open where you can select a paid Data Fetcher plan and enter your payment details to upgrade.
Return to the Data Fetcher extension and click 'I've done this'.
Under Schedule click '+ Authorize'.
A new window will open where you can authorize which particular Airtable bases you would like Data Fetcher to access.
If you select 'All current and future bases in all current and future workspaces' you'll avoid issues with unauthorized bases in the future.
Click 'Grant access'.
In Data Fetcher, you'll see that Schedule this request has now switched to on.
Select how often you want the request to run, e.g. 'Every hour'. Click 'Save'. The request will now run on the schedule and automatically import the data from the remote XML file to your Airtable base.
Does Airtable Have an 'IMPORTXML' Equivalent?
Google Sheets has a handy IMPORTXML function for importing remote file types such as XML, HTML, CSV, TSV and RSS and Atom XML feeds. Airtable does not currently have an equivalent function/ formula.
The closest equivalent to IMPORTXML using just Airtable's native features is the Scripting block. You can use this to fetch and transform the data, then write it to your Airtable base. However, this gets very fiddly, which is why we built Data Fetcher. Data Fetcher automatically handles fetching and transforming the XML file, and updating your base.
Follow the steps above for an equivalent to IMPORTXML in Airtable using Data Fetcher.
Related Posts
How to Import a Website's XML Sitemap into Airtable
Jun 16, 2022
•Rosie Threlfall
•XMLHow to Import a Remote CSV in Airtable
Feb 9, 2022
•Andy Cloke
•CSVRemote File