Iqy Import to Excel row limit

  • 6 September 2018
  • 7 replies
  • 389 views

Userlevel 6
Badge
  • Good Looker
  • 405 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 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.

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 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 7
Badge +1

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

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 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

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).

Reply