How to open CSV files in Excel

Despite their simplicity, Comma Separated Values files, also known by their extension CSV, are still quite popular in some areas, namely in sciences and software localisation. Most spreadsheet software, like OpenOffice and LibreOffice Calc, supports them out of the box and Excel does too, although it makes some assumptions about what a CSV file should look like. This, of course, means that you can’t open CSV files by double-clicking on them, because this will cause them to display in an unexpected way. And this also applies to CSV files generated in Windows too!

Take this, for example. It’s a CSV file that I exported from Windows’ Event Viewer. It details the latest boots in my system. As you may notice, Excel displays a few values. However, some data is missing. In my case, I wanted to see how long each boot took in my machine, but Excel won’t show that information.

Excel won't show every information contained in the CSV file.
Excel won’t show every information contained in the CSV file.

In other cases, your data may end up only in column A, because Excel didn’t bother to check if it could have been displayed in another way.

Some CSV files fail to display properly if opened by double-clicking.
Some CSV files fail to display properly if opened by double-clicking.

How to open CSV files in Excel

Thankfully, there’s a quick and easy workaround for this issue. Instead of double-clicking to open a CSV file, import it in a new spreadsheet. First, we’ll need to open Excel and click Empty Workbook.

Now, it’s time to import our CSV data into our workbook. First thing, let’s click on Data. On the left side is the Get external data group of options. Click on From text.

You will be prompted with the Import text wizard prompt, where we can tweak our settings.

The Import text wizard prompt.
The Import text wizard prompt.

On the bottom is a preview of our file. You can use it to determine how to go about with editing the settings for importing your data.

Here’s what each setting means:


Original data type

This field allows you to choose how data is separated. Delimited means that cells are separated using a single character, which can be a comma, a colon, a tabulation, a space or a user-defined character. Fixed width means that the file contains a fixed number of characters per each column, so that no additional separator character is needed.

Start import at row

Change the default value of 1 to discard values contained in previous lines. This can be useful if you only need to import a portion of the original file.

File origin

This drop-down menu allows you to select the correct encoding for your file. Excel always defaults to Windows (ANSI) encoding, so make sure you select the right encoding. Selecting a different encoding will preview the end result in the box at the bottom.

My data already has an header

Checking this box will treat the first row as the header of the file.


After you’re done tweaking the settings there, it’s time to move onto the second step by clicking Next. You will be presented with this prompt:

step2


Delimiters

Allows you to specify what character is considered a delimiter. If Excel doesn’t find commas, it assumes the tabulation character as the delimiter. The user can override this by selecting one of the four presets (TabulationSemicolonCommaSpace) or by entering the separator in the field Other.

Treat consecutive delimiters as one

If left unchecked, two consecutive delimiters indicate an empty cell. Otherwise, they will be ignored until a non-delimiter character or a line break or end of file is found, whichever comes first.

Text qualifier

Specifies a character between which delimiters are considered actual characters. Valid options are quotes, apostrophes and [none]. The following line

lunch,”I had pizza, french fries and a coke”,12 am

Will be treated as

qualifiers1

 

 

whereas the line

lunch,I had pizza, french fries and a coke, 12 am

will be treated as

qualifiers2

 

 

Data preview

Shows how the generated workbook will look like.


After clicking next, the third step allows to set the format for each column. Inverted colours indicate the currently selected column.

step3


Column data format

Allows to select between GeneralTextDate, and Do not import column (skip). General will convert all values to text. It’s unclear to me if there’s any difference between selecting General or TextDate will treat that particular column as containing a date. The drop-down box on its right allows to select the date format.

Advanced

Allows to define the decimal and thousands separator, as well as the presence of less of a minus sign for negative values.


Clicking Finish will allow the user to select where she wants to import the data. Usually, that’s cell A1. Clicking OK will close the prompt and import all data.

About Andrea Luciano Damico 137 Articles
Andrea Luciano Damico is a freelance translator from Italy. Among his interests are linguistics, technology, video games, and generally being a chill guy. He runs Let's Translate.it and Tech4Freelancers.net.