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.
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.
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.
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.
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:
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 (Tabulation, Semicolon, Comma, Space) 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.
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
whereas the line
lunch,I had pizza, french fries and a coke, 12 am
will be treated as
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.
Column data format
Allows to select between General, Text, Date, 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 Text. Date will treat that particular column as containing a date. The drop-down box on its right allows to select the date format.
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.