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.
Please, for the love of god make this happen. I've actually written an app to open CSV files simply because Excel messes them up so bad. I have ID card numbers that are 16+ digits long that get converted to scientific notation. All my leading zeros get stripped out removing vital info. Dates get messed up or sometimes fields that look like dates become dates. I don't know who thought this was a good idea. I mean at least have an option to toggle if you want Excel to completely wreck your file.
Rob King commented
So glad to see this is receiving attention, having recently seen what Excel did to corrupt some extracted data from one system as it was being processed and passed through to another. In this case it was mobile telephone numbers where leading zeroes were lost and those stored in the +447 format were converted to scientific notation; nothing seemed immune to some degree of interference.
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.