Why are apostrophes appearing when generating a CSV?
There is an option in Manage to handle Excel and CSV output that could be interpreted as a formula. It can be updated by visiting (Manage > Settings > Security - Escape Excel Formulas).
The Escape Excel Formulas option is a security flag that will apply for all projects using Excel sheets.
If this option is disabled, the security will still need to be handled for any Excel sheet that may form part of the project's calculations in Produce. One way to possibly handle the security for this is by having only designers upload Excel spreadsheets through Design as a template.
Once Excel interprets a cell as active content, it will attempt to execute functions contained in it.
"-2+3+cmd|' /C calc'!A0" will open the calculator app on a user's PC when the formula is evaluated in Excel.
To avoid unintended formula evaluation, the Escape Excel Formulas option is provided. When this option is enabled, an apostrophe is inserted at the front of any value beginning with:
This ensures that Excel treats the value as a string rather than evaluating it as a formula.
If formula evaluation is desired (for example to allow negative numbers, beginning with
-, to render as intended) the solution is to disable Escape Excel Formulas - ensuring that any values which a user could manipulate to insert a formula into the spreadsheet are prepended with an apostrophe prior to being inserted.
Additional information about Comma-separated values.
Updated 11 months ago