Manage CSV Files

CSV (Comma-Separated Values) files are widely used for storing tabular data because they are lightweight and universally compatible. However, opening and editing CSV files in applications like Excel and Apple Numbers can lead to unintended changes, especially when dealing with data that includes leading zeroes (e.g., Student IDs). Here, we outline steps and best practices to ensure that leading zeroes remain intact throughout the process.

Why Do Leading Zeroes Get Erased?

By default, spreadsheet applications treat numbers as numerical values, which means that any leading zeroes are dropped (e.g., "00123" becomes "123"). This is problematic if the leading zeroes are essential for student IDs

General Tips for Managing CSV Files

Understand the Nature of CSV: CSV files are plain text, and any formatting you apply in spreadsheet software will not be saved unless properly managed. Each application interprets the data according to its default settings, which often assumes numbers are purely numerical.

Work with Backups: Always save a copy of your original CSV file before opening it in Excel or Numbers. This ensures you can revert to the original data if formatting gets altered.

Manage CSVs in Excel

CSV files opened in Excel will often auto-formats the data, leading to the loss of padded zeroes. You can use Excel’s Import Wizard to specify how data should be treated To avoid losing padded zeroes.

1. Open Excel but do not open the CSV directly

If you directly open a CSV file you will be prompted with the following pop up message:


Click Don't Convert to disable Excel from removing leading zeros

2. Go to "Data" > From Text/CSV (in newer versions of Excel)

3. Select the CSV file you want to open

4. In the Text Import Wizard:

4a. Choose Delimited as the data type

4b. Ensure the delimiter is set to Comma

4c. Select the columns containing padded zeroes

4d. Select Text instead of General under Column Data Format

5. Complete the wizard, and Excel will display your data without altering leading zeroes

Save CSV Files in Excel

1. Go to File > Save As and choose CSV (Comma delimited) (*.csv)

If you make changes and save them, Excel will warn you about losing features that are incompatible with CSV. Ensure you double check your data after saving.

Manage CSVs in Apple Numbers

CSV files opened in Apple Numbers will often auto-formats the data, leading to the loss of padded zeroes. 

1. Open the CSV file in Numbers

2. Select the columns that contain data with leading zeroes

3. In the Format Panel on the right, under Cell, change the format from Automatic to Text

4. Go to File > Export To > CSV and save the changes

Ensure to open the file in a text editor like Notepad or a code editor to verify the padded zeroes are still present