CSV Quoting and Encoding: Prepare Reliable Python Imports

CSV looks simple because rows and columns are visible in a spreadsheet. But a CSV file does not describe types, its delimiter varies by region, and text values can contain commas, line breaks or quote characters. A careful import starts by checking the file's structure before transforming values.

Headers should be clear and unique

The first row normally becomes the field names used by your program. Use concise, unique headers without relying on their column position. A duplicate header can silently overwrite a value when a row is converted to a dictionary.

customer_id,full_name,city
00127,"Ana Silva, Jr.",São Paulo
00128,"Rui Costa",Recife

The comma inside Ana Silva, Jr. does not create a new column because the value is quoted. Without those quotes, the row would have four values while the header defines three.

Confirm delimiter and quoting rules

Some exports use semicolons instead of commas, particularly when comma is the decimal separator in the user's locale. Others use tabs. Open a small sample as plain text and confirm the separator before importing. In Python's csv module, specify the dialect or delimiter explicitly when the source is known.

import csv

with open("customers.csv", encoding="utf-8", newline="") as file:
    rows = csv.DictReader(file)
    for row in rows:
        print(row["customer_id"])

newline="" lets Python's CSV module handle line endings correctly. The encoding matters too: use the actual export encoding, with UTF-8 as a common modern choice rather than an assumption for every source.

Convert types deliberately

CSV values arrive as text. The value 00127 may be an identifier whose leading zeros are meaningful; converting it to an integer would change it. A price, date or boolean needs an explicit conversion rule and a decision about how to handle blank or invalid cells.

Useful import routine: inspect the headers, check a handful of rows, choose the delimiter and encoding, then convert and validate each important field in code.

Keep source data traceable

Retain the original export and record the import assumptions: source system, date, delimiter, encoding and column mapping. That makes a later discrepancy easier to investigate without guessing whether the problem began in the export or the import script.

Next steps

Use the CSV to Python tool to inspect a small, non-sensitive sample. Then read the CSV to Python list guide and the official Python csv module documentation.