Follow

CSV vs. XLS: How to Open a CSV in Excel

Anytime you "open" a CSV using Excel, Excel tries to be helpful and format fields according to the data in them. That means values like zip codes, sizes, and UPCs are formatted as numbers. When Excel formats these fields as numbers, it strips away leading zeroes and converts especially long numbers into scientific notation (e.g. "3.09E11"). That means zip codes can lose their leading 0's, sizes "0" and "00" can get deleted entirely, and UPCs (and even some style IDs) can get changed into scientific notation. To avoid that, you want to "import" the data into Excel as text.

 

Open a CSV using Excel (Mac):

1. When you download the CSV, SAVE the file directly to your desktop. (Do not "open" or double-click.)

2. Open a blank spreadsheet in Excel.

3. Choose File > Import...

4. File type: CSV File 

 

5. Click Import.

6. Choose the CSV you have saved.

7. Click Get Data.

8. Data type: Delimited

 

9. Click Next

10. Delimiters: Comma (no other options should be checked)

11. Click Next

12. In the Data preview window, select ALL columns. (Click the first column. Scroll all the way to the right, then Shift + Click on the last column.)

13. Column data format: Text

If you have properly selected all the columns, you will see a label above each column in the preview that says "Text".