CSV Parser Guide: Delimiters, Quotes, and Encodings

CSV (Comma-Separated Values) is the simplest tabular format: each line in the file is a row in the table, and the values within a line are separated by a comma or another character. It is how you receive exports from CRMs and accounting systems, Excel dumps, and reports from ad platforms (Google Ads, Meta Ads) and analytics tools. The format looks trivial, and that is precisely why people get CSV parsing wrong so often. This article is part of our overview of document parsing and is about how to read CSV correctly with a proper csv parser.

Why "split on the comma" is a bug

The temptation to parse CSV by hand — line.split(",") — hits everyone and almost always ends in bugs. The reason is that CSV has escaping: a value containing a comma is wrapped in quotes, and quotes inside a value are doubled.

sku,name,price
101,"Coffee grinder, manual",34.90
102,"Kettle ""Retro""",21.90

Here "Coffee grinder, manual" is a single value, not two, and Kettle "Retro" contains quotes. Any manual split breaks on this. That is why you always use a ready-made CSV reader — it knows about quotes, escaping, and line breaks inside cells.

Delimiters, encodings, and the BOM

Despite the "comma" in the name, the delimiter often turns out to be a semicolon (;) — that is how Excel saves files in locales where the comma is the decimal separator. You also meet tabs (TSV) and the vertical bar. A good parser can detect the delimiter automatically or takes it as a parameter.

The second recurring problem is encoding. Files from older systems often arrive in a legacy encoding such as windows-1252 or Latin-1 (ISO-8859-1) rather than UTF-8. On top of that, files from Excel frequently begin with a BOM (an invisible marker at the start) that makes the first column's name read as sku instead of sku. The cure is to specify the correct encoding when reading (in Python, encoding="utf-8-sig", which strips the BOM for you).

Python

The standard library includes the csv module, which is enough in most cases. The most convenient tool is DictReader — it returns each row as a dictionary keyed by the headers.

import csv

with open("prices.csv", encoding="utf-8-sig", newline="") as f:
    reader = csv.DictReader(f, delimiter=";")
    for row in reader:
        print(row["sku"], row["name"], row["price"])

When the CSV is large or you need to analyze it right away — filter, group, aggregate — reach for pandas. One line reads the file into a table (a DataFrame).

import pandas as pd

df = pd.read_csv("prices.csv", sep=";", encoding="utf-8-sig")
expensive = df[df["price"] > 30]
print(expensive[["sku", "name"]])

pandas is also handy for conversion: the same DataFrame is saved back to CSV, Excel, or JSON with a single command.

JavaScript / Node.js

In the browser and in Node, the most popular parser is Papa Parse: it auto-detects the delimiter, understands headers, and can work with streams. It is the default choice when people search for a nodejs csv parser.

const Papa = require("papaparse");
const fs = require("fs");

const file = fs.readFileSync("prices.csv", "utf-8");
const result = Papa.parse(file, { header: true, skipEmptyLines: true });

for (const row of result.data) {
  console.log(row.sku, row.name, row.price);
}

For server-side pipelines with large files, teams more often use csv-parse — the streaming parser from the Node CSV ecosystem.

PHP

The built-in fgetcsv function already handles quoting, so it is enough for simple jobs.

<?php
$f = fopen("prices.csv", "r");
$headers = fgetcsv($f, 0, ";");

while (($row = fgetcsv($f, 0, ";")) !== false) {
    $record = array_combine($headers, $row);
    echo $record["sku"] . " — " . $record["name"] . "\n";
}
fclose($f);

For more convenient work — filtering, selecting columns, converting encodings — use the league/csv library.

Go

Go's standard library has the encoding/csv package, which covers most jobs, including a non-standard delimiter.

package main

import (
    "encoding/csv"
    "fmt"
    "os"
)

func main() {
    f, _ := os.Open("prices.csv")
    defer f.Close()

    r := csv.NewReader(f)
    r.Comma = ';'
    rows, _ := r.ReadAll()

    for _, row := range rows[1:] { // skip the header
        fmt.Println(row[0], row[1], row[2])
    }
}

If you are scraping data straight from the web rather than reading a local file, the same principles apply — see our Go web scraping guide for fetching and then writing the rows out to CSV.

When CSV stops being enough

CSV is fine as long as the data is flat. The moment you get nesting, several tables in one file, formatting, formulas, or merged cells, it is no longer a CSV job but Excel parsing. And the reverse: if the source Excel file is too complex to read directly, it is often exported to CSV first and processed uniformly. And if your CSV is really an export of events or log lines, look at the approaches in our articles on parsing TXT and parsing logs.

If your exports arrive with "floating" encodings, a different delimiter from one dump to the next, or broken quotes, scraping.pro can normalize them to a single shape and set up stable parsing and data extraction with delivery into whatever system you need — so a malformed CSV never breaks your pipeline again.