CSV stands for Comma Separated Values, a plain-text file format used to store tabular data (numbers and text) where each line is a data record and each record consists of one or more fields separated by commas. This guide explains what CSV files are, how they work, their common uses, and how to handle them effectively.
Formula
The calculator above works out your average pace and speed from a total time and distance. The formulas are:
- Pace = Total time (minutes) ÷ Distance (km)
- Speed = Distance (km) ÷ (Total time (minutes) ÷ 60)
Where:
- t = total time in minutes
- d = distance in kilometers
Pace is expressed in minutes per kilometer (min/km), and speed in kilometers per hour (km/h).
Worked Example
Using the default values: t = 120 minutes, d = 10 km.
- Pace = 120 ÷ 10 = 12.00 min/km
- Speed = 10 ÷ (120 ÷ 60) = 10 ÷ 2 = 5.00 km/h
The calculator shows a pace of 12.00 min/km and a speed of 5.00 km/h.
What Is a CSV File?
A CSV file is a simple, human-readable text file that stores data in a structured way. Each line in the file represents a row of data, and within that line, commas separate the individual fields (columns). For example, a CSV file with three columns (Name, Age, City) might look like:
Name,Age,City Alice,30,New York Bob,25,London
CSV files are widely used because they are easy to create, read, and edit with any text editor, and they can be imported and exported by almost every spreadsheet and database application. The format is not standardized by a single official specification, but the basic rules are simple and widely understood.
How CSV Files Work
The core idea is that a delimiter (usually a comma) marks the boundary between fields. However, because commas can appear inside data (e.g., in addresses or notes), the format includes quoting rules. Fields containing commas, double quotes, or line breaks are enclosed in double quotes. If a field contains a double quote, it is typically escaped by doubling it (“” ). For example:
Name,Notes Alice,"Loves coffee, tea, and cake" Bob,"Said ""hi"" to me"
Most CSV parsers also handle newlines within quoted fields, so a record can span multiple lines. The first line often contains headers that name each column, but this is optional. Some systems use other delimiters like semicolons or tabs (then called TSV), but the term CSV is commonly used for any delimited text file.
How to Choose the Right CSV Format
When creating or exporting a CSV, you need to decide on a few things:
- Delimiter: Comma is standard in many regions, but some locales use semicolons because commas are used as decimal separators. Check what your target application expects.
- Character encoding: UTF-8 is the safest choice for international text. Older software may expect ASCII or Windows-1252.
- Headers: Include a header row if you want to make the file self-describing; many tools assume the first row is headers.
- Quoting: Use double quotes only when necessary (i.e., when a field contains a delimiter, quote, or newline). Some tools quote every field, which is also valid.
- Line endings: Use CRLF (Windows) or LF (Unix) consistently. Most modern applications accept both.
If you are unsure, start with a simple comma-delimited, UTF-8 encoded file with headers and quote only when needed. This works with Excel, Google Sheets, Python, R, and most databases.
Common Problems and How to Avoid Them
CSV files are easy, but they have a few pitfalls:
- Commas in data: Always quote fields that contain commas. For example, “New York, NY” must be quoted.
- Quotes in data: Escape quotes by doubling them. A value like He said “hello” becomes “He said “”hello”””.
- Mismatched delimiters: If you open a CSV with semicolons in Excel, it may show everything in one column. Use the import wizard or specify the delimiter.
- Encoding issues: If you see mojibake (e.g., é instead of é), the file is not in the expected encoding. Save as UTF-8 with BOM for older Excel versions.
- Trailing commas: Some exporters add a comma at the end of each line, which can create an extra empty column. Check your data.
- Line breaks within fields: If a field contains a newline, it must be quoted, or the record will be split incorrectly.
Most issues come from not following the quoting rules. A robust CSV parser will handle these cases, but it is better to generate clean files from the start.
CSV vs. Other Formats
CSV is often compared to other data formats:
- Excel (.xlsx): Binary, proprietary, supports formulas and formatting, but not directly readable by plain text tools.
- JSON: Hierarchical, supports nested data and types, but more verbose and not as easily viewed in a spreadsheet.
- XML: Very flexible and self-describing, but verbose and harder to read by humans.
- Parquet: Columnar and compressed, ideal for big data, but not human-readable.
CSV wins when you need a simple, universal exchange format that any tool can read. It is the lingua franca of tabular data.
How to Open and Edit CSV Files
You can open a CSV file in any text editor (Notepad, TextEdit, VS Code) to see the raw text. For a more user-friendly view, use a spreadsheet application like Microsoft Excel, Google Sheets, or LibreOffice Calc. In Excel, you can double-click a CSV file, but it may not parse it correctly if the delimiter is not the default. Use the Data > From Text/CSV import feature to specify the delimiter and encoding. In Google Sheets, use File > Import and choose the CSV file; it usually auto-detects the delimiter.
When editing, be careful not to introduce stray commas or quotes. If you are working with large files, consider using a dedicated CSV editor or a script (e.g., Python’s csv module) to avoid manual errors.
Frequently Asked Questions
What does CSV stand for?
CSV stands for Comma Separated Values. It describes a file format where data fields are separated by commas, with each line representing a row.
Can I use semicolons instead of commas in CSV?
Yes, many systems, especially in Europe, use semicolons as the delimiter because commas are used as decimal separators. The file is still often called CSV, but technically it is semicolon-separated. When opening such a file, you may need to specify the delimiter.
How do I handle commas inside a CSV field?
Enclose the entire field in double quotes. For example: “Smith, John”. The quotes tell the parser that the comma is part of the data, not a delimiter.
Why does my CSV file show weird characters like é?
This is an encoding mismatch. The file is probably saved in UTF-8, but the program is interpreting it as Latin-1 or Windows-1252. Save the file as UTF-8 with BOM or change the encoding in the import settings.
How do I create a CSV file from Excel?
Go to File > Save As, choose CSV (Comma delimited) (*.csv) as the file type. Note that this will only save the active sheet and may lose formatting, formulas, and multiple sheets.
Is there a size limit for CSV files?
There is no hard limit in the format itself, but applications have limits. Excel can handle up to 1,048,576 rows and 16,384 columns. For larger datasets, use a text editor or a scripting language.