One of the most frequently asked questions from beginners to Excel is ‘what is a csv file?’ Specifically, what is the difference is between an Excel file (.xlsx) and a (.csv)? They can both be opened in Excel, so why are they saved as a different file type?
What does CSV Stand For?
CSV stands for “comma separated values”. In the case of this file, the “delimiter” is a comma and it is used to separate individual pieces of data. To better illustrate, we’re actually going to start with a text file and work our way back to Excel. CSV files are often called “flat files“, a term that can be used interchangeably with csv. The file is “flat” because the data is in its most raw form.
The most common reason data might be delivered to you as a flat csv file is to save space. The file size is much smaller to begin with and easier to transfer via email, download link, slack exchange, etc. Sometime you might receive a folder of csv files you have to combine later. For now, we’ll focus on how a csv file looks in its most raw format.
Importing Comma Separated Values into Excel
The text (.txt) file below is ‘names.txt.’ It very clearly contains three rows of name, address, & phone number. Each of those values is separated by a comma. Because the data is organized in this way, when we open up this exact file in excel, it will interpret each comma separated value as being in its own column.
Upon opening our .txt file in Excel, we encounter the “text import wizard”. Again we see the magic word: delimited.
On the second page of the import wizard, we select comma. As you can see in the lower half of the picture, Excel has already started to parse out these values based on our criteria of comma as a delimiter.
And here’s our final product. Every value separated by a column is in its own column. The same result would happen with 3 rows or 300,000. So even though we’re looking at the exact same file, its now in a format we can use in Excel.
Now that we have our data formatted in Excel, we can save the file as a .csv file and it will open up in Excel instead of Notepad. If you’re adding formulas, charts, illustrations, etc you’ll want to save this file as an Excel File. Otherwise, those additional elements will be stripped out.
Click here to read our post about the specific differences between Excel files and CSV files