Many datasets are stored in formats other than Excel’s XLSX format. One of the most common storage formats used across the data science world is the CSV or comma-separated values format.
A CSV file is a text file containing data where the columns of data are separated by commas. Here’s a sample of what a CSV file might look like if you open it up in a text editor:
month,sales,costs Jan,1000,500 Feb,2000,800
Here’s what this same CSV file looks like interpreted as a table:
month | sales | cost |
---|---|---|
Jan | 1000 | 500 |
Feb | 2000 | 800 |
Other Delimiters
CSV files typically use commas as delimiters, the symbol that indicates where one column ends and another begins. However, in some cases this doesn’t work very well. For example, in some countries the ,
is used as a decimal marker. In these countries, using a ,
as a delimiter will cause problems, since Excel can’t tell the difference between ,
as a decimal and ,
as a delimiter. In cases like this, it’s better to use another symbol, like the semi-colon ;
, as a delimiter. Even with a different delimiter, the format is still called CSV.
Excel provides an import tool that lets us select which symbol is being used as a delimiter. We’ve placed a slideshow illustrating this process in the learning environment. Note that some of the buttons and menus might look slightly different depending on your operating system and version of Excel.
When you’re ready to practice this yourself, get started on the instructions below!
Instructions
When you’re ready to practice what you’ve learned, download and work through our exercise spreadsheet.
Note that for this exercise, you will also need to download a CSV file. We’ve provided two:
- if your computer uses
.
for decimals, download this comma-delimited file - if your computer uses
,
for decimals, download this semicolon-delimited file
Feel free to download our solutions spreadsheet to compare your work to ours! Note that you might get an External data connections have been disabled
warning. When we imported the CSV, that established a connection between Excel and the CSV file. You can ignore this warning.
When you are ready to learn more, select Next
!