Iqy Import to Excel row limit


(Ian) #1

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!


(Xin Bao) #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.


(Ian) #3

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!


(Danny Thorpe) #4

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