Iqy Import to Excel row limit

  • 6 September 2018
  • 7 replies
  • 399 views

Userlevel 6
Badge
  • Good Looker
  • 410 replies

Hi,



I am wondering if I can use an iqy (https://docs.looker.com/sharing-and-publishing/publishing-looks-with-public-urls#microsoft_excel_spreadsheets) file but download ALL rows, currently it respects the limit saved in the Look.


The iqy file has some extra parameters such as:



Selection=EntirePage


Formatting=None


PreFormattedTextToColumns=True


ConsecutiveDelimitersAsOne=True


SingleBlockTextImport=False


DisableDateRecognition=False


DisableRedirections=False



and wondered if there was another I could add or edit one of the above which would give me all rows.



Thanks!


7 replies

Userlevel 6
Badge

No not yet, we might make some kind of service that handes data extracts that can be called from excel (as well as other places) but thinking how to handle auth (if any).

Userlevel 4

Just use the API via VBA?

Have you ever made this work?

We had a looker consultant working on it and the outcome was, that API and VBA does not work?

Userlevel 6
Badge

What options are possible now? We have a lot of users pulling data into Excel and this 5k limit is causing problems.


Just use the API via VBA?

Userlevel 7
Badge +1


This is no longer possible, and the approach detailed above won’t work anymore. Be aware!

Userlevel 2

Hi Ian,



The query created for a look by the Looker UI will have the 5000 row limit imposed on all Looker UI queries (or lower row limit, if specified). There is no URL param to override the row limit on the look, especially not on a public URL since that could be used as a denial of service attack vector by malicious parties outside your organization.



Looks can be constructed to use queries with row limits higher than the UI cap of 5000 rows. Via the API (pseudocode):



look = sdk.look(look_id)

query = sdk.query(look.query_id)

query.limit = 15000

new_query = sdk.create_query(query)

look = sdk.update_look(look_id, { query_id = new_query.id })



The public URL (unchanged) for that look will now return 15,000 rows of data instead of the UI 5,000 row limit.



As you raise the row limits on your look queries, keep in mind that very high row limits run the risk of impacting your db and your Looker instance’s responsiveness. Be particularly cautious of distributing public URLs on looks with high limits to a large number of people.



Hope this helps,


-Danny

Userlevel 6
Badge

This is for non technical users so it will take some involvement to help guide the user through this but thanks for your response, if we get some extra time to work on this we will setup the solution.


Thanks!

Userlevel 2

Hi Ian,



The limit to the number of rows in Public link is the same as a look, which is 5000. We recommend using API to get all result. You can find an example achieving it from Google Sheet.







If you want stick to Excel, you can use a similar approach with VBA. We currently don’t have an example in VBA.

Reply