How to: Convert CSV to Excel
When you're working with data in CSV format, it's likely you will need to open the data in a spreadsheet program, like Excel. It's no secret that there are a few "kinks" that can happen (commonly!) when trying to open your file in Excel. To avoid these frustrating setbacks to what should be a simple task, we'll walk through here how to successfully open a CSV in Excel, error-free.
Before we begin, let's review those most common head-scratchers that you may encounter while trying to open a CSV file in Excel.
There are plenty of other issues that may arise, depending on your dataset, but these are the most common that can turn a simple task of opening a file, into a truly annoying project to deal with, if you're unfamiliar with the solutions to fix them.
What it boils down to is this: Excel is a spreadsheet program, designed to automatically detect and format data to fit it's model. What happens when you simply open a CSV (which is just plain text) is that your simple and straightforward data gets translated and transformed by Excel's programming. During this process, things can go a little awry. Excel can make mistakes (yes really!) when it translates your data.
So, what do we do about it?
Why, we follow the specific steps to help Excel translate the CSV file correctly. It sounds more complex than it really is. The lovely people at Excel know about this file translation hijinks, and they have a built-in feature for you to use, so that it doesn't happen to you. So, skip the File > Open shortcut and follow these steps instead...
Before we begin, let's review those most common head-scratchers that you may encounter while trying to open a CSV file in Excel.
- 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. You will find yourself in good company if you go straight to Excel File > Open > your CSV file and then watch those all important digits fall off into the abyss...
- Values changed to date format - another formatting issue, you'll see numbers magically transformed to date format, rendering the original information unreadable.
- All text is consolidated to one column - as if Excel read your file as one long, run-on sentence. You will have no tabular formatted data, because everything is crammed into one column.
There are plenty of other issues that may arise, depending on your dataset, but these are the most common that can turn a simple task of opening a file, into a truly annoying project to deal with, if you're unfamiliar with the solutions to fix them.
What it boils down to is this: Excel is a spreadsheet program, designed to automatically detect and format data to fit it's model. What happens when you simply open a CSV (which is just plain text) is that your simple and straightforward data gets translated and transformed by Excel's programming. During this process, things can go a little awry. Excel can make mistakes (yes really!) when it translates your data.
So, what do we do about it?
Why, we follow the specific steps to help Excel translate the CSV file correctly. It sounds more complex than it really is. The lovely people at Excel know about this file translation hijinks, and they have a built-in feature for you to use, so that it doesn't happen to you. So, skip the File > Open shortcut and follow these steps instead...
Convert CSV to Excel 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"
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" (remember our delimiters review!)
5. Start the import at row 1 (this should be the default selection here), and check the preview to make sure everything is looking gravy...
4. Select the file type "Delimited" (remember our delimiters review!)
5. Start the import at row 1 (this should be the default selection here), and check the preview to make sure everything is looking gravy...
6. Choose your delimiter (most commonly this is comma, so if you're not sure, go ahead and choose this option. Again, you can check the preview to make sure you're looking good.)
7. Select the text qualifier. The most common is a double quote ("), so again, if you're not sure, go ahead and select this. Simply put, your text qualifier is how Excel will understand what are separate values in your file. And practically speaking, it's how Excel knows the difference between a delimiter, and just text (for example, if you have the value "5,000", the quote acts as the text qualifier telling Excel it is the whole value "5,000" and not two separate values "5" and "000", if your delimiter is a comma.)
7. Select the text qualifier. The most common is a double quote ("), so again, if you're not sure, go ahead and select this. Simply put, your text qualifier is how Excel will understand what are separate values in your file. And practically speaking, it's how Excel knows the difference between a delimiter, and just text (for example, if you have the value "5,000", the quote acts as the text qualifier telling Excel it is the whole value "5,000" and not two separate values "5" and "000", if your delimiter is a comma.)
8. Choose your data format. This is key to avoid 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 keep hands-off, 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" file into an ".XLS" (or .XLSX, etc.) file.
Voila!
Voila!