Data exchange

What is a CSV file: A comprehensive guide

Structured data plays a critical role in data management and information exchange, and the CSV file format is one of the simplest formats for representing structured data. Whether you're a data analyst or software developer, or you work with data in any capacity, understanding what the CSV file format is, when it’s useful and how it works is a fundamental skill.

A CSV is a commonly used file extension for spreadsheets. Even software programs that don't look and feel like a spreadsheet application frequently offer a CSV file type as an output file for downloading a dataset (like a contact list, a supply list or a report of results or actions). Why? Because CSVs are incredibly easy to edit and share!

In this post, we dive into what (exactly) a CSV file type is, how they're used in different software applications and how to create, edit, export and import them.

Table of contents

What is a CSV file?

“CSV” refers to the CSV file extension, meaning any file that ends in ".csv." The .csv file extension stands for "comma-separated value” file, and it's one of the most common outputs for any spreadsheet program. 

A comma-separated value file refers to a file where the data is input as data separated by commas. A spreadsheet application converts those comma-separated pieces of data into cells in tables to make them easier to read and edit. 

In Windows, you can click on any file to see a preview of it in the file explorer folder on the right. If you click on an Excel CSV file, you can actually see what it looks like behind the scenes: A mess of data separated by commas. Thank goodness for programs like Microsoft Excel and Google Sheets, which make this data much more manageable than just a block of text with a ton of commas!

The types of software that use the CSV file format

Many types of software use the CSV file format. The two most common ways a CSV file would be used are inputting and outputting. On the input side, a CSV file will be imported, bringing data in to be utilized within a software program. As for outputting, a CSV is a standard output file format because software users can easily edit and share CSV file reports or datasets. 

Let's look at some examples to better understand how software utilizes CSV files. 

Common use cases for importing CSV files

One of the most common reasons to import a CSV file in the business world is to upload a list of customer contacts. You might do this when onboarding data to a new email marketing software like Active Campaign.

Active Campaign CSV upload

Another type of software that could require you to upload a list of customer contacts would be a CRM like Hubspot or Nutshell. Referral marketing software like GrowSurf could also require you to upload customer lists to add customers to your referral program so they can generate a unique referral link to share your business with their friends.

There are, of course, numerous other datasets you might want to upload beyond customer lists. For example, if you're an ecommerce company, you might need to upload a list of your product SKUs to an inventory management system or order fulfillment software.

Common use cases for exporting CSV files

Not only do many software programs input CSVs, but they also output them. A CSV file format is a standard exporting format because it's well-recognized and widely used

Its simplicity has led to its adoption by so many programs that few software developers want to stray from CSVs with a different file format when exporting spreadsheet data. Some programs will give you the option of exporting CSV or PDF, such as Gusto's custom report builder, but when it comes to spreadsheet data, a CSV export is the most common file format.

Gusto custom report builder

Key benefits of CSV files

Why use CSV files? Let's take a look at the most important benefits:

  • Widely adopted: People within your organization will likely be accustomed to using CSV files. Most importantly, this file type is not unique to Macs or PCs but can be used by any desktop device and operating system. 

  • Easy to organize and edit: CSV files are editable, and the changes are not locked unless, of course, a user locks a specific set of cells from editing. The good news is that, unlike PDF downloads of spreadsheet data, CSVs can be changed later. 

  • Utilized by many business software programs: Many enterprise software programs rely on CSV imports for onboarding user data. At the same time, plenty of programs have CSVs as their primary output for reports.

  • Used by major spreadsheet applications: When you use CSV files, you can create and edit them in any major spreadsheet application like Microsoft Excel or Google Sheets.

How to open and edit a CSV file

Excel is the most commonly used spreadsheet application for opening and editing CSV files. You can download a CSV file from many software programs. If you have Excel installed on your computer, your computer will automatically default to it as your spreadsheet application. So, after downloading the CSV file, just double-click to open it in Excel. If you already have it in a file folder, you can simply double-click to open it. 

To edit any piece of data, just click once on the cell that contains the data. You can edit the cell content directly in that cell or in the area called fx, which stands for "Excel Functions." In this area, you can write standard characters and text or any functions and formulas you want to use.

How to create a CSV file

Want to create a brand new CSV file? Here's how:

Open up Microsoft Excel and save a new file as a CSV. You can then edit it, and it will continue to save as a CSV whenever you click save. 

The most common way to get started is to create the headings for your columns. Let's say you want to create a contact information list for the people on your team. You can make one column for first names, another for last names, another for phone numbers, and another for email addresses.

How to import a CSV file

Thousands of software programs utilize CSV files. The CSV import process will differ slightly for each one, but due to the consumerization of enterprise tech, even business programs should have easy-to-use CSV import features. 

Most CSV importers follow this process:

  • Select the file you want to upload: From your computer, select the correct CSV file you want to import to your software program. 

  • Match the columns to the appropriate fields: The next step is to match the headings in your CSV columns to the data fields used and understood by your software. This is a key step towards making sure your imported CSV data is usable. 

  • Address any errors: A quality CSV importer should be able to explain if anything goes wrong clearly. This way, you can edit the data and try the import again. Without details on the CSV import issues, it's tough for users to know how to fix them. Unfortunately, many software programs lack quality CSV importers who provide troubleshooting

Beyond the CSV: Other file formats used for spreadsheets

Besides CSV files, there are a few other file formats that can be read and understood by spreadsheet programs or can be used to download data sets from a spreadsheet program. 

When working in Google Sheets, you can download the file in several different formats besides CSVs:

  • Microsoft Excel (.xlsx)

  • OpenDocument format (.ods)

  • PDF document (.pdf)

  • Web page (.html)

  • Tab-separated values (.tsv)

Microsoft Excel CSV import screenshot

As for Microsoft Excel, there are even more file formats available for saving data from your spreadsheet, including:

  • Excel Workbook (.xlsx)

  • Excel Macro-Enabled Workbook (.xlsm)

  • Excel Binary Workbook (.xlsb)

  • Excel 97-2003 Workbook (.xls)

  • XML Data (.xml)

  • Single File Web Page (.mht or .mhtml)

  • Web Page (.htm or .html)

  • Excel Template (.xltx)

  • Excel Macro-Enabled Template (.xltm)

  • Excel 97-2003 Template (.xlt)

  • Text tab-delimited (.txt)

  • Unicode text (.txt)

  • XML Spreadsheet 2003 (.xml)

  • Microsoft Excel 5.0/95 Workbook (.xls)

  • Formatted Text space-delimited (.prn)

  • Text Macintosh (.txt)

  • Text Ms-DOS (.txt)

  • CSV Macintosh (.csv)

  • CSV MS-DOS (.csv)

  • DIF (.dif)

  • SYLK (.slk)

  • Excel Add-in (.xlam)

  • Excel 97- 2003 Add-in (.xla)

  • PDF Document (.pdf)

  • XPS Document (.xps)

  • Strict Open XML Spreadsheet (.xlsx)

  • OpenDocument Spreadsheet (.ods)

Microsoft CSV file types

CSV files are popular for a reason. They're simple, minimalistic, widely understood and utilized by many different types of software. 

Editor's note: This post was originally published in 2020 and has been updated for comprehensiveness.

The Flatfile Data Exchange Platform

The easiest, fastest, and safest way for developers to build the ideal data file import experience

Get started for free!