Fields starting with equals (`=`) do not open correctly in Excel

[kiprobinson]

If a field value starts with equals character (=), the csv does not open correctly in Excel. This may technically be an Excel problem, but it would be nice if there was some kind of "excelSafe" option to csv-stringify.

Examples:

Field       CSV Encoding     What Excel Shows      Excel-safe CSV Encoding (causes Excel to show same as input)
=test       =test            #NAME?                "=""=test"""
="test"     "=""test"""      test                  "=""=""""test"""""""
="test      "=""test"        ="test                "=""=""""test"""

Workaround:

cast: {
    string: s => s.charAt(0) === '=' ? `="${s.replace(/"/g,'""')}"` : s,
}

[kiprobinson]

Update: I found a related issue that references this page which suggests this is a problem if a field starts with =, +, -, or @. That page also says simply putting a ' in front of the cell value works, but in my testing on latest version of Excel that's not true.

So my updated workaround would be:

cast: {
    string: s => s.match(/^[=+\-@]/) ? `="${s.replace(/"/g,'""')}"` : s,
}

[wdavidw]

In your exemple above, you could delegate the escape work to the library:

cast: {
    string: s => {value: s, quoted: s.match(/^[=+\-@]/) },
}

[wdavidw]

This being said, we could introduce a new option, safe_spreadsheet or anything else if the usecase is shared among several user. Any suggestion for the name of the option ?

[kiprobinson]

Just quoting is not enough. "=test" still gives #NAME? when opened in excel. from my testing, it has to be ="=test".

I think Excel first takes the value and parses, i.e. if it's quoted it unquotes, otherwise it takes the original value. Then it says "ok does this start with an equals sign? if so it's a formula let me evaluate it".

I'm not sure what happens if you try to open in Google spreadsheets--it may just be an Excel problem in which case I'd suggest calling it "excelSafe".

[wdavidw]

I don't have excell on my host but with libreoffice, "=test" is broken as well and ="=test" is a correct solution.

[tomyam1]

PapaParse has this option as escapeFormulae Added on https://github.com/mholt/PapaParse/pull/796

(Also related to https://github.com/adaltas/node-csv-stringify/issues/69)

[wdavidw]

Is someone interested in proposing a pull request ?