Share via

CSV formatting gets lost when trying to edit and save a CSV in Microsoft Excel

Anonymous
2021-02-25T06:32:52+00:00

Using the following Excel version:

The steps to reproduce the issue are as follows:

  1. Open a csv file in the above version of Excel.
  2. Edit the file (I had added a hard enter in a cell followed by some text).
  3. Save it using Ctrl+S.
  4. Closing the file shows a prompt that the file is not saved yet and trying to save it from the prompt shows the default save type as (.txt) instead of (.csv).
  5. If you close without saving from the prompt, opening it again will show that the CSV formatting is completely gone.

I tried using another Office version:

But for this version, the issue could not be reproduced.

Could you please help with this issue?

The resultant CSV file with the formatting lost after saving can be accessed using the following link:

Editted_csv.csv

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2021-02-27T08:03:10+00:00

    Excel is not a CSV file editor. If you open or save a CSV file in Excel you have to respect the system settings of your machine.

    If you insert a linebreak you break the data structure. If you would use a simple text editor like Notepad you would not claim that there's a linebreak, right?

    You can make a suggestion here, but consider Excel works that way for decades.

    https://excel.uservoice.com/

    There are many tools out there to edit or import/export CSV files in/from Excel, e.g.

    https://www.dropbox.com/s/m7qdh5gs3wqb99r/CSV.xla?dl=1

    But consider: That might solve the issue on your local machine, but there is no guarantee that it works on other machines or if you upload such a file anywhere.

    Again:

    CSV files are plain text files and therefore can not contain any information about format nor data structure.

    Andreas.

    10 people found this answer helpful.
    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2021-02-25T08:09:34+00:00

    CSV files are plain text files and therefore can not contain any information about format nor data structure.

    It is recommended not to open CSV files, you should import them, save the Excel file and maybe share both files. Have a look into this article:
    Power Query - How to import a CSV file that does not match your locale - Microsoft Community

    Andreas.

    5 people found this answer helpful.
    0 comments
  3. Anonymous
    2021-02-26T07:24:10+00:00

    By formatting, I meant the commas that separate the different data in a single row. I have attached a gif here to explain the issue to you.

    3 people found this answer helpful.
    0 comments