CSV: Comma-Separated Values

The universal data exchange format.

Data EngineeringCSVDataImport

Understand the minimalist simplicity of CSV files and why they are the backbone of data engineering and automation.

DATA FORMATS: 01

What is a CSV File?

A CSV file is just a simple text file that holds data in a grid. It is the easiest way to move information like names, prices, or dates from one program to another.

What does it stand for?

Comma

Separated

Values

The name tells you exactly how it works. Every piece of data is a value. Every value is separated by a comma. It is that simple. Because there is no hidden code or complex styling, almost every computer program in the world can read it.

How a CSV Looks: Text vs. Table

This is what happens when you open the exact same CSV file in two different ways.

Opened in Notepad (Raw Text)

Name,Age,City
John,25,New York
Sarah,30,London
Mike,22,Tokyo

Opened in Excel (Table View)

Name	Age	City
John	25	New York
Sarah	30	London
Mike	22	Tokyo

The 4 Golden Rules of CSVs

1. One Line = One Row

Every time you press "Enter" to start a new line of text, the spreadsheet knows to start a new row. You do not need any special code to make rows.

2. Commas Split Columns

Every time you type a comma, the spreadsheet knows to move the next piece of text into the next column to the right.

3. The Top Row is the Header

Most of the time, the very first line of text is used for the names of the columns (like "Name", "Price", or "Date").

4. No Colors or Math

A CSV file only holds text. It cannot save your bold fonts, yellow background colors, or math formulas. It is purely raw data.

The "Comma Problem" (And How to Fix It)

If commas are used to split columns, what happens if your data actually has a comma in it? For example, what if a city is named Paris, Texas? If you just type a comma, the computer will break "Paris" and "Texas" into two different columns!

The Solution: Use Quotes

If you put quote marks around a piece of text, the computer knows to ignore any commas inside those quotes.

Wrong (Creates 4 columns): John, 25, Paris, Texas

Right (Creates 3 columns): John, 25, "Paris, Texas"

CSV vs. Excel (XLSX)

People often confuse CSV files with Excel files because Excel opens both of them. Here is the difference.

CSV (.csv)

Just raw text and data.
Very small file size. Loads instantly.
Can be opened by almost any app ever made.
Cannot save colors, graphs, or math formulas.

Excel (.xlsx)

A complex workbook.
Can save bold text, colors, charts, and math.
Can have multiple sheets (tabs) at the bottom.
Only opens in spreadsheet software. Larger file size.

It is not always a comma.

Sometimes, a comma is a bad choice for splitting text. If your data has a lot of natural commas (like a list of addresses), programmers might use a different symbol to separate the columns. This symbol is called a Delimiter.

;

Semicolon

Common in Europe.

Pipe

Great for complex text.

[TAB]

Tab Space

Known as a TSV file.

Where are CSVs used in real life?

Online Stores

If you own a store on Shopify or Amazon, you use a CSV to upload hundreds of products, prices, and descriptions all at once.

Banking

When you download your monthly bank statement to see where you spent money, your bank usually gives it to you as a CSV file.

Email Contacts

When you move your contacts from Google to Apple, or upload an email list to a newsletter app, you are moving a CSV file.

How to make a CSV from scratch

You do not need fancy software to make a CSV. You can make one right now using the most basic text tools on your computer.

1 Open Notepad (Windows) or TextEdit (Mac).
2 Type your column names on the first line, separated by commas (e.g., Item,Price,Stock).
3 Press "Enter" and type your data for row one.
4 Click File > Save As, name your file inventory.csv, and save it!

// What you type in Notepad: Item,Price,Stock
Apple,1.00,50
Banana,0.50,100
Orange,0.75,20

How to Export a CSV from a Spreadsheet

If you are already working in Excel or Google Sheets, turning your table into a CSV file takes exactly two seconds.

In Google Sheets

File > Download > Comma Separated Values (.csv)

In Microsoft Excel

File > Save As > Choose "CSV (Comma delimited)"

How to open a CSV (The Right Way)

If you just double-click a CSV, it usually opens in Excel. This is fine most of the time. But if Excel breaks your data, do this instead:

1 Open a blank Google Sheet.
2 Click File > Import.
3 Upload your CSV file.
4 Google will perfectly place all your text into neat columns.

What happens if data is missing?

Sometimes, a spreadsheet cell is completely blank. In a CSV file, a blank space is recorded by putting two commas right next to each other. The computer reads the first comma, sees nothing, reads the next comma, and correctly assumes that column is empty.

With a middle name: John,Robert,Smith Without a middle name: John,,Smith

The "Multi-Line" Problem

Rule #1 said that pressing "Enter" starts a new row. But what if one of your columns contains a long physical address that spans multiple lines? How do you press "Enter" without breaking the CSV?

The Solution: Quotes strike again.

Just like the comma problem, wrapping the entire address in quote marks tells the computer to ignore any "Enter" keys inside the quotes.

Name,Address,Phone
Bob,"123 Main St
Apt 4B
New York",555-0100

How big can a CSV be?

There is no limit to how big a CSV file can be. Because it is just plain text, it can hold 10 rows or 100 million rows.

Excel's Limit

1,048,576 rows

Excel will crash or cut off your data if you open a file larger than this.

CSV's Limit

Infinite (∞) rows

Limited only by your computer's hard drive space.

Are CSV files safe?

Yes. A CSV file is one of the safest file types you can download. It is 100% plain text. It cannot run code, it cannot install a virus, and it cannot execute malicious macros.

The only warning: While the CSV itself is safe, if you open it in Excel, Excel might try to run a math formula if a piece of text starts with an equals sign (=). If you see weird code starting with = in a CSV, do not let Excel calculate it.

CSV vs. JSON

If you talk to programmers, they often debate using CSV or JSON (JavaScript Object Notation). Both are just text files used to move data. Here is how they compare:

CSV (Flat Table)

Great for simple grids. Bad for nested data (like a list inside a list).

Name,Age,Pets
John,25,Dog

JSON (Nested Tree)

Great for complex data. Harder for normal people to read.

{
  "Name": "John",
  "Age": 25,
  "Pets": ["Dog"]
}

Programmers love CSVs

Because CSVs are just text, it is incredibly easy to write code that reads them. A programmer can write a script that opens a CSV with 500,000 rows, does math on every row, and saves a new file—in about three seconds.

// Example Python Code to read a CSV:

import csv

with open('data.csv') as file:
    reader = csv.reader(file)
    for row in reader:
        print(row)

Common Mistakes & Bugs

Bug #1

Missing Zeros

Problem: You type a zip code like "01234", but when you open the CSV, it just says "1234".

Fix: Excel thinks "01234" is a math number and removes the zero. Import the file instead of double-clicking it, and tell the program that column is "Text", not "Numbers".

Bug #2

Fix: This means the file was saved with the wrong alphabet code. Always save your CSVs using "UTF-8" encoding. It supports almost every language and symbol.

The Professional Checklist

Before you send a CSV file to your boss or upload it to an important database, check these four things:

Always use a Header Row. Never leave the top row blank. Name every column clearly (e.g., "First_Name", not just "Name1").
Format dates the right way. Use the standard format: YYYY-MM-DD (e.g., 2024-12-31). It prevents confusion between American and European computers.
Remove totally empty rows. If you have blank lines at the bottom of your file, computers might think they are broken pieces of data.
Don't use spaces in column names. Instead of "First Name", write "First_Name". It makes it easier for databases to read.

A Quick History Lesson

The concept of separating values with commas actually predates personal computers. It was first used in 1972 by a compiler program built by IBM. That means the CSV format is older than Excel, older than Apple, and older than the World Wide Web.

Why CSV Will Never Die

Software comes and goes. Databases change. But plain text lasts forever. As long as we need to move data from Point A to Point B, the humble CSV file will be there to do the heavy lifting.

CSV: Comma-Separated Values

CSV (.csv)

Excel (.xlsx)

Missing Zeros

Weird Symbols (Ã©)

More in this series

Data Engineering

Spreadsheets

Presentations

Word Processing

Digital Publishing

Software Engineering

Markdown & Text