Excel: Retaining formatting when importing TXT or CSV files

When opening a text file (txt), comma separated file (csv), or other file formats in Excel, Excel will automatically format data types which leads to data changes not expected or wanted. Some examples are Excel removing leading zeros off a number or converting long number like campus ID to long number format. 

Two possible solutions

NOTE: These steps need to be implemented every time the TXT, CSV file is reopened in a spreadsheet program. 

Import File into Excel - Preferred

1. Launch Microsoft Excel and open a blank workbook
2. On the Data Menu at the top of the screen, click From Text/CSV.
3. Navigate to the correct file, click on it, and choose Import.
4. Change Data Type Detection to “Do not detect data types”.
5. Verify data in preview window, Click Load.

If you need to save the file back as a txt, csv or other format, click save and change file format before saving.

Use a true text editor such as Windows Notepad or Apple TextEdit.app or notepad++ to view the file and formatting.

Change cell formatting

1. Once you open the CSV file, right-click the column letter and select Format Cells.
2. Then choose Custom, and set the format to '00000000.' The number of 0's should indicate the number of characters desired in the field, including the 0's. If the field value is 012345, then the custom format should be '000000' to indicate 6 digits with the 0. 
3. This will add the leading '0' to all cells from which it is missing. And when you save it as .csv, it will export the leading '0' in the output file. 

Use a true text editor such as Windows Notepad or Apple TextEdit.app or notepad++ to view the the file and formatting

Need help?

If you have questions, please contact the ITS Help Desk at 608.342.1400 or helpdesk@uwplatt.edu.  You may also visit the Help Desk on the first floor of the Karrmann Library.