Export data from iqy to xlsx

Help
1

Hello all,

My project involve steps to export data from a sharepoint to excel file.

The problem that I faced is when I exported the data, it was in iqy format. I need to change this to xlsx format. I see 2 options to get the info in the formal that I need:

  1. Open sharepoint → Export data into iqy → copy from iqy & paste to new excel (xlsx format) where I need to further do some data massaging (filtering, adding new column, deleting some row).
  2. Open new blank excel file → click ‘Data’ → click ‘Get Data’ → Click ‘From online services’ → Click ‘From sharepoint online lists’ → input URL & wait till data loaded into excel → save the file as xlsx

For option #1: I dont know how to perform copy all data in the sheet to a new black excel file. Since the data was in iqy i dont think i can use the ‘excel application scope’
For option #2: I cant find a way to open a new black excel file

I appreciate your input. Thank you.

2

I had the same issue and found another post that mentioned that you can just save the iqy file somewhere and then point to the iqy file in the Excel Application Scope. If you then use Read Range to pull the table you can do whatever you want with the data. When I test it automatically refreshes the query every time I run it.image

2 Likes