Add a setting/option where all CSV/delimited files are opened as "Text"
Add a setting called
"Open text-delimited files (CSV, etc) as text only, with no type inference."
When this setting is active, then every column's format will be "Text" when a CSV or other such file is opened, so that the initial view is exactly the text represented in the CSV.
Thanks for this suggestion! We’re evaluating our future investments and your votes help us prioritize. Please keep voting if you like this idea.
When opening a csv in Excel I never want to see anything changed unless i edit it after the fact. Its not just leading zeros that get removed but dates get silently replaced by integers. By default there should be no conversions unless overridden by global options which by default should be set 'safe' so un unexpected formatting takes place. Users need the confidence to open a csv file, edit a single cell then save it knowing nothing else has been changed in the background.
When double clicking *.csv-files from explorer Excel should just open the data/from-text wizard.
And that wizard should have a checkbox "Import all fields as text-fields" or a button which sets the datatype for all columns to text
Just install OpenOffice. It's a shame that we have to install a direct competitor to our software to get basic .csv functionality, but OpenOffice is honestly the best and most convenient solution until Excel has a practical solution for editing .csv files.
Importing the .csv file and using "Save As" every time you have a minor change to make to the file is not acceptable.
Yigal: use common sense. If the file is .csv delimited then each field should be treated as a column. Same applies to .tab. and yes this applies to file ppen.
Why was it broken?
The prior version worked fine.
Not it requires 7 steps
PLEASE go back to what worked
We managed to find the only adequate solution. Install the extension and use it for free. The site has a video with English subtitles.
The problem here is that not many people open CSV files in Excel and even though 100% of them hate how Excel automatically re-formats the data upon opening files, this feature will never get enough votes for MS to do anything about it. No matter how simple the fix is. I hate MS.
Microsoft have proved over and over again that THEY DON'T LISTEN.You can have the most reasonable request for changing something that doesn't work well and really requires a small effort on their side to fix it, but they WON'T DO IT and there will be no explanation why! That's their current culture. Too big to care.
Solution and explanation here. Third-party programmers have created an add-in to solve the problem.
This problem is the automatic use of auto-formatting. It is necessary to disable this disgrace. Data without a request and warning, you will not even know that before your eyes other data.
На самом деле проблема эта более глобальная и заключается в автоматическом применении автоформатирования. Нужна возможножность отключить это безобразие. Не знаю никого, кому эта функция была бы полезна так как она искажает данные без запроса и предупреждения, вы даже не узнаете что перед вашими глазами другие данные.
In general Excel should assume any numbers starting with 0 are text anyhow.
Auto-formatting of CSV's in Excel is extremely frustrating and provides no functionality. It's hard to understand why this was implemented in the first place as it would have been easier to just display all entries as text; the user should be given the option to do this by default.
Brad Hunter commented
An option to open .csv files as "TEXT" would be awesome. We have .csv file containing HEX data and Excel always interprets a hex value like "34e2" as an exponential value "3.40E2". It is a pain to go into Excel each time and tell it to open the file with all columns as text. We like to just open the file from Windows Explorer by double clicking on the file. So a setting in Options would solve the problem.
Tiberiu Chibici commented
When opening a CSV file, it is dangerous to execute formulas, as described in article . Usually CSV files are used to transfer data, not worksheets, so it wouldn't be a bad idea to interpret the cells as text.
My suggested fix would be to interpret CSV data as plain data without any formulas, unless the user explicitly wants to.
And this applies not only to the CSV, but to all file formats! Open your eyes already while you Libreoffice from bypassed.
Simply add the default formatting option. And then collect the statistics and see that everyone chooses "TEXT". Because this is the only format that does not damage the data!
Yes, you just need to always open CSV as text, without damaging the data! Constantly truncated zeros, some numbers turn into dates! The problem is that the developers did not work with such data, otherwise they would correct this misunderstanding.
Who will pay to receive this? Or what file and where to fix?
Daniel Smith commented
I have implemented a "general format" button in an addin to restore sanity to csv file I open. Scientific format is rarely a good choice.
Excel is horrible for data files, yet it doesn't have to be! When opening a CSV data file it converts numeric strings to Scientific notation which is actually useless because it loses precision and cannot even be converted back and truncates leading zeros on fields like zip codes! For the love of GOD why do you need to do this? how many people in America actually use Scientific Notation? Seriously. Why can't I open a CSV file without you converting my fields. I work in the mailing industry and have been fighting this nonsense for ten years and I have to tell my clients please do NOT USE Excel for data for this simple reason. You could easily solve this simply by NOT ASSUMING every numeric string is an Exponential NUMBER!!! And no, I shouldn't have to import a CSV and specific field by field. Just treat the Goddam fields as TEXT PLEASE!. It could be that SIMPLE!
Yes! I have to deal with this all the time, for years, in getting my externally generated CSV output to be Excel-friendly, all for no good reason. CSV is TEXT! It should by default be opened that way, as literally written. (With maybe commas and quotes needing escaping) I'd feel lucky just to have a preference option available, given that someone 20 years ago decided Excel should try be "smart" like Clippy, and reformat everything possible, and default is probably permanent stuck this way now.