Text formatting is lost when saving a file as a CSV

When saving a file as a CSV, Excel will remove any formatting from the cells and save only the values. This is because CSV files are plain text files and do not support formatting.

Hi David. I am an Excel user like you.

Since the .CSV file contains no formatting when it is re-opened in Excel it is opened each time as a new file and Excel evaluates the content of the columns to apply the most obvious formatting. Most of the columns will probably say they are formatted as General. This is not a problem unless the data is being imported incorrectly. The .CSV file will still be only text when the file is saved again and can be opened as a text file using Notebook or a similar text application.

If the data is being imported incorrectly when opening the file, you can go through the import process to identify how the data should be imported. Here is the process to follow.

Note: When I did this experimenting with a column of dates and changed them to text after I imported them the column formatting still said General, however, the data was in fact saved as text. You can verify this by trying to change the format of one of the cells. If it is saved as a number, you can change it, for example, from date to currency and back. If it is saved as text, it will not change.

If you are having difficulty formatting the data/Columns from a .csv file before bringing it into your Excel spreadsheet, you can use Get Data to do this. Open a new Excel file. On the Data Ribbon go to Get Data>From File>From Text/CSV.

.

Find and select the file you are looking for and click Import at the bottom of the window.

In the window that comes up click on Transform Data at the bottom of the window.

.

This will open the Power Query Editor. Check your columns and for any that need the format to be corrected. Click on the column Header and in the Home Ribbon>Transform section click the dropdown for Data Type and select the correct data type for your column.

.

When all columns are correctly formatted, click on Close and Load at the left end of the Home Ribbon.

.

This will load the file into a sheet in your workbook. The Query will be kept as part of the file at this point. If desired, you can delete the query by going to the Query Ribbon and clicking Delete in the Edit section.

.

I am trying to do this for a file used with Ebay upload. I do all your steps. That part works. However when I go to save it as a CSV file it fails to upload. So when I go to open it is has changed the item number back to a number like so

Hi David. I am an Excel user like you.

Since the .CSV file contains no formatting when it is re-opened in Excel it is opened each time as a new file and Excel evaluates the content of the columns to apply the most obvious formatting. Most of the columns will probably say they are formatted as General. This is not a problem unless the data is being imported incorrectly. The .CSV file will still be only text when the file is saved again and can be opened as a text file using Notebook or a similar text application.

If the data is being imported incorrectly when opening the file, you can go through the import process to identify how the data should be imported. Here is the process to follow.

Note: When I did this experimenting with a column of dates and changed them to text after I imported them the column formatting still said General, however, the data was in fact saved as text. You can verify this by trying to change the format of one of the cells. If it is saved as a number, you can change it, for example, from date to currency and back. If it is saved as text, it will not change.

If you are having difficulty formatting the data/Columns from a .csv file before bringing it into your Excel spreadsheet, you can use Get Data to do this. Open a new Excel file. On the Data Ribbon go to Get Data>From File>From Text/CSV.

.

Find and select the file you are looking for and click Import at the bottom of the window.

In the window that comes up click on Transform Data at the bottom of the window.

.

This will open the Power Query Editor. Check your columns and for any that need the format to be corrected. Click on the column Header and in the Home Ribbon>Transform section click the dropdown for Data Type and select the correct data type for your column.

.

When all columns are correctly formatted, click on Close and Load at the left end of the Home Ribbon.

.

This will load the file into a sheet in your workbook. The Query will be kept as part of the file at this point. If desired, you can delete the query by going to the Query Ribbon and clicking Delete in the Edit section.

.

You should use xlsx to store formatting on Excel file.

Thank you for your reply. The formatting we desire is TEXT format. We understand CSV files are standard text files and are not trying to have any other formatting, just text.