Sometimes we download from the internet or receive files in CSV format by other means. These files are table data that, unlike Excel files, are written in conventional text files.
The fact of being in this way can generate certain problems, but without a doubt the greatest of all is that if we have our computer located with a Spanish keyboard, we will realize that if we try to open these files the result is not what we expected. In the following example we see what happens when we open a csv with Excel , the example is a download of the history of Microsoft stock values downloaded from Google Finance:
It is not at all pleasant that something that we expect to be a table is displayed this way.
This is because since our operating system is located in Spain or any Spanish-speaking country, it will not recognize the comma (,) as a box delimiter. The reason this happens is that in our locale or language settings the designated delimiter is the tab. As if this were not enough, opening CSV files in Excel directly can cause more encoding problems and misidentification of the data.
To solve this, Excel provides us with a very friendly way to solve it, through the data import menu. Please carefully follow the steps below to successfully open a CSV file.
In this trick we are using Microsoft Excel 2016 and a sample CSV data file downloaded from the Google Finance platform corresponding to the historical stock market values of Microsoft shares.
If you want to follow this guide using the same CSV file that we use, you can download it by clicking here .
1 - Open Excel and create a new workbook.
2 - Go to the Data tab and click on "Get external data".
3 - Then click on "Data from a text file".
4 - An explorer window will appear to find and select the CSV file that we want to open. After choosing it, click on "open".
Immediately after, a wizard will jump to guide us in the process of importing and converting the data to our Excel workbook.
5 - Most likely our CSV file is delimited by commas so we select the option "Delimited". If the file includes headings or titles for the fields, do not forget to select the option "My data has headings". Once this is done, click next.
6 - We come to the most important step, where we choose the delimiter, which is usually comma. When we select it, we will see that it shows us an example of how our data will be selected and we can finally see the columns as they should be.
7 - We come to the step where we can indicate what type each column is. Select each column in the preview and indicate what type of data it is. If we leave it in "General" it will take it as a numerical value. We can select the date format as well, where DMA abbreviates day, month and year. Before clicking on "Finish" it is important to click on advanced and go to step 8 of this trick.
8 - Most likely, if the data we are importing has numbers, it is in American format , which, unlike Spanish-speaking countries, uses the point as a decimal delimiter and the comma to delimit thousands and millions. If this is the case, having clicked on advanced we have to indicate the format used by the data we are importing and put a point for the decimal separator and a comma for the thousands separator.
9 - We accept the advanced configuration window and click on finish.
10 - Once this is done, a last window will appear indicating if we want the data to be imported into the current document or if we want it in a new document. If you have already opened a new document before starting this, the behavior is exactly the same. Additionally we have another series of options to explore before clicking accept.
Now we should have correctly imported data in Excel, we have the columns as they should be and we can finally start working with them.
Comments
Post a Comment