Many software programs offer data output in the form of a downloadable CSV file.
Owing to their simplicity and versatility, CSV files take up less space and are easily transferable. As such, CSV is often the go-to extension for a lot of database programs.
In fact, CSV and spreadsheets go hand in hand.
That is why Excel provides multiple ways to convert a CSV file to Excel. In this tutorial we will look at three such ways:
- By opening the file from Windows Explorer
- By opening the file directly in Excel
- By importing the file
We will also look at a few issues you are likely to face when you convert a CSV file to Excel and how to address and resolve these issues.
What is a CSV File?
A CSV file is a very commonly used file extension.
It is a plaintext format file in which values are separated by commas, hence the name Comma Separated Values (CSV).
However, it is not uncommon to find CSV files where the values are separated by other symbols like semicolons or tabs.
These files can be opened using any spreadsheet program like Excel, Google Sheets, Open Office, etc.
You can also open them in a simple text editor like Notepad.
The files contain simply the data in text form, without any formatting or formulas.
Owing to their ease of storage and their compatibility, the CSV format is a popular file format.
3 Ways to Convert a CSV File to Excel
The data in CSV files do not contain any kind of formatting.
So, if you want to use it in Excel, you will need to first get Excel to format the data into a form that can be displayed in cells.
Let us look at three ways in which we can convert a CSV file to Excel.
Method 1: Directly Opening a CSV File in Excel from Windows Explorer
You can directly open a CSV file that is in any folder from Windows Explorer or your file browser.
To open the file in Excel, simply double-click the file.
Most of the time computers are set up to open CSV, TSV and other similar files directly in Excel, if it is present on your computer.
You can tell if Excel is the default program to open the file from the familiar green Excel icon as shown below:
However, if your computer has not been set up to open CSV files directly in Excel, you can set it up as follows:
- Right-click on the CSV file.
- Select ‘Open With’ from the context menu that appears.
- You should now see a submenu with a list of applications. If you see ‘Excel’ as one of the options, go ahead and select it.
- If you don’t see Excel as one of the options, then click on ‘Choose another app’.
- Select Excel from the app options (Click ‘More Apps’ for more app options).
- Click on the checkmark next to ‘Always use this app to open .csv files’. This will make sure that Excel becomes the default program to open all CSV files from now.
Method 2: Opening a CSV File Directly in Excel
You can also open a CSV file directly in Excel as follows:
- Open Microsoft Excel.
- Click on the File tab.
- Click on Open.
- Browse and select the CSV file that you want to open.
That’s it! Your CSV file should automatically open in Excel.
Once the file opens, you might find some data items looking out of place.
You might also find some of the data formatted differently from what you had expected.
This is because Excel uses your default data format settings to convert each column of the original file to Excel.
However, there is no need to worry. You can always format the data according to your requirements either in the original CSV file or within Excel.
We will discuss more on how to do that towards the end of this tutorial.
Note that the first two methods discussed so far do not change the format of the CSV file to XLS or XLSX.
If you save the file, it will still get saved as a CSV file.
Method 3: Importing a CSV File to Excel
This method lets you import and converts data from a CSV file into an existing Excel worksheet.
The steps to import a CSV file to Excel is as follows:
- Select the cell from where you want to start displaying the imported data.
- From the Data tab, select ‘From Text’ under the ‘Get External Data’ group.
- This will open a dialog box, from where you can select the CSV file that you want to import.
- Either double click on the file name or select the file and press Open.
- This will open the ‘Text Import Wizard’.
- The first step of the wizard lets you choose the file type that best describes your data. Make sure the ‘Delimited’ option is selected.
- Click Next.
- The second step lets you set the delimiter by which you want to separate the data. Since the data items in our CSV file are separated by commas, make sure the ‘Comma’ option is checked.
- You can also make sure the checkbox next to ‘Treat consecutive delimiters as one’ is checked. This ensures you don’t get unnecessary blank cells due to the presence of repeated consecutive commas (that were probably there by mistake).
- Click Next.
- The third step lets you set the format for each column. At the bottom of the wizard window, you can see a preview of how your data is going to look after splitting the column. If you want to change the format for any of the columns, simply select the column you need to format from the preview and select the radio button corresponding to the format you want for that column. If you don’t want the column to show at all, then you can select the ‘Do not import column (skip)’ option.
- Click Finish.
- Finally you will get asked to confirm where you want to start displaying your imported data. If importing to the same sheet, check if the starting cell is correctly mentioned in the input box below ‘Existing worksheet’. If you want to display the imported data in a new worksheet, check the radio button next to ‘New worksheet’.
- Click OK.
You should now see your CSV data imported into the cells starting from your selected cell.
Handling Problems that Arise After Converting a CSV File to Excel
Now let us take a look at some issues that are likely to arise when you convert a CSV file to Excel and how to address and handle these issues.
Some of the problems you might see after conversion include:
- Data displayed in a single column
- Values not separated as expected
- Lost leading zeros in numerical values
- Numerical or Text values converted to dates
- Dates converted to text
Let’s address each of these issues and see how to resolve them.
Data Displayed in a Single Column
After converting your CSV file to Excel you might find each record of data displayed in a single Excel column as shown below:
To break them up into separate columns, you can use the Text to Columns feature of Excel as follows:
- Select the range of cells containing your copied data
- From the Data tab, select the ‘Text to Columns’ button (in the ‘Data Tools’ group).
- This will open the Convert Text to Columns wizard.
- You will notice that the steps in this wizard are exactly like the ‘Text Import Wizard’ discussed in method 3 of this tutorial (importing a CSV file to Excel). So you can follow steps 6 to 12 from method 3.
- At the end of 12, when you click the Finish button, you should see your CSV data imported into the cells starting from your selected cell.
Your data should now be split into separate columns for each field, based on the comma delimiter in the CSV file.
You can now go ahead and further clean up / format the data as required.
Values not Separated as Expected After Converting CSV to Excel
In some cases, you might find the entire structure of the table gone awry.
This could be because you have different separators set in your computer’s regional and language settings from the separators set in the CSV file.
For example, in some countries, the default separator is the comma, while in certain others, it is the semicolon.
To solve this issue, you can do one of the following:
- Change the Separator in your Computer’s Regional Settings
- Import the File instead of Opening Directly
Change the Separator in your Computer’s Regional Settings
Specify the separator for your region directly in the CSV file, by typing “sep=,” or “sep=;” in the first line.
You can add this in any text editor, for example, Notepad.
Import the File instead of Opening Directly
Instead of directly opening the file, you can try importing the file (as shown in method 3 – importing a CSV file to Excel), and provide the right delimiter in Step 2 of 3 in the Text Import Wizard.
Note: Sometimes you might find more than one value put into a single cell. This might be because of the absence of a delimiter separating the two values in the original CSV file. To solve this problem, you can either open the CSV file in a text editor, add the separators in the right place, or you could split the cell using the Text to Columns feature. Make sure the cells to the right are empty before splitting the cell. You don’t want the existing values to get overwritten by the split values.
Lost Leading zeros in numerical values after Converting CSV to Excel
If your original CSV file contains numerical values with leading zeros, you might find them absent after converting the file to Excel.
This is because your Excel cells are automatically set to display numerical values in the General format.
This format removes leading zeros from numbers. If you want to retain the leading zeros, you can try changing the format of the column cells to Text.
Numerical or Text Values Converted to Dates after Importing CSV to Excel
You might find certain text values from the original CSV file mysteriously converted to dates after importing to Excel.
This could be because the original values in your CSV file happen to resemble date values. For example, you might have a user name as jan19, which Excel might interpret as a date.
The solution to this problem is simple. Simply change the format of the cell to Text.
Dates Converted to Text after Importing CSV to Excel
Finally, you might find certain dates from the original CSV file converted to text.
This might be because Excel did not recognize the date format, so it assumed it to be a Text value.
The solution to this problem is to simply convert the format of the cell to Date and set the date to your required format.
In this tutorial, we showed you three different ways to convert a CSV file to Excel.
We also pointed out certain issues you are likely to face after converting the file, and suggested solutions to overcome the issues.
We tried to make this tutorial informative enough so you know exactly what to do when using CSV files in Excel. Hope we did not miss any points.
Other Excel tutorials you may also find useful: