6 ms·

If you put an apostrophe in front of the number it will store it as text but will appear as a number. Just to be clear - you can then save the xls/xlsx to csv

If you put an apostrophe in front of the number it will store it as text but will appear as a number. Just to be clear - you can then save the xls/xlsx to csv and the csv won't contain the leading apostrophe, but will show the whole number.

yen223 11y ago
If you put the apostrophe in front of a number, and save it as csv, that works. But if you reopen that exact same file, Excel will still misinterpret the number. The issue here was that there's no way to let Excel know that this value in this CSV file is not a number. The only way around it that I know of is to stick with xlsx, which has its own pain points.
- scriptman 11y ago
  Yes, it isn't ideal. What you can do is put 2 apostrophes in front of the number in xls/xlsx and save it to csv. The conversion will drop one apostrophe and when you open the csv it will still have one apostrophe in front of it followed by the whole number. Then you can use a formula like =RIGHT(A1,LEN(A1)-1) to remove the leading apostrophe. The number generated by the formula appears as a proper number again and is usable in calculations.
- ZenoArrow 11y ago
  Would it still be an issue with TSV? That's my preferred flat file data format, find it less error prone compared with CSV.
  - scriptman 11y ago
    Yes, it's still an issue. I prefer tab separated too, but it still doesn't help you.
- protomyth 11y ago
  "But if you reopen that exact same file, Excel will still misinterpret the number." No, you can set the import to treat it as a text field. It is really easy and this should not be a problem. The import can define field by field what it should be treated as (most often used with dates).
  - yen223 11y ago
    The problem here is that, like the folks mentioned in the article, I wasn't the one opening the file on Excel. I'm generating a CSV file from a webapp to be sent to clients for processing. They would double-click the file, and see those mangled fields. The real issue here is that Excel doesn't respect quotes on a CSV file for some inane reason. That is a bug on Microsoft's end.
- geographomics 11y ago
  The way around it is, rather than opening the CSV file directly, to create a new blank workbook and import the CSV file using Data --> Get External Data --> From Text. This opens the Text Import Wizard and from there one can define the datatype for each column - set it to Text (instead of the default, General) and it won't be interpreted as a number. Another way is to rename the .csv file to be .txt instead, and load it in normally. This triggers the Text Import Wizard upon opening, and the rest is as above. Also to do this programmatically, one can use the VBA function Workbook.OpenText, and specify the data types via the FieldInfo parameter.

yen223 11y ago

If you put the apostrophe in front of a number, and save it as csv, that works. But if you reopen that exact same file, Excel will still misinterpret the number. The issue here was that there's no way to let Excel know that this value in this CSV file is not a number. The only way around it that I know of is to stick with xlsx, which has its own pain points.

scriptman 11y ago
Yes, it isn't ideal. What you can do is put 2 apostrophes in front of the number in xls/xlsx and save it to csv. The conversion will drop one apostrophe and when you open the csv it will still have one apostrophe in front of it followed by the whole number. Then you can use a formula like =RIGHT(A1,LEN(A1)-1) to remove the leading apostrophe. The number generated by the formula appears as a proper number again and is usable in calculations.
ZenoArrow 11y ago
Would it still be an issue with TSV? That's my preferred flat file data format, find it less error prone compared with CSV.
- scriptman 11y ago
  Yes, it's still an issue. I prefer tab separated too, but it still doesn't help you.
protomyth 11y ago
"But if you reopen that exact same file, Excel will still misinterpret the number." No, you can set the import to treat it as a text field. It is really easy and this should not be a problem. The import can define field by field what it should be treated as (most often used with dates).
- yen223 11y ago
  The problem here is that, like the folks mentioned in the article, I wasn't the one opening the file on Excel. I'm generating a CSV file from a webapp to be sent to clients for processing. They would double-click the file, and see those mangled fields. The real issue here is that Excel doesn't respect quotes on a CSV file for some inane reason. That is a bug on Microsoft's end.
geographomics 11y ago
The way around it is, rather than opening the CSV file directly, to create a new blank workbook and import the CSV file using Data --> Get External Data --> From Text. This opens the Text Import Wizard and from there one can define the datatype for each column - set it to Text (instead of the default, General) and it won't be interpreted as a number. Another way is to rename the .csv file to be .txt instead, and load it in normally. This triggers the Text Import Wizard upon opening, and the rest is as above. Also to do this programmatically, one can use the VBA function Workbook.OpenText, and specify the data types via the FieldInfo parameter.