Excel often save .csv files as tab delimited format. What happened

csvmicrosoft excel

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.

Best Answer

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.)

Related Question