What is a Comma Separated Values CVS File?

File and How to Use It?

In this blog post, I am going to introduce you to a CSV or comma separated value file, its core functionality, benefits, and business application.
You will receive complete information on comma separated values CSV files and also become familiar with several important details, such as:

Let's jump right in!

What is the CSV file format?

CSV is a file extension that refers to any file that ends in ".csv". It is also an abbreviation, which stands for comma-separated values. This kind of file is a plain text file that utilizes commas to isolate values. Each line of the file serves as a data record, which has one or more fields, separated by commas. Although the CSV file format is not fully standardized, it is a commonly used file extension when it comes to spreadsheets and exchanging data between different applications. Since all applications can recognize records separated by commas, importing such data files to your database can be done very conveniently.

Structure of a CSV File

Comma-separated Values file has an uncomplicated structure. CVS is a combination of fields (columns) separated by the comma character and records (rows) terminated by newlines. Per RFC 4180, this format has 4 core features

Here is an example of a CSV file.

A basic CSV file may look like the following:

Name, address, phone number, email

Example, Example Address, 111-222-3344, example@example.com

Eample2, Example2 Address, 999-888-7766, example2@example.com

This is all a CSV file is. Of course, a comma-separated values file can be more complex than that, it may contain millions of lines, more entrances on each one, or long strings of text. Moreover, some CSV files do not have headers, other files use quotation marks to surround each bit of data.

The most useful operation is when a CSV file is imported into a spreadsheet program, it looks like a traditional spreadsheet, separated by columns and cells. The carried data is easily readable and can be viewed with both text editors and spreadsheet programs.

A brief history of CSV files

The format dates back to the early days of computing for business and is widely used to pass data between computers with different internal word sizes, data formatting needs, and so forth. For this reason, CSV files are common on all computer platforms.

It began with the IBM Fortran (level H extended) compiler that started supporting CSV under OS/360 in 1972. In 1978 list-directed input/output was supported by FORTRAN 77 using commas and spaces for delimiters. CSV was standardized with RFC4180 as a MIME Content-Type in 2005, and eight years later some of RFC 4180's deficiencies were tackled by a W3C. In 2015 this organization made the first drafts of recommendations for CSV-metadata standards to enhance CSV format.

Basic rules of CSV file format

There is a list of rules and limitations that you need to take into account to deal with CSV files.

First of all, A CSV file does not require a specific character encoding, byte order, or line terminator format, however not all software support all line-end variations. Line terminators can also be embedded as data within fields.

Second and really important, all records should have the same number of fields, moreover, they should be in the same order. Data within fields is regarded as a sequence of characters, not bits or bytes.

Third, any field may be quoted. Fields with embedded commas or double-quote characters must be quoted always. Fields with embedded line breaks must be quoted as well.

In some CSVs, leading and trailing spaces and tabs may be ignored, so fields with such spaces as meaningful data must be quoted. Yet such trimming is forbidden now.

Finally, the first record in CSV may serve as a header with column names in each of the fields.

Core benefits of CSV files

Over the last few decades, CSV files have been widely adopted by many users, since it is easy to use the file type on any desktop device and any operating system. Furthermore, CSVs are easy to organize and edit. You can edit and create CSV files in Microsoft Excel, OpenOffice, or Google Sheets in a matter of seconds.

Last but not least, that file type is in favor of many different business software programs, and it is mainly utilized for importing user data or outputting it for reports.

Common use cases for importing CSV files

Do you wonder why a CSV file is so useful?

That is because such files proved the easiest way of supplying databases with new records or importing client details in bulk (into CRM) in the professional world. Internet shops can use CSV to upload and update information about their inventory.

Common use cases for exporting CSV files

As I have mentioned before, a variety of business software programs use CSV when it comes to exporting spreadsheet data, because that format is well-recognized and widely used. For instance, the majority of office applications, along with PowerShell and Python, use CSV to output records

Types of software that use the CSV file format

Both spreadsheets and database management systems support CSV file format.

A digital database built on the relational model of data exports and imports CSV by the COPY command when using standard SQL. Some of those databases offer a foreign-data wrapper to configure any variant of CSV.

Many utility programs on Unix-style systems can split files on a comma delimiter and then process simple CSV files.

The MACroS editor can operate on CSV files using CSV-nav mode as well.

Now, let's review how to take advantage of CSV files in practice.

Interesting fact: each software that works with CSV has its limits on the maximum number of rows the files can have. For instance, Microsoft Excel and OpenOffice support 1,048,576 rows only, while Google Sheets can process up to 5 million cells (the sum of columns and rows). Nonetheless, text editors have no row or cell limit.

How to View a CSV File

Any CSV file can be opened and viewed in a text editor or a spreadsheet program.

Use Notepad in Windows or TextEdit in MacOS to view the contents of your CSV file. Just run that application, click on "File" menu, choose "Open", and select the .csv file.

These standard software might encounter some difficulties in opening very large CSV files. For such cases, you can employ professional plain text editors, such as Notepad++.

You can view CSV files on a desktop or through online spreadsheet programs, such as Google Sheets. Just log in to your Google Drive and upload the file in .csv. It is much easier to view and evaluate your data in a CSV file in a spreadsheet format.

How to Open and Edit a CSV File

You can secure any PDF file containing sensitive information by encrypting it with passwords and applying permissions, such as preventing its printing, copying its text, adding comments, inserting pages, and other things. Such an application as Adobe Acrobat can be used to add a password to your PDF file with a few clicks via Protect tools. Alternatively, you can utilize a third-party PDF editing solution or take advantage of a free PDF encryption service available on the web.

What are alternatives to PDF?

Excel might be the best spreadsheet application for opening and editing CSV files, but such a program is not free and needs to be installed on your computer. As an alternative, you may leverage a free office suite like OpenOffice or LibreOffice to open files with a .csv extension.

Apple Number, Excel, OpenOffice, and LibreOffice Calc are suitable for editing data in CSV files on a desktop as well. To edit that file type, open it with the application and click on the cell you need to edit to change its content.

CSV files can be edited online for free with Google Sheets or other suitable services.

How to Create a CSV file

Want to make a CSV file? It's easy.

Open up MS Excel or Apple Numbers and save a brand new file as a CSV. You are free to edit it and continue to save it as a CSV whenever you want. Start with creating the row with headings for your columns. Then fill each column with data.

Tips for creating CSV files While CSV document are fairly straightforward, there will occasionally be times where there is an issue. Knowing what could be causing the issue will help you quickly and easily resolve the problem.

How to Import a CSV File

CSV files can be imported into applications that support that type of data.

To import a CSV file into an application, click on "Import" button and select the file in .csv to upload. Next, you should specify its format (for instance, it can be Google CSV or Outlook CSV for importing contacts), then match columns to the appropriate fields used by the application. The uploading process should go smoothly, however, some errors may occur. If you encounter any difficulties, try to make edits to the data to make it suitable for your application and import it again.

Remember, CSV files can only save one page. If you are trying to convert an excel document that has multiple tabs, saving the document as a CSV file will only save the tab that you currently have open. Each tab will need to be saved individually in order to preserve your information.

Alternatives to CSV

Other file formats can be read, understood by spreadsheet programs, and used to download records of plain text.

The most popular alternatives to comma-separated values files are JSON and XLS and XLSX. Each of them comes with its advantages and disadvantages.

JSON - JavaScript Object Notation is easy for humans to read and write. Yet, it is less compact as compared to the CSV file.

In case you want to know if a CSV file is an Excel file (.xls, xlsx), the difference between CSV and XLS or XLSX file formats is that CSV format is a plain text format in which values are separated by commas (Comma Separated Values), while XLS or XLSX file format is an Excel Sheets binary file format which holds information about all the worksheets in a file, including both content and formatting.

Most database programs can export data as CSV and the exported CSV file can then be imported by the spreadsheet program. Specification Edit RFC 4180 proposes a specification for the CSV format

Key Takeaways

CSV files could be defined as the most convenient way of importing and exporting data, as well as sharing it across several software applications or services. So, when you need to extract information from one application and input it into another one, CSV must be on top of your list.

Implementors should "be conservative in what you do, be liberal in what you accept from others" when processing CSV files. Always Wrapped Fields may always be wrapped with double quotes, they should be parsed and discarded by the reading applications.

Usually, the most common way is to read CSV files from Excel, since the program (although not in the older versions) automatically identifies the separators and forms the table without having to do anything else.