Create and Open
How to open CSV data in Excel properly
When you're working with data in CSV format, it's likely you will need to open the data in Excel. And it's no secret that this process (commonly!) is known as not too straightforward. To avoid any frustration below is a step-by-step instruction on how to successfully open a CSV in Excel.
There are plenty of issues that may arise, but these are the most common and annoying ones that you’re likely to face while trying to open a CSV file in Excel.
Excel is a spreadsheet program, designed to automatically detect and format data. And during this process, things can go a little awry. Excel can make mistakes when it translates your data.
The people who created Excel are aware of these CSV file translation blunders, and they have a built-in feature to prevent it from happening to you. So, instead of using the File > Open shortcut, use these steps.
Import CSV data into Excel spreadsheet via Text Import Wizard
1. Open the spreadsheet where you want to view your CSV data (this may be a new, blank spreadsheet, or an existing one)
2. Click on the "Data" tab and then select "From Text"
There are plenty of issues that may arise, but these are the most common and annoying ones that you’re likely to face while trying to open a CSV file in Excel.
- You have no tabular formatted data and everything is crammed into one column
- Leading zeroes are dropped - an absolute nightmare if you're dealing with zip codes, account numbers, or any other important numerical value in your dataset
- Numbers magically transformed to date format, rendering the original information unreadable
Excel is a spreadsheet program, designed to automatically detect and format data. And during this process, things can go a little awry. Excel can make mistakes when it translates your data.
The people who created Excel are aware of these CSV file translation blunders, and they have a built-in feature to prevent it from happening to you. So, instead of using the File > Open shortcut, use these steps.
Import CSV data into Excel spreadsheet via Text Import Wizard
1. Open the spreadsheet where you want to view your CSV data (this may be a new, blank spreadsheet, or an existing one)
2. Click on the "Data" tab and then select "From Text"
3. Browse for your CSV file and then click "Import"
You will now follow the steps of the Text Import Wizard, which can vary slightly for some users, but we'll follow the most common selections here in our guide.
4. Select the file type "Delimited"
5. Start the import at row 1 (this should be the default selection here).
There is also a preview pane here where you can see how your data will be transformed based on your selections.
You will now follow the steps of the Text Import Wizard, which can vary slightly for some users, but we'll follow the most common selections here in our guide.
4. Select the file type "Delimited"
5. Start the import at row 1 (this should be the default selection here).
There is also a preview pane here where you can see how your data will be transformed based on your selections.
6. Choose a delimiter. It can be a comma or other delimiter (here is our delimiters review) You can see one in the Import Wizard preview to be sure.
7. Choose a text qualifier. Most commonly used are quotation marks (""), but if you're not sure, choose this option. This is how Excel distinguishes between a delimiter and a text (you delimiter can be just a part of your value). For example, if your delimiter is a comma and you have the value "100,000" in your data, the quotation marks act as the text qualifier telling Excel that it is the entire value "100,000" and not two separate values "100" and "000."
7. Choose a text qualifier. Most commonly used are quotation marks (""), but if you're not sure, choose this option. This is how Excel distinguishes between a delimiter and a text (you delimiter can be just a part of your value). For example, if your delimiter is a comma and you have the value "100,000" in your data, the quotation marks act as the text qualifier telling Excel that it is the entire value "100,000" and not two separate values "100" and "000."
8. Choose your data format. This is key to avoiding issues like those dates magically appearing or leading zeroes dropping off and more. If you have any numeric values you want to keep "untouched" - then you can select the column and choose the type "Text" in this step. This tells Excel to stay away and not try to guess the format of your data.
9. Now confirm the location where you wish to import the data (i.e. new spreadsheet, or existing) and click OK!
The end result here is that, by importing the CSV data into Excel, you have actually converted your CSV data into a good Excel spreadsheet.
