Download ArticleDownload ArticleThis article was co-authored by How.com.vn staff writer, Megaera Lorenz, PhD. Megaera Lorenz is an Egyptologist and Writer with over 20 years of experience in public education. In 2017, she graduated with her PhD in Egyptology from The University of Chicago, where she served for several years as a content advisor and program facilitator for the Oriental Institute Museum’s Public Education office. She has also developed and taught Egyptology courses at The University of Chicago and Loyola University Chicago.
This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources.
This article has been viewed 146,559 times.
Learn more...
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 How.com.vn 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] 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] 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] 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.
- To find TextEdit, click the
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.
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
