How to keep text format in CSV file

Keep a copy of your data files in a plain text format, with comma or tab delimiters. I generally use comma-delimited (CSV) files.

So a spreadsheet like this:

Would become a plain text file like this:

id,sex,glucose,insulin,triglyc
101,Male,134.1,0.60,273.4
102,Female,120.0,1.18,243.6
103,Male,124.8,1.23,297.6
104,Male,83.1,1.16,142.4
105,Male,105.2,0.73,215.7

It’s not pretty to look at, but you can open the file in Excel and it will look pretty again. More importantly, this sort of non-proprietary file format does not and never will require any sort of special software.

(And most of the time, the first thing I do with an Excel file is convert it into a set of CSV files, one per worksheet, because they’re easier to handle in code.)

If any of the cells in your data include commas, Excel will put double-quotes around the contents of each cell when it’s saved in CSV format. That requires slightly more finesse to deal with, but it’s generally not a concern.

To save an Excel file as a comma-delimited file:

• From the menu bar, File → Save As
• Next to “Format:”, click the drop-down menu and select “Comma Separated Values (CSV)”
• Click “Save”
• Excel will say something like, “This workbook contains features that will not work…”. Ignore that and click “Continue”.
• Quit Excel. It will ask you, “Do you want to save the changes you made?” Click “Don’t Save”, because you just saved them. (Excel really doesn’t want you to use a format other than its own.)

Note that there’s also an option to save as “Tab Delimited Text”. Many people prefer that. (I don’t like tab delimiters so much, myself, as multiple adjacent tab characters are hard to discern by eye.)

Also note that, if your Excel file did contain critical features that will not work when saved as a plain text file, that’s a problem. For your primary data file, keep things simple.

Personally, I prefer to use a vertical bar (|) as a delimiter between fields, because the data itself will seldom contain a vertical bar. But you can’t get that from Excel.

Next up: Other things to avoid.

(Previous: Use data validation to avoid data entry mistakes.)

Hello! Is there a way for me to retain the "Text" formatting on some columns in a CSV file? I am handling data with a lot of leading zeros on them and am looking a way to maintain them whenever I open the CSV file again without importing the text file or opening a new workbook, copy pasting the text file, and comma delimiting whilst selecting the said columns as "Text".

I would want to be able to save it as a CSV file and when I open the CSV file again the columns that I pre-selected as "Text" will still be in the same format which would lead to the leading zeros being retained in my data set. Thank you for your help!

Edit: I am using the most recent excel version for office 365.

When I save areport with data as a CSV file, all of the leading zeros are cut off any numbers when I open the file in Excel. How can I keep the zeros?

This is actually an Excel issue. The program automatically truncates all leading zeros from numbers in CSV files. The key is to change at least the columns where the leading zeros occur (i.e. ORG or Fund numbers) to "text." There are two ways to accomplish this.

First and simplest way: If the report needs to be .csv, save the entire report as Excel. Then open the Excel document, convert the whole sheet to text format, and save as .csv.

Second and more 'surgical' way: Start with the report open:

1. Click on Export icon > Export Data to CSV and the Save as CSV Options box opens.
2. Set the Text qualifier as either double or single quotes.
3. Set the Column delimiter as a comma.
4. Click OK.
5. Click Save -- DO NOT OPEN THE CSV FILE DIRECTLY WITH EXCEL!
6. Open a new worksheet in Excel (see below for Excel screenshots.)
7. Select the Data tab.
8. Click on the From text icon to display the Import Text File dialog box.
9. Select the CSV file to import and click Import. The Text Import Wizard appears.
10. Select the Delimited radio button -- Text Import Wizard, Step 1 determines that your data is delimited.
11. Select the row number where you want the import to start.
12. Check the My data has headers. box if true. Review the preview of the file at the bottom of the box.
13. Click Next
14. Check "Comma" as a delimiter (column dividers will appear in preview)-- Step 2 lets you set delimiters
15. Click Next
16. Highlight the column(s) with leading zerosin Step 3
17. Mark those columns format as "text" by clicking the radio button in the Column Data Format section.  NOTE: You will need to do this for each column where the data contains leading zeros.
18. Click Finish, Import Data dialog box opens.
19. Click OK.
20. The leading zeros will still be there in the new worksheet with the imported data. The columns with real numbers will still be able to be used with calculations.

Successive screen shots of Excel process:

Why does CSV file not save formatting?

Can you save formatting in CSV?

How do I stop CSV from changing date format?

How do you save text in a CSV file?