Download Article
Download Article
If you need to import data from the web into your Microsoft Excel spreadsheet, you can use the import feature of Power Query on both Windows and macOS. While the steps are straightforward in Windows, Mac users technically don't have the option to import web data. Fortunately, we've found a simple workaround for Mac using Apple’s native TextEdit app! This wikiHow will show you import data from a remote URL into Excel in Windows and macOS.
How to Import Data from a Website into Excel
On Windows, go to Data > From Web, enter the URL, and click Connect to select and import web data. On a Mac, create a text file in TextEdit with a .iqy suffix that contains the URL, then use "Run Web Query" to import the data.
Steps
-
Click an empty cell in the spreadsheet. Choose the cell where you'd like the pasted data to begin.
-
Click the Data tab. This tab is located between Formulas and Review at the top of the app window.Advertisement
-
Click From Web on the Data ribbon. You will see this in the upper-left corner of Excel on the "Get & Transform Data" panel. [1] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source This option is in the upper-left corner of your Data ribbon. This opens the "From Web" window.
- If you don't see this option, click the Get Data drop-down menu at the top-left, select From Other Sources , and then select From Web .
-
Enter the website URL and click OK . The address you enter or paste into the typing area should be the URL that contains the data you want to import.
-
Click Connect to access the site anonymously. If the data is on a public website, this will display the tables in Table View. [2] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source If the site requires a login:
- If you usually sign in to this page by entering a username and password into the site's own login/sign-in fields, select Basic , enter your login details, and click Connect .
- If you need to log in through a work or school account, select Windows to use a Windows network login, or select Organizational account and then Sign In to log in through your organization's server.
- If you have an API key, select Web API , enter the key, and click Connect .
-
Select a table. The list of tables appears in the left panel of the Navigator window. When you select a table, the data will appear in the panel on the right.
- If you're not sure which table you need, click the Web View tab at the top of the right panel to display the website, and then click the table to select it.
- To import more than one table, check the box next to "Select multiple items" near the top-left.
-
Click the Load button. This will import the selected table data into your Excel spreadsheet.
-
Open TextEdit and create a new document. Because Excel for macOS, even the one that comes with Office 365 , doesn't have an option to import data from the web, you'll need to do a little workaround that involves creating a query in a text editor first. [3] X Research source We got the best results using Mac’s built-in TextEdit app.
- To find TextEdit, click the Spotlight search button at the top of the screen and type in textedit . Click the first result that comes up.
-
Click New Document . When you open TextEdit, a window will open where you can browse files to open. Click the New Document button at the bottom of the window, or close the window and select New from the File menu.
-
Type or paste the URL you want to import from. It's important that the URL be in plain text. Type it in directly or copy and paste it from the navigation bar at the top of your browser.
- The copied URL should be the only text in the file you're creating.
-
Click Format and select Make Plain Text . Open the Format menu in the bar at the top of the screen and choose the Make Plain Text option. This will allow you to save the file in the correct format.
-
Click the File menu and choose Save… . Now that you’ve set the file to Plain Text, it will save to the correct file format (in this case, the Unicode (UTF-8) plain text format). Click Save in the File menu.
-
Give the file a name that ends with .iqy . Even though you're saving the file as a Unicode plain text file, you'll need to make sure the file has the .iqy file extension. For example, my_query.iqy . Enter your desired name with the .iqy ending in the Save As: text box at the top of the Save pop-up window.
-
Uncheck the box to add a txt extension. You’ll see a check box in the Save window that says If no extension is provided, use “.txt”. If that box is checked, uncheck it before you save. Your file will now be saved with a .iqy extension.
-
Select a location and click Save . It doesn’t really matter where you save your file. Just pick a location you’ll remember, like Desktop or your Documents folder.
-
Open your spreadsheet in Excel. Now that you have a query to work with, you can import it into Excel. Create a new spreadsheet or open a pre-existing one where you’d like to import your data.
-
Click the Data menu in the menu bar. Note that there are two Data menus while Excel is open—one in your Mac's menu bar at the top of the screen, and another in the menu bar at the top of the Excel spreadsheet. These two menus are different. You will need to click the Data menu in the menu bar at the top of your Mac's screen, not the one at the top of the spreadsheet.
-
Select Get Data (Power Query) . Additional options will expand.
-
Click Run Web Query… . This will open a Finder window.
-
Select your IQY file and click Get Data . Navigate to the location where you saved your text file and select it, then click Get Data . You'll be prompted to choose where to import the data.
-
Choose where to import the data and click OK . You can import the data into the current sheet at a particular cell location, or create a new sheet for the data. Once you click OK , Excel will go to the URL you added to your IQY file and import the web data into your sheet.
Expert Q&A
Video
Tips
References
About This Article
You can easily import a data table from the web into your Excel file using Excel's "From Web" import tool. To do this, open your Excel file, and then click the cell where you'd like the imported data to appear. On the Data tab, click the From Web button on the left side of the toolbar to open the "From Web" window. Type or enter the URL that contains your data and click OK . If the data is on a public website, click Connect to open it. If it requires a login, choose the option in the left panel that describes how you typically sign in, enter your login details, and click Connect . If you know the name of the table you want to import, click it in the left panel. If you'd rather select it from the website, click the Web View tab above the right panel to load the site, and then click the desired table. You can import more than one table at once by checking the "Select multiple items" box and clicking additional tables. When you're finished, click Load to import the selected data to your spreadsheet.
