Open CSV File

Open CSV File in Excel

Microsoft Excel has full support of CSV file format specification. It means that any correctly formatted CSV file could be open in Microsoft Excel. The only potential issue could with size of the file. Very large CSV files could be time consuming to load and process. That is why if you have a large CSV file it may be a good idea to split it into multiple CSV files.

There are two ways to open CSV file in Microsoft.

First one is by using File->Open… dialog which will automatically try to interpret data in CSV file and format it. The benefit of it is that it is quick and easy but the problem with it is that it makes all decisions how to format the data and may incorrectly present the data.

If you want more control on how Microsoft Excel will parse CSV data, click “Data->Get External Data->Import Text File…” from the main menu. You will see a file open dialog where you can select your CSV file. When you click on “Get Data” button in that dialog you will see another window open with a sample of your data and more information on how Microsoft Excel plans to process it.

Microsoft Excel Text Import Wizard could be used to import data in multiple data formats including CSV, TSV, and others. Below we will show how to open CSV file with it. The process contains three steps.

In step 1 you can select whether data in the file is delimited or fixed width, choose File origin encoding in the “File origin” field and choose line number from which import should start. For CSV file make sure to select “Delimited” option, since comma is a delimiter. Click “Next” to proceed.

In step 2 you will be asked to choose a delimiter which is used in the file to separate data values. Choose “Comma” and uncheck “Tab” if it is checked as shown below. Choose “Text Qualifier” in the corresponding combo box. Text qualifier is a symbol used to enclose values which can contain commas inside them. It is needed for Microsoft Excel to process data correctly. For example, “John, Doe” enclosed in double quotes will be interpreted as a single value and comma inside it will be ignored. Last option is “Treat consecutive delimiters as one”. Typically, you will need to uncheck it as by default empty values are denoted with a dedicated delimiter. Click “Next” again to proceed.

In step 3 of the Microsoft Excel Text Import Wizard you can select data format for each column parsed from the CSV file. As you can see below you can click on any column in the file and choose data format to use while parsing. Data format could be one of the following:

• General
• Text
• Data
• Do not import column (Skip)

You can also click on “Advanced…” and choose decimal and thousands separators to be used in CSV file.

Click on the “Finish” button to process and open the CSV file. You will see your data correctly imported into corresponding Excel rows and columns. If you see any errors with the import process you can choose “Data->Get External Data->Import Text File…” menu option again to reimport the file.