Excel can sometimes format a CSV file in a way that causes data issues or unwanted adjustments. This article shows you how to access data in a CSV file within Excel and keep your data intact.
About Excel formatting
When opening a CSV file directly in Excel, Excel defaults to formatting fields according to how it interprets the data.
As a common scenario, Excel often converts these values to numbers: zip codes, sizes, and UPCs. When Excel formats these fields as numbers, it strips away leading zeroes and converts long numbers into scientific notation (for example, 3.09E11). With this adjustment, 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 these formatting changes, import the CSV data into Excel as text.
Importing CSV data into Excel (Mac)
To start, when you download a CSV, save the file to your desktop. Don't open or double-click the file.
Navigate to Excel and open a blank spreadsheet. Select File > Import.
Choose CSV file and select Import.
Select the saved CSV file.
Make the following selections:
- Select Get Data.
- Choose Delimited.
- Select Next.
- Delimiters: Comma (no other options should be checked).
- Select Next.
- In the Data Preview window, select all columns. Tip: Select the first column, scroll all the way to the right and then press the Shift key while selecting the last column.
- Column data format: Text
If you selected all of the columns, you'll see a label above each column in the preview that says Text.
Select Finish.
When Excel asks Where to you want to put the data?, choose Existing sheet and select OK.
See the below example of a CSV file successfully imported into Excel.
Importing CSV data into Excel (PC)
The PC steps are similar to the Mac steps, so reference the above images as needed.
- When you download a CSV, save the file to your desktop. Don't open or double-click the file.
- Navigate to Excel and open a blank spreadsheet.
- Select Data > From Text/CSV.
- Select the saved CSV file.
- Select Import.
- Data type: Delimited.
- Select Next.
- Delimiters: Comma (no other options should be checked).
- Select Next.
- In the Data Preview window, select all columns. Tip: Select the first column, scroll all the way to the right and then press the Shift key while selecting the last column.
- Column data format: Text.
- If you selected all of the columns, you'll see a label above each column in the preview that says Text. Select Finish.
- When Excel asks Where to you want to put the data?, choose Existing sheet and select OK.