> Well, despite plentiful advice on StackOverflow and elsewhere, I’ve found only one (undocumented) thing that works with any sort of reliability: For any cell that begins with one of the formula triggering characters =, -, +, or @, you should directly prefix it with a tab character.
>Unfortunately that’s not the end of the story. The character might not show up, but it is still there. A quick string length check with =LEN(D4) will confirm that.
The documented way is prefixing with a ' character. It doesn't have the length issue either.
As to the root issue, I can't think of any perfect way to transfer a series of values between applications that apply different types to those values and applications that don't. At some point, something is going to have to guess.
> The documented way is prefixing with a ' character. It doesn't have the length issue either.
It is suggested in comments, but the author answered
> Yes, this prevents formula expansion... once. Unfortunately Excel's own CSV exporter doesn't write the ', so if the user saves the ‘safe’ file and then loads it again all the problems are back.
That's it. My pet peeve issue with Excel/CSV is USA zip codes. Excel will happily eat leading zeros. There is a specific number format to correct that. If you export that file to CSV with the format set the CSV file will have 5 digits. If you reopen that CSV file in Excel it gobbles up the zeros all over again.
As someone mentioned elsewhere this is an issue with long numbers. Excel converts them to scientific notation. Reformat and export, all good. Reopen said file, back to scientific notation.
Really anything that relies on an escape character (') or a specific format gets lost on export to CSV. It exports correctly but there is simply no way to document these formats in a CSV file and have it be compatible with anything but Excel.
Same with phone numbers. In most parts of the world, local numbers (not fully-qualified with country code), are written/dialed with a leading zero.
Excel eats these and/or uses scientific notation!
If you use Data -> From Text, and on Step 3 select all the columns and make them "Text", that will prevent Excel from mangling any of the data (stripping leading zeros, evaluating strings starting with = as formula etc.)
Excel somehow has one of the worst CSV parsers I have ever used. You would think this is something that should be bread and butter for a spreadsheet app, but it does a surprisingly terrible job.
Came here to say the same. Also tested it to confirm and the single quote mark inside the double quotes does indeed force interpretation as a string instead of a formula. In both Excel and Google Sheets.
Interestingly, in Excel removing the quotes entirely also causes a formula to be interpreted as a formula and text (even with spaces) as text and numbers as numbers.
In my testing, quotes are only needed when a field contains a comma to prevent it being interpreted as a delimiter.
"transfer a series of values between applications that apply different types to those values and applications that don't"
If we thought about it as an API mechanism, we would parse the strings and apply rules to sanitise or reject it.
Here is a principle for thinking about data. Distinguish internal data structures (persistence, search) from interchange structures (APIs). Codebase A should not be able to directly access the structures of Codebase B. To communicate, they must use explicit APIs.
At the moment, this principle is not mainstream. The CSV loader is not sure if it is loading an interchange format or persistence format. Another, that happens regularly: (1) developer builds a database as a storage mechanism. (2) developer decides to have other separate codebases query into that database. Is the database an application-data-structure (interal) or an API (external)? It is acting as both.
The applications that are communicating either have to agree on the types in advance or they have to use an interchange format that makes it explicit. If your applications don't both know the types in advance then you shouldn't be using CSV.
I think the common model people had of CSV was that it was an imperfect way to transfer values, but safeish from code execution, XSS or "all your Google account data gets exfiltrated" type effects.
Users associate application behaviour with file formats.
Analogy: people think PDF files are safe, but aren't aware of the constant stream of RCE vulnerabilities that is Acrobat Reader & how widely it's used, which invalidates their model of behaviour associated with PDF files.
i don't know why spreadsheet applications don't standardise on a file extension that they won't screw with. call it csf or something. treat it like pure CSV except don't interpret =/@ or any of the other weirdness. basically just interpret all fields as plain strings as default [even if they look like numbers]. this way everything is backwards compatible old 'weird CSV' files still work and those that care about their users can use .csf and the files won't endanger their users.
That would make safety an "opt in" measure, it should be "opt out" instead. Make the CSV format stop interpreting formulas unless you specifically ask it to. Most people don't put formulas in their CSV files anyway.
>Unfortunately that’s not the end of the story. The character might not show up, but it is still there. A quick string length check with =LEN(D4) will confirm that.
The documented way is prefixing with a ' character. It doesn't have the length issue either.
As to the root issue, I can't think of any perfect way to transfer a series of values between applications that apply different types to those values and applications that don't. At some point, something is going to have to guess.