To fix some common problems that can occur when reading or writing a Microsoft Excel file, you can first try the suggestions below:
- Ensure the Microsoft Excel file is not open in Excel.
- Ensure the Microsoft Excel file is not password protected. This module does not currently support password-protected files.
- If the file is too large, it may fail to load into memory. You can try several possible workarounds:
- Divide the file into separate files (if the spreadsheet contains many worksheets that do not reference each other).
- Export the file into CSV and then read it using the CSV reader.
Other Common Issues
- Max Features to Read
- Increasing memory use in writer
- Permissions do not work as expected
- Selecting Update Feature Types doesn’t refresh schema
Max Features to Read
The schema is determined either through Feature Type parameters or by scanning x features of the feature type, where x is defined by the Max Features to Read parameter in the Workbench Navigator. The schema is useful for defining the schema which may be used by writers when generating a workspace, but it is ignored on read.
Therefore, features beyond the Max Features to Read might return values that do NOT comply with the schema. (If this is an issue, you can re-add a reader with a larger value for Max Features to Read or manually edit the attribute types in the Attributes tab of the parameters dialog).
Permissions do not work as expected
If permissions do not seem to be working correctly – for example, you cannot delete columns in generated Excel worksheets despite setting the Delete Unlocked Columns permission – your cells may still be locked (since this is the default for all cells in new Excel worksheets).
For more information, please refer to Locked Versus Unlocked.
Selecting Update Feature Types doesn’t refresh schema
If Update Feature Types does not refresh the schema, open the Parameters dialog while in the Update dialog. This will trigger a new read of schema.
Increasing memory use in writer
When repeatedly writing to the same output file(s) with either of the Drop or Truncate Existing Sheets/Named Ranges options, process memory usage may steadily increase over time until the point where the FME translation crashes. The crash is accompanied by a log message similar to:
|
Failed to free sufficient memory to reach the process usage limit. To improve stability and performance please increase the memory available to FME. Available memory can be increased by adding physical RAM, increasing swap space, or closing other applications. Excel Writer: Failed to open the dataset 'myfile.xlsx'. Error message was 'ok' XLSXW writer: A fatal error has occurred. Check the logfile above for details |
This is a symptom of the Excel shared string table growing larger to the point where it no longer fits into the memory available to FME. This is a known issue in the library we use to read Excel files, and we are attempting to find a solution that does not impact writer performance. In the meantime, there are two potential solutions to the issue:
- Recommended Solution – The recommended solution is to use the Template File writer parameter, which will make a copy of the template file to write data to each time the workspace is run. (Using the Template File parameter requires setting the Overwrite Existing File parameter to Yes.) Each new set of values will not be orphaned in the string table and the process memory usage will be minimized; output files will not grow indefinitely after repeated translations.
- Alternate Solution – Alternatively, opening the file with Excel and then resaving the file will remove unused table entries and return it to a minimum size. However, this operation may not be automatable and, depending on the size of the file, may take considerable time.