15

I never know how that may happen.

I really really hate it.

That's because my program counts on csv files being csv.

And I never know, until now, how can my csv file becomes tab delimited format

Did I click save as?

Did I save as usual?

It's a csv files. I always want it to be csv.

Sometimes I open the file and found out that the comma is gone. I got to do

Data -> text to column

To fix that up. And then save again. If I save again, it's csv allright. But how the hell it become tab delimited in the first place? I never ever instruct excel to save it in tab delimited. How can that happens at all? The extension is still .csv.

Additional info

  1. I rarely save the file. I usually get out of excel and some prompt may have show up. I may have save it without knowing.
  2. If I try to do file save as, excel will default to "txt" even though the file is clearly .csv files
  3. However, if I just save in a hurry, it won't save to .csv files. It will save to .csv.txt files. So to have my .csv files to contain tab delimited value do not seem to make sense at all.

enter image description here

The program that uses the CSV is my own custom made program. I now arrange that it can read both tab and comma. However, the program is very error sensitive when it comes to its input. That mistake have costed me hundreds of dollars already.

3
  • What's the program created those file? What's the one last opened them? Does it happens to all of your csv-s? Do they remain correct after you've fixed them? Dec 19, 2016 at 10:47
  • which version of office was it? its 2k18 and the problem still persists
    – Dev_Man
    Feb 19, 2018 at 4:49
  • I feel your pain. Excel does this on a semi-regular basis to me and, whatever the reason, as far as I can see, it can go and do one. I had an instance today where it did it to one file, which I then fixed, only for it to do the same thing to a different file around the same time as I fixed the first. Totally fuming. Like you, it breaks our software when CSV isn't CSV. Excel should honour the file extension.
    – Bart Read
    Apr 4, 2018 at 18:45

4 Answers 4

16

Just to expand on Chris76786777's answer on this possible Excel bug, I have also noticed if you open a CSV file that is encoded with UTF-8 with a BOM header on it (UTF-8-BOM), add some columns, and simply save it, it will save it with tabs instead of commas as the delimiter, but you will not see the (corrupted) results until you close the file and open it again in Excel.

If you open a plain UTF-8 CSV file without a BOM then the same edit above works just fine and you can reopen it in Excel normally.

One way to work around this is to open the CSV file in a robust text editor like Notepad++ or Vim, and convert the file encoding from UTF-8 BOM to just UTF-8 before fiddling with it in Excel. (I realize the BOM might be needed sometimes for Excel to understand different languages properly, at least according to this.) If you can remove the BOM, hopefully your results will then be more predictable (at least until Microsoft finds that this behavior is a bit odd.)

1
  • Thank you! Opening the CSV file in Notepad++ and changing the encoding from UTF-8-BOM to standard UTF-8 fixed this problem for me.
    – Nick
    Jul 18, 2018 at 21:12
9

When you double-click CSV file and it opens in Excel what actually happens is that Excel treats it as a text file, however it's being converted to columns "on the fly". It's still text file, though.

In order to open CSV as CSV in excel and then save it as CSV you need to import it.

Unfortunately the default action of importing CSV from double-click is gone with Office 2003.

For you it's either to get into the habit of importing your CSVs to Excel always (has it's advantages if you have large numbers - 12 digits or more) or switch to other tools - OpenOffice for example.

4

I believe this is a bug in Excel. I've noticed it happens when I save an XLSX as CSV with the UTF-8 CSV option. If I save with the "regular" version of CSV, this conversion from CSV to tab delimited doesn't happen. I can save, close, and reopen by double-clicking and the file remains CSV.

I ran into this issue over the past few days and this is what I've noticed.

1

What worked for me is similar to what was suggested by Russ :

  • Edit the CSV in a text editor and replace ; with ,

Also set my default CSV program to a text editor, not Excel.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .