The files CSV (comma separated value, i.e. values separated by commas) are files widely used to contain values and data of all types and separated from each other by commas (or semicolons in some cases). The file CSVunlike the file Excelhas no limits on the rows it can store and for this reason it is a type of file used to contain a lot of data and to function as a real database.
If you have saved a CSV file on your computer (for example you have exported CSV contacts from your mobile phone) and you have tried to open it, you will most likely have noticed that although it is displayed in Excel, all the data does not appear ordered, but rather appears in a single column resulting in data confusion. In this article we will see what the best solutions are for open CSV files with Excelso as to have all the data in their place, in their respective columns.
By doing a Google search on “how to open CSV files with Excel” there are various guides and tutorials that describe the manual procedure to follow, which differs based on the Excel version installed on your computer. Sometimes, however, these guides, instead of simplifying the work, can complicate it, in fact the procedure to follow is easier than you might imagine.
It takes just a few seconds to import e open a CSV file with Excel, without any risk of data loss. To make it as simple as possible we have decided to publish short video tutorials that show the few clicks to do and the procedure to follow… look at them, it’s really simple! Each video refers to a version of Excel, so check your Excel version first and then watch the relevant video.
(VIDEO) Open CSV Files with Excel 2007/2010/2013/2016
Open CSV Files with Excel (Guide)
If the video above isn’t very clear don’t worry. Here are the simple steps to correctly import and open a CSV file in Excel:
1. Open the CSV file via Excel (you will see all the data confusingly)
2. Select the first column of the file (by clicking on the column letter TO top left)
3. Click above Data –> Text in Column
4. select the “Delimiter” box
5. Select the comma (or semicolon) as the separator field: all data will automatically be divided by column
That’s all! This method just described takes only a few seconds to sort all the data of a CSV in Excel. Depending on the version of Excel installed on your computer, the names of the options indicated above may change, but the procedure remains practically the same. For example in EXCEL 2003 you will have to click at the top on Menu Data -> Import external data -> from text; in EXCEL 2007/10: Data menu -> “from Text” button located on the left.
Below we point out 2 other alternative methods for opening CSV files with Excel.
Convert CSV to Excel online
The first method is to use a free service conversion from CSV to EXCEL. Among the many services there is for example THIS which allows you to convert CSV files to EXCEL (XLS or XLSX) in seconds. Once opened, the Excel file obtained will have all the data in the respective columns.
Open CSV in Excel via Notepad
The second method alternative instead involves doing these operations:
a) Rename the CSV file to a .TXT extension file
b) Open the TXT file with Notepad (Notepad)
c) Click on Edit –> Replace and replaces all the commas with semicolons (or with the TAB)
d) Save the TXT file and then rename the extension again from TXT to XLS (or better to XLSX)
e) Open the Excel file and you should see all the data sorted by column