CSV files are a very popular and versatile file format that makes it easier to collect and share very large data (i.e. databases).
Whether at school or work, they use them for all sorts of data management, and most software can open them. But what is a CSV file, and how do I open it?
In this guide, you’ll learn:
- What is a CSV file?
- What does a CSV file contain?
- What is a CSV file used for?
- How to open a CSV file
- How to create a CSV file
What is a CSV file?
A Comma Separated Values (CSV) file is a plain text file that stores a list of data in a tabular form. All CSV files use the .csv suffix.
A separator, also known as delimiter separates the values in each row. That way, it makes it easier for both humans and computers to read and write the data.
The most common delimiter is the comma (,), but it could be another character depending on certain factors (see next section below).
What does a CSV file contain?
A CSV file is a simple flat file, which contains data in its most basic form. The contents are usually plain texts, numbers, characters, or dates broke up in a row by a delimiter.
For example, in many European countries, they use a comma to separate the integral part of a number from the decimal part. In that case, a CSV file separated by a comma would create a problem like extra columns making the data inaccurate.
That’s when they can use other characters to separate the stored values, including:
- Semicolon (;)
- Tab (\t)
- Space ( )
- Single or Double Quotation Marks (”) (“”)
- Pipe (|)
Sample of a CSV file contents
item, cost, condition, unit cellphone, $450, refurbished, 1 airpods, $199, new, 1 macbook pro, $1599, new, 1 ipad, $799, new, 1
The first row in a CSV file contains the column headings. In this case, our column headings are item, cost, condition, unit. When you import a CSV file into a database, each line represents a row of the table (a data record).
This is how this CSV data looks like in Excel.
There is no other layering of data in a CSV file. For example, a CSV file will not carry any graphics, fonts, or specific designs. It just carries plain text formatted in a particular way. This particular format helps the CSV file to be the file of choice in many scenarios.
What is a CSV file used for?
The most common uses of a CSV file is to store and transfer data between applications that use proprietary format.
For example, you may want to transfer information from a database application that stores data in a proprietary format to a spreadsheet program that doesn’t support this format.
Since CSV files are easily read in many different software, there’s a high possibility that the database application can export its data as CSV. Then, you can import the exported CSV file to the spreadsheet program.
A more specific example is when you want to switch to a different password manager. In such a situation, you’d use a CSV file to export your passwords from your old password manager to the new one.
How to Open a CSV file
Any software that can open a .txt file can open a .csv file. After opening a CSV file, you typically can see that there is a header (as seen in the example above). The first row in a CSV file is called the header, which carries the name of the columns. The header describes what kind of content is listed under each column.
There are many ways to access the contents of a CSV file. You can even use a notepad to open a CSV file, using the File function and hit Open. Look for a file with the .csv extension, and hit OK. It may take some time, though if you’re trying to open a large CSV file (file with size in GBs) in a note pad.
However, there are more advanced programs for processing data. Spreadsheet applications or programs like Microsoft Excel, Google Sheets, or OpenOffice Calc are more advanced programs that support multiple file formats, including the CSV format.
How to open a CSV file in Excel
Microsoft Excel supports CSV out of the box and comes as the default software to open .csv files. That means you can double click on a CSV file on a computer that has Excel installed, and the file will open automatically.
If you already have Excel open, click on File at the menu bar, then select Open from the dropdown menu and browse to the location that contains your CSV file. Select your file to open it directly in Excel.
If your CSV file delimiter is not a comma (,), the contents may not get separated into individual columns, and the rows may be lumped into a single column.
As you can see in the image below, this CSV file’s delimiter is a pipe (|), which made Excel parsed the contents into a single column (column A).
Do the following to parse other delimiters in Excel.
- Select the column which has the values you want to separate. Click on Data at the menu bar, then Text to Columns.
- A Convert Text to Columns Wizard will come up with Delimited and Fixed Width options (You can also see a preview of the text which you highlighted in the first step). Select Delimited, then click Next.
- In the next screen, you will get options to choose your delimiter. Your delimiter may not be in the list. If that’s the case, check the box next to Other to add your delimiter.
- Click Next, then select Finish in the next window so Excel can parse your CSV file values into columns. Below is the final result.
How to open a CSV file in OpenOffice Calc
You can also use the program OpenOffice Calc to open a CSV file. If OpenOffice is installed on your computer, by default, CSV files should open automatically in Calc when you double-click on the file (if course that type file was associated with it).
If OpenOffice is not the default program to open these types of files, right-click on the file and select OpenOffice from the dropdown menu. Look over the options to make sure the correct ones are selected.
In case you already opened the OpenOffice Calc program, on the menu bar select File, then click Open, and select the CSV file.
How to open a CSV file in Google Sheets
Google sheets, like Microsoft Excel, provides a very straight forward method to open CSV files. It also gives an option to review the data before you import it.
- Access Google Sheets, and open a Blank file. In the menu bar (not your computer menu bar), select File, then click on Import.
- Select the Upload tab. You can either drag and drop your CSV file or hit the Select a file from your device blue button.
- An Import File window will pop up on the screen with different options like Import Location, Separator type, etc. Make your selection or keep the default values. Then select Import data.
Once imported your CSV file, Google Sheets will beautifully parse the values into separate columns.
How to create a CSV file
Did you know that you don’t need special programs to create a simple CSV file? You can simply use a notepad to create a string of values, each separated by a comma. Save the file with .csv extension file name, and you have yourself a CSV file. It’s that straightforward.
That said, some applications natively use this file format as it’s pretty easy and fast to do transfers of high volume data into and out of a database.
If you choose to export your mobile phone contacts, they would typically get exported in a CSV file format. So does your Gmail contacts with all the attributes exported as a CSV file.
CSV is a popular and reliable file format that make our life easier. Most spreadsheet programs and database applications that we use every day offer native support for importing and exporting .csv files.
Now that you understand the capabilities of a CSV file, you’ll get more comfortable to handle it in the future.
Was this guide helpful? Then, please share it with your friends.