How to convert (open or import) CSV file to Excel

How to open CSV file in Excel

A comma separated values file created in another program can still be opened in Excel by using the standard Open command.

1. In your Excel, head over to the File tab and click Open, or press the Ctrl + O shortcut.
2. In the Open dialog box, select Text Files (*.prn;*.txt;*.csv) from the drop-down list in the lower right corner.
   

3. Browse for the CSV document, and then double-click it to open.

A comma separated values file (.csv) will be opened in a new workbook straight away.

For a text file (.txt), Excel will start the Import Text Wizard. See Importing CSV to Excel for full details.

How to open CSV file from Windows Explorer

The fastest way to open a .csv file in Excel is to double click it in Windows Explorer. This will immediately open your file in a new workbook.

However, this method only works only if Microsoft Excel is set as the default app for .csv files. In this case, a familiar green Excel's icon appears next to .csv documents in Windows Explorer.

If your CSV files are set to open with another default app, then right-click the file, and choose Open with… > Excel.

To set Excel as the default program for CVS files, here are the steps to perform:

1. Right-click any .csv file in Windows Explorer, and then select Open with… > Choose another app from the context menu.
2. Under Other options, click Excel, check off the Always use this app to open .csv files box, and click OK.
   

How to import CSV into Excel with Text Import Wizard

First off, it should be noted that the Text Import Wizard is a legacy feature, and beginning with Excel 2016 it is moved from the ribbon to Excel Options.

If the Text Import Wizard is not available in your Excel version, you have these two options:

• Enable From Text (Legacy) feature.
• Get Excel to launch the Import Text Wizard automatically. For this, change the file extension from .csv to .txt, open the text file from Excel, and then follow the steps of the wizard described below.

To import a CSV file to Excel, this is what you need to do:

1. In Excel 2013 and earlier, go to the Data tab > Get External Data group, and click From Text.
   

   In Excel 2016 and later, go to the Data tab > Get & Transform Data group, and click Get Data > Legacy Wizards > From Text (Legacy).
   

   Note. If the From Text wizard is not there, make sure you have it enabled. If Legacy Wizards is still grayed out, select an empty cell or open a blank worksheet and try again.

2. In the Import Text File dialog box, browse for the .csv file you want to import, select it and click the Import button (or simply double-click the file).
   

3. The Text Import Wizard will start, and you follow its steps. First, you choose:
   • The Delimited file type
   • The row number to start import at (normally, row 1)
   • Whether your data has headers

   The preview window in the lower part of the wizard shows a few first entries from your CSV file.
   

4. Choose the delimiter and text qualifier.

   Delimiter is the character that separates values in your file. As CSV is a comma separated values file, obviously you select Comma. For a TXT file, you'd typically select Tab.

   Text qualifier is the character that encloses the values in an imported file. All text between two qualifier characters will be imported as one value, even if the text contains the specified delimiter.

   Generally, you choose the double quote symbol (") as text qualifier. To check this, you can click Back and see which character encloses the values in the preview of your CSV file.

   In our case, all numbers with a thousands separator (which is also a comma) are wrapped in double quotes like "3,392", meaning they will be imported in one cell. Without specifying the double quote sign as the text qualifier, the numbers before and after a thousands separator would go into two adjacent columns.

   To make sure your data will be imported as intended, look carefully at the Data preview before clicking Next.
   

   Tips and notes:

   • If more than one consecutive delimiter is present in your CSV file, then select the Treat consecutive delimiters as one option to prevent empty cells.
   • If the preview shows all data in one column, that means a wrong delimiter is selected. Change the delimiter, so that the values are displayed in separate columns.
5. Define data format. The default is General - it converts numeric values to numbers, date and time values to dates, and all remaining data types to text.

   To set another format for a specific column, click anywhere within it in the Data Preview, and then choose one of the options under Column data format:

   • To keep leading zeros, choose the Text format.
   • To correctly display dates, choose the Date format, and then pick an appropriate format in the drop-down box.

   When you are happy with the Data preview, click the Finish button.
   

6. Choose whether to import data to an existing worksheet or a new one, and click OK.
   

   Tips and notes:

   • To configure some advanced options such as refresh control, layout and formatting, click Properties… in the above dialog box.
   • If some imported data are displayed incorrectly, you can change the format with the help of Excel's Format Cells feature.

Here are a couple of helpful tips on how to prevent automatic data formatting when importing from a text file.

How to transfer CSV to Excel by connecting to it

In Excel 365, Excel 2021, Excel 2019 and Excel 2016, you can import data from a text file by connecting to it with the help of Power Query. Here's how:

1. On the Data tab, in the Get & Transform Data group, click From Text/CSV.
   

2. In the Import Data dialog box, select the text file of interest, and click Import.
   

3. In the preview dialog box, the following options are available to you:
   • Delimiter. Pick the character that separates values in your text file.
   • Data Type Detection. You can let Excel automatically determine data type for each column based on the first 200 rows (default) or entire dataset. Or you can choose not to detect data types and have the data imported in the original Text format.
   • Transform Data. Loads the data to the Power Query Editor, so you can edit it before transferring to Excel. Make use of this feature to set the desired format for particular columns.
   • Load. Controls where to import the data. To get the csv file imported to a new worksheet, select Load. To transfer the data to an existing or new sheet in the form of a table, PivotTable/PivotChart, or only create a connection, choose Load to.

   

   Clicking the Load button will import the CSV data in the table format like this one:
   

   The imported table is linked to the original CSV document, and you can update it anytime by refreshing the query (Table Design tab > Refresh).

   Tips and notes:

   • To change the table to a normal range, right-click any cell, and then click Table > Convert to Range. This will permanently remove the query from the sheet and disconnect the imported data from the original file.
   • If values in a certain column are imported in a wrong format, you can try fixing it yourself by converting text to number or text to date.