Hello, I have been trying to find some documentation on striping formulas from an excel file on upload. I would like to just import the values if possible.
We have multiple large excel documents our lab uses that would befit from being switched to a real database. I want to setup a importer using the File Upload component that will receive the file, then write the values to a SQL table.
I know I can probably just copy the values to a new excel sheet, but I was curious if there was already support for this.
I sniffed around the manual but I didn't notice anything with a few searches. Sorry If I missed anything like this.
Generally, Excel caches the values of cells that are computed, and those cached values can be deliberately targeted by the Apache POI libraries. However, saving the cached value with the spreadsheet is not required, so you may need to execute the formula.
Unfortunately, Apache POI is a memory hog, and any solution to this will result in a memory spike. Consider using a single dedicated thread to do such imports, so you can't get multiple users/tabs running at once.
You can go directly from the imported sheet (in memory) to a "Prep" query, so that one-time spike is it.
Thanks, I will look into all the information you provided. Once these files have been imported we would be discontinuing the excel documents, so I don't have to worry about multiple users using them all the time. Its more of a one and done kind of thing, per file.
Once all the data has been moved over we will be switching to a input screen to document everything moving forward.
I'd do this with local resources in the designer script console (or Vision, if available), iterating through a local folder of spreadsheet files. Not using an upload component. You can then point Apache POI at the source file for best memory footprint, and no gateway memory usage at all.