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 the feedback!
Can you comment on whether you expect this to basically import all the lines as text to column A, or if you have some other expectation?
Also, I’m assuming people refer here to the file/open path, and not to the Data/From-Text or New-Query/From-Text path.
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.
My use case for this is opening large CSV files which include product UPCs, some with leading zeros. As it is, Excel changes the UPC to scientific notation and drops the leading zero. I would like a setting which will still take advantage of the delimited file to separate the file into coumns but will set each column to text format to avoid the problem described above.
100% agree. This issue is a major problem for scientific applications.
Kevin Bouwman commented
Agreed, every column text, do not try to infer dates and numbers.
George Krasle commented
I was working with data-files containing long time-date stamps, and lots of data was destroyed. If a string in the origin file can't be interpreted as a number, it should be left as a string, not "corrected" to just 15 digits.
Workaround was to use notepad++ macro to append a tick to every string.
Harlan Grove commented
For example, genetic data often looks like dates, and opening CSVs converts such data into dates. Since opening CSVs doesn't launch Text to Columns, there's no way to prevent Excel from making this conversion other than by changing the CSV file's extention to .TXT and opening is as a text file rather than as a CSV file.
IOW, ONLY separate CSV records into columns, but otherwise leave as text with no conversion of numbers into numeric values, no conversion of anything looking like dates into dates, etc.