[Pivot Table] limitation of the pivot table size

[Pivot Table] limitation of the pivot table size

Posted by: anh.pham on 24 April 2024, 11:10 am EST

• 

  anh.pham

  • Post Options:
  • Link
    Copy

  Posted 24 April 2024, 11:10 am EST

  Hi support team,

  We are encountering an issue with the Pivot Table size. Specifically, when we set up an Excel file on our customer device and then upload it to our application with Spread Designer support to open, we receive an error message stating “Incorrect file format.” Performing a similar action with a smaller data set (around 50,000 rows and 2,000 columns) works successfully. We are working with a source data set of 100,000 rows and 25 columns. After setting up the pivot table, the resulting data set has 14,180 rows and approximately 2,000 columns. It is at this point that we encounter the aforementioned error message.

  Please kindly advise us on a workaround for this issue.

  Kindly refer to the attachment to verify our sample Excel file. The sample file too heavy so I can not upload it

  Best Regards,
  
  Marco

• 

  anh.pham

  • Post Options:
  • Link
    Copy

  Posted 24 April 2024, 11:11 am EST

  https://drive.google.com/file/d/1Run-YNq-xkO6TmjIip5gVtK7AonqHSKF/view
  
  Here is my sample file link.

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 25 April 2024, 8:33 am EST - Updated 25 April 2024, 8:38 am EST

  Hi Marco,

  Based on my understanding, when attempting to import an Excel file containing approximately 100k rows and 25 columns, it fails to export and displays an “Incorrect file format” error.

  I’ve successfully replicated this issue on my end. Upon inspecting the Excel file, it seems that the file size exceeds the loading capacity of the web platform. Even attempting to load the file into Google Sheets results in an error message stating “Too large to import,” as depicted in the provided image “googleSheet.png.”

  

  923×465 17.9 KB

  However, it’s noteworthy that the error message should not indicate “Incorrect file format.” As I’m uncertain about this, I’ve escalated the matter to the relevant team for further investigation. The internal track ID for this issue is “SJS-23996”. I’ll keep you updated once I receive any further information.

  Regards,
  
  Priyam

• 

  anh.pham

  • Post Options:
  • Link
    Copy

  Posted 25 April 2024, 10:31 pm EST

  Hi Priyam,
  
  Based on your information, it appears that the file size exceeds the standard. Could you ideally tell us what the ideal file size is? Namely, since we typically use the pivot table function, we should provide information to our customers to reduce their concerns when working with large files.

  Best Regards,

  Marco

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 29 April 2024, 9:03 am EST

  Hi,

  We are still investigating the issue at our end. We will let you know about our findings as soon as possible.

  Regards,

  Priyam

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 30 April 2024, 2:44 am EST - Updated 30 April 2024, 2:52 am EST

  Hi,

  The concerned team informed us that the Excel file initially existed as a zip file. After unzipping it, SpreadJS proceeds to read the XML file, parsing it into an object, and then loading it into SpreadJS.

  However, in the provided Excel file, upon unzipping, the sheet2.xml file is almost 700MB. When attempting to load that blob and extract text from it using the native JavaScript API, no data is returned. You can verify this by referring to the attached GIF “Demo.gif” and the demo available at this link: https://drive.google.com/file/d/1fTJFrRj7xCX53nZWnR0e2h8yKUiZnfto/view

  

  In SpreadJS, if text cannot be extracted from the blob, the initial assumption is typically that the file format is incorrect or that the file is damaged. Due to SpreadJS’s loading strategy, there isn’t a file size restriction, hence, no “File is too big” exception is raised.

  Ultimately, the success of file loading depends on the application’s current environment and any limitations imposed by the web technology platform being used.

  Regards,
  
  Priyam

• 

  anh.pham

  • Post Options:
  • Link
    Copy

  Posted 30 April 2024, 4:55 am EST

  Hi Priyam,
  
  We have already set up the other scenario on the sample file I sent above already. On the source data sheet, we’ve separated the data into two sources for the pivot table: one with 73,000 rows and 20 columns, and the other containing the remaining data. As a result, we can successfully load the sample file onto Spread Demo. Even when we increase the data size to over 100MB, it continues to function properly.
  
  Could you review whether this issue comes from a limitation of the pivot table function?
  
  Best Regards,
  
  Marco

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 1 May 2024, 7:03 am EST

  Hi,

  SpreadJS does not have size limitations and the same with the pivot table. However, as mentioned previously, this can vary depending on your web platform and application environment. If the native JavaScript API can extract text from the file, it will load and function smoothly without any issues. Please refer to the GIF and sample above for a clearer understanding.

  Regards,

  Priyam

• 

  anh.pham

  • Post Options:
  • Link
    Copy

  Posted 2 May 2024, 5:34 am EST

  Hi Priyam,
  
  We agree with what you mentioned. However, we can’t explain this to our customer as we’ve discussed. Could you provide us with some advice about the standard file size or update the warning message through the SpreadJS designer to a clear message?

  Best Regards,
  
  Marco

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 3 May 2024, 8:32 am EST

  Hi,

  I’ve inquired with the developer regarding your concerns. I’ll keep you informed once I receive any updates. The internal tracking ID is “SJS-23996”.

  Regards,
  
  Priyam

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 7 June 2024, 8:31 am EST

  Hi,

  The developer informed us that the real situation can be very complex and the file size is not necessarily determined by the data. They suggested checking the file size before loading it into SpreadJS:

  • For runtime, you can get the file blob and restrict the file size.
  • For the designer, you can monitor

    GC.Spread.Sheets.Designer.Events.FileLoading

    , retrieve the file blob from the event args, and then restrict the file size and display a dialog if necessary.

  For example, do not accept files larger than 20MB.

  Regards,
  
  Priyam