Open CSV files safely with Excel

The CSV file format is a common data storage format that is used to store tabular data. It is often used for exchanging data between applications.

How to open CSV files?

If you don’t have any other program installed that is responsible for opening CSV files, Excel is probably the default one. You can recognize this by looking at the CSV file icon.

Application, icon

Description automatically generated

If you open this file using a notepad, it looks like this:

Graphical user interface, text, application

Description automatically generated

When you double-click it, it opens automatically in Excel.

Table

Description automatically generated

This way of opening files is good for small ones as data is not that complicated and everything should work fine.

What can go wrong?

When you deal with CSV files, especially large ones, in Excel, there is a big chance that something can go wrong. Problems you may encounter can be divided into several types:

Wrong delimiter

In CSV files, there are a few popular types of delimiters: comma, semicolon, or tab. If you have more than one inside a file, Excel can recognize the wrong delimiter as the one that should be used.

Wrong date format

You can use date format, which may not be obvious that it’s incorrect, e.g., writing days in place of months.

Non-date column recognized as a date

If Excel recognizes something in the CSV file as a date (often incorrectly), it will change the formatting from general to date.

But sometimes some fields (for example, part number) can look like dates but should be treated as normal text.

Why do you want to open these files safely?

Let’s look at how to open CSV files using a safe approach.

  1. Open Excel file (xlsx).
  2. Navigate to Data >> Get & Transform Data >> From Text/CSV.
  3. Select a file to import.

After you import a file, there is a preview with data from that file (based on the first 200 rows).

Table

Description automatically generated

As you can see, the first row from the file is automatically recognized as a header. The delimiter is set to commas as no other characters such as semicolons or tabs are present inside the file.

Click Load to import data and convert it to a table.

Table, Excel

Description automatically generated

The tab of a worksheet changes its name to the name of the imported file.

You can also click Load To… from a dropdown button where you can choose the way you want to view the data.

Graphical user interface, text, application

Description automatically generated

Fixing incorrect dates

Open a file in notepad and modify the date 12/1/1990 and change it to value: 22/1/1990. As the first value in date and time notation in the United Stated is month, therefore the value is incorrect.

Let’s see what happens we you open the file by double-clicking it.

At first, everything looks fine, but when you click the value C2, you can see what type of formatting is used there (Home >> Number).

A picture containing table

Description automatically generated

When you click the remaining dates (C3 and C4), the formatting is set correctly to Date.

That’s why it’s safer to import files.

If you import the file, you can notice that the first row of data is not used for headers (Column1, Column2, Column3).

Table

Description automatically generated

Is this the case, you should look whether there is everything ok with the data. If you checked and everything is fine, and you still want to use the first row as a header, click the Transform Data button.

Now, it’s time to remove the first row.

Inside the Power Query Editor, go to Home >> Transform >> Use First Row as Headers.

Graphical user interface, text, application

Description automatically generated

The first row was removed and the second one is used as headers.

Graphical user interface, text, application

Description automatically generated

Click Close & Load in the upper-left corner to place data inside a sheet in the form of a table.

Application, table, Excel

Description automatically generated

Now, if you check the formatting of data under “Birth day”, all of it is formatted to the general type.