Importing External Data Into Excel on Mac // Excel Tutorial

In this short Excel tutorial I'm going to show you how to import external data into Excel. This guide is focused on Mac OS since the workflow in windows is a little different.

If you have any questions, please feel free to write a comment and ask directly. I'll do my best to help.

[clayrains]

Under the "Data" menu item, I go to "Get external data" item in the dropdown menu, but in the submenu, there IS NO "Run saved query" option, only "run web query, " with the two choices below it of "From database" and "Import text file." I tried choosing both of those options and selecting the .iqy file, but it does not work. It's really frustrating that I can't find a tutorial that works. I've tried several, and I finally figured out they're using Excel for Windows, and that it's not even possible in Mac. But I found this tutorial, and thought this would work. It doesn't. I don't know what on Earth the problem could be with this tutorial, because the person is using the EXACT SAME version of Excel I'm using on my Mac, and the menu items I'm seeing him choosing don't exist either.

[luzern]

2:24 -> For those who aren’t able to select the (grey) iqy-file. Try to save this file as ms-dos instead of mac os.

[TheExpertTutorials]

Nice vid, i've been trying to find a easy solution and here it is. if you are having the same problem that excel wont recognize it as a iqy just do this (This is an extract of someone else comments) :
""Find the file that you saved with the .igy extension. Right click it and select 'Get Info'. Navigate to the 'Name and Extension' tab and I suspect that you have to delete the '.txt' suffix. Once you do, the OS will ask you what sort of file type you want to save the file as--.txt or .iqy. Select '.iqy'.""

[WhatTheCiv]

no run saved query option, version 16.42

[chunyanji]

3:20 > for people cannot upload the ipy file. I found the answer from" Webbme - Hemsidor & Media"
Quoted : " click the file and click "show info". There my file name was .iqy.txt so I needed to remove .txt again and then it worked for me!"

[theziggy]

Worked a charm, very clear explanation! Thankyou!!

[philiphallstrom]

This is a little strange but after looking through the comments I actually solved the issues (sort of).

My first issue was that I can't get the option "Run Saved Query" when I'm going to Toolbar->Data>Get External Data->?

I do however get the options to either "Run Web Query", "From Database" or "Import Text File". If I then choose "Run Web Query" I can run the file I have prepared as shown in your video but by default, unfortunately, there is an issue that some other commenters also was getting namely that the file gets saved as "iqy.txt". If going back to the location where the file was saved (in my case by going to Finder->Applications and then search for "Queries") it was quickly changed and the file could be run. 

However, my second problem was that my test website wouldn't work as I only get a message saying that there was no data to import. After testing a few other websites it worked flawlessly so my conclusion is that some websites probably protect their information to prevent this action.

Felt like this explanation probably help to solve some of the issues others are struggling with despite a great tutorial!

[annemullender]

Wow, that's so useful! Great explanations!

[alpha]

Thank I have learnt a little bit more but it does not work for me. I copy the link, put it in a word document save as you said and then when I want to put it in excel the document is grey, I have no access but I have 2 queries which were on my computer as samples and they are also iqy ! I have tried again but it does not I have done exactly as you said (txt and before saving

[frankherder]

If its not working on mac, it is probably because your file name didn't change fully.

For example, even when you add .iqy, there may be .txt added to the end without you realizing it.

Go into terminal and modify the file name. So if its SampleFile.iqy.txt, you have to run this -

mv SampleFile.iqy.txt SampleFile.iqy

Hope that helps. Just worked for me

[ImrePetro]

It worked beautifully. I could select the text file by pretending to rename it and then I was able to import it into excel.
I wonder why did the MS guys make this process so complcated, as this is definitely not a user-friendly way to import a webpage. It is more like an easter egg. Whatever, thank you!

[stijnv]

THANKS A LOT MAN, BIG STRUGGLE BEFORE

[finefloor]

Hi Thanks for your video, It's really helpful. I am importing crypto data from the api, the cell I need displays a $ sign as well as the current updated total so my calcs don't work when copied over to my work sheet, Is there any way around this ?

[hjenei]

Great tutorial. Can this be used to pull data from another Excel workbook located on OneDrive? I`ve tried but getting alert "This Web query returned no data." Any advice? Thanks!

[fendiarikristianto]

Why do I choose data in the IQY format in Excel, data like in the video at minute 3:06? Cannot be selected.

[elenapiller]

the .iqy file is grayed and I cannot select it. The other difference is that in excel it says "Run Web Query" not the saved query option that you had. Would that be affecting this?

[troelsrosenberg]

it doesn't work for me as well, seems like we all have the same problem with the word 2019.
is there another way too save the file a iqy file?

[LebenWerden]

Hi, leider habe ich das gleiche Problem wie die meisten hier. Bei mir fehlt "run web query". Ich habe Excel Version 16.48, Microsoft 365-Abo.
Ist es wichtig wo man die .iqy-Datei speichert? Ich habe sie aufm Desktop gespeichert. ICh hoffe sehr Du kannst helfen. Danke.

[miland]

Hi, for me everything works, except that my file generates the link instead of a table. Trying to import information from CoinGecko API. First I thought I didn't save it as a Hyperlink but this wasnt the case. Any help is much appreciated!

[nehapinjala]

I dont see run saved query but i can see only run web query. how can I fix this?