Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Wdata not connecting query while query is below the cell limit

Con risposta
0

Commenti

4 commenti

  • Commento ufficiale
    Tony Scalese

    Hi Brian,

    This issue sounds like it would be best looked into by our support team.  Can you open a ticket and we'll make sure the team is looking into this for you?

    Thanks,

    Tony

  • Jeff Hickey

    Hi Brian. Spreadsheets are limited to a maximum of two million cells across all Sheets. This means that if other Sheets within your Spreadsheet contain data, or historically used data, that will count toward the 2 million limit and reduce the maximum amount of cells available for use in other Sheets. For example, if Sheets A and B used a total of 500,001 cells of data (or historically used data), then all other Sheets within that Spreadsheet have 499,999 cells available. In your specific example, the other Sheet(s) may be using 955,001 or more cells which means your query result would exceed the remaining available cell limit.

    You can check the cell usage by opening your Spreadsheet and going to File > Properties > Spreadsheet > Limits. This will show you how many cells of the 2,000,000 are in use. If you're finding that you're hitting the 2 million limit, you may want to consider a redesign of your architecture to take advantage of Wdata Tables which are designed to store and manage larger sets of data rather than storing the data in Spreadsheets.

    Below are actions to free up cells in a Spreadsheet:

    • Delete unused Sheets from within the Spreadsheet
    • Delete rows and columns in Sheets (not just the cell values) that are no longer needed or historically used
    • Reduce/limit query output size to fit within remaining cell allocation
    • Use a new Spreadsheet that has its own allocation of two million cells
    0
  • Brian Tokar

    Thanks Jeff, just did a check and am about 300,000 shy of two million so not sure if that's the issue or if something else is at play here.

    0
  • Jeff Hickey

    If your Query says it produces 45,000 results when ran in Wdata, this means that it produces 45,000 rows plus 1 for headers. You need to multiply that by the number of columns in the Query. So 45,001 x 9 = 405,009 which is greater than 300,000 and would fail to import the Query results into the Spreadsheet.

    If you do find that you're within the limit, I'd recommend reaching out to Support for additional troubleshooting help as Tony suggested.

    0

Accedi per aggiungere un commento.