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.
If you open this file using a notepad, it looks like this:
When you double-click it, it opens automatically in Excel.
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.
- Open Excel file (xlsx).
- Navigate to Data >> Get & Transform Data >> From Text/CSV.
- 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).
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.
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.
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).
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).
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.
The first row was removed and the second one is used as headers.
Click Close & Load in the upper-left corner to place data inside a sheet in the form of a table.
Now, if you check the formatting of data under “Birth day”, all of it is formatted to the general type.