Messy CSV: Excel, encodings and Indian formats

A bank’s export lists the reference 400123456789012345, the STD codeThe area code of an Indian landline number, written with its leading 0 and dialled before the subscriber's number, such as 0484. 0484 and the amount 1,23,456.50, and every one of them is valid CSV. Open the file in a spreadsheet and the reference loses its last digits and the code its zero; read it with automatic typing in code and the code loses its zero again while the amount stays text, all without an error.

You will see which values spreadsheets change and why, read the same export with a type declared for every column, parse amounts in Indian digit groupingdigit grouping: The separators a locale puts between groups of digits in a number. English in the United States writes 123,456,789.5; English in India groups by three and then by two, as 12,34,56,789.5. A parser removes them by the documented rule. and day-first dates with parsers that refuse to guess, and check what delimiter and header detection return before you trust it.

A spreadsheet changes four kinds of values when it opens a CSV file

CSV has no types, so a spreadsheet decides what each cell is when it opens the file. Microsoft documents what Excel does by default: it removes leading zeros from numerical text, cuts numerical data to 15 digits of precision and shows it in scientific notation, reads digits around the letter E as scientific notation, and turns some strings of letters and numbers into dates. Its specifications put the precision of a number at 15 digits, and a number with more has every digit after the fifteenth set to zero. This program applies the first three rules to a bank export, the way the documentation describes them, and prints each cell they change:

"""The cells of a bank export that a spreadsheet changes when it opens the file, by the conversions Microsoft documents."""

import csv
import io
import re

EXPORT = (
    "txn_date,narration,ref_no,withdrawal,deposit,std_code,mobile,batch\r\n"
    '04/10/2026,NEFT Asha Traders,400123456789012345,"1,23,456.50",,0484,09876543210,12E4\r\n'
    "13/10/2026,Cash deposit,400123456789012340,,2500.00,0484,09812345678,12E5\r\n"
)


def spreadsheet_value(text):
    """What a spreadsheet keeps of a cell with its default conversions, and why; None when the text stays text."""
    if re.fullmatch(r"\d+", text):
        digits = text.lstrip("0") or "0"
        if len(digits) > 15:
            return digits[:15] + "0" * (len(digits) - 15), "15 digits kept, the rest become 0"
        if digits != text:
            return digits, "leading zeros removed"
        return None
    if re.fullmatch(r"\d+E\d+", text):
        mantissa, power = text.split("E")
        return str(int(mantissa) * 10 ** int(power)), "digits around E read as a power of ten"
    return None


kept = {}
for row in csv.DictReader(io.StringIO(EXPORT, newline="")):
    for column, text in row.items():
        changed = spreadsheet_value(text)
        if changed:
            value, why = changed
            print(f"{column:<8} {text:<20} -> {value:<20} {why}")
            kept.setdefault(column, []).append(value)

refs = kept["ref_no"]
print("two references, one number:", refs[0] == refs[1], refs[0])

ref_no   400123456789012345   -> 400123456789012000   15 digits kept, the rest become 0
std_code 0484                 -> 484                  leading zeros removed
mobile   09876543210          -> 9876543210           leading zeros removed
batch    12E4                 -> 120000               digits around E read as a power of ten
ref_no   400123456789012340   -> 400123456789012000   15 digits kept, the rest become 0
std_code 0484                 -> 484                  leading zeros removed
mobile   09812345678          -> 9812345678           leading zeros removed
batch    12E5                 -> 1200000              digits around E read as a power of ten
two references, one number: True 400123456789012000

Under those three rules, four of the eight columns change. The references lose their last three digits, and the two different references 400123456789012345 and 400123456789012340 become the same number. The STD code and the mobile number lose their leading zero, and the batch codes 12E4 and 12E5 become 120,000 and 1,200,000. Once a cell holds the changed value, the original digits are gone from the workbook and from every file saved from it.

The cure is to keep such columns text from the moment the file is opened. Microsoft’s own advice for long numbers is a text format, set before the values arrive, or an import that sets the column’s type to Text; a format applied after the values are in the cells does not bring the digits back.

Automatic typing loses data in code too, so declare each column’s type

Spreadsheets are not the only guessers. Many CSV readers offer type inferencetype inference: Guessing a column's type from the text of its values, as the automatic typing of spreadsheets and CSV libraries does. It changes values without an error: 0484 becomes 484, and a long reference loses digits.: Papa Parse converts numeric and boolean text when dynamicTyping is on, though it leaves numbers beyond 2^53 as text to keep their digits, and csv-parse tries to convert every field with cast: true. At heart the guess is the same everywhere: if the text parses as a number, it becomes one. This program reads the export three ways, as text, with that guess, and with a type declared for every column; the JavaScript tab makes the same guess with Number():

"""One bank export read three ways: as text, with guessed types, and with a type declared for every column."""

import csv
import io
from datetime import datetime

EXPORT = (
    "txn_date,narration,ref_no,withdrawal,deposit,std_code,mobile\r\n"
    '04/10/2026,NEFT Asha Traders,400123456789012345,"1,23,456.50",,0484,09876543210\r\n'
    "13/10/2026,Cash deposit,400123456789012340,,2500.00,0484,09812345678\r\n"
)


def guess(text):
    """Automatic typing: the first of int and float that accepts the text, else the text itself."""
    for convert in (int, float):
        try:
            return convert(text)
        except ValueError:
            pass
    return text


def paise(text):
    """An amount such as 1,23,456.50 in whole paise: grouping commas removed on purpose, empty means no amount."""
    if not text:
        return None
    rupees, _, fraction = text.replace(",", "").partition(".")
    return int(rupees) * 100 + int(fraction.ljust(2, "0"))


DECLARED = {
    "txn_date": lambda text: datetime.strptime(text, "%d/%m/%Y").date().isoformat(),
    "narration": str,
    "ref_no": str,
    "withdrawal": paise,
    "deposit": paise,
    "std_code": str,
    "mobile": str,
}

rows = list(csv.DictReader(io.StringIO(EXPORT, newline="")))
for n, row in enumerate(rows, start=1):
    print(f"row {n:<9} {'text':<21} {'guessed':<21} declared")
    for column, text in row.items():
        print(f"  {column:<10} {text!r:<21} {guess(text)!r:<21} {DECLARED[column](text)!r}")

row 1         text                  guessed               declared
  txn_date   '04/10/2026'          '04/10/2026'          '2026-10-04'
  narration  'NEFT Asha Traders'   'NEFT Asha Traders'   'NEFT Asha Traders'
  ref_no     '400123456789012345'  400123456789012345    '400123456789012345'
  withdrawal '1,23,456.50'         '1,23,456.50'         12345650
  deposit    ''                    ''                    None
  std_code   '0484'                484                   '0484'
  mobile     '09876543210'         9876543210            '09876543210'
row 2         text                  guessed               declared
  txn_date   '13/10/2026'          '13/10/2026'          '2026-10-13'
  narration  'Cash deposit'        'Cash deposit'        'Cash deposit'
  ref_no     '400123456789012340'  400123456789012340    '400123456789012340'
  withdrawal ''                    ''                    None
  deposit    '2500.00'             2500.0                250000
  std_code   '0484'                484                   '0484'
  mobile     '09812345678'         9812345678            '09812345678'

// The same export typed the way many converters do it in JavaScript: Number() on every field that is not text.
const rows = [
  { ref_no: "400123456789012345", withdrawal: "1,23,456.50", deposit: "", std_code: "0484", mobile: "09876543210" },
  { ref_no: "400123456789012340", withdrawal: "", deposit: "2500.00", std_code: "0484", mobile: "09812345678" },
];

const guess = (text) => (Number.isNaN(Number(text)) ? text : Number(text));

rows.forEach((row, n) => {
  console.log(`row ${n + 1}`);
  for (const [column, text] of Object.entries(row)) {
    console.log(`  ${column.padEnd(10)} text ${JSON.stringify(text).padEnd(22)} guessed ${JSON.stringify(guess(text))}`);
  }
});
const [a, b] = rows.map((row) => guess(row.ref_no));
console.log("two references, one number:", a === b, Number.isSafeInteger(a), Number.MAX_SAFE_INTEGER);
console.log('Number("0x1A") is', Number("0x1A"), 'and Number("  ") is', Number("  "));

row 1
  ref_no     text "400123456789012345"   guessed 400123456789012350
  withdrawal text "1,23,456.50"          guessed "1,23,456.50"
  deposit    text ""                     guessed 0
  std_code   text "0484"                 guessed 484
  mobile     text "09876543210"          guessed 9876543210
row 2
  ref_no     text "400123456789012340"   guessed 400123456789012350
  withdrawal text ""                     guessed 0
  deposit    text "2500.00"              guessed 2500
  std_code   text "0484"                 guessed 484
  mobile     text "09812345678"          guessed 9812345678
two references, one number: true false 9007199254740991
Number("0x1A") is 26 and Number("  ") is 0

Python’s guess kept the long references exact, because Python’s int has no size limit, but it dropped the zeros of both codes and gave the amounts no type you could rely on: the grouped withdrawal stayed text, and deposit is an empty string in row 1 and the float 2500.0 in row 2. JavaScript did worse. Number("400123456789012345") is above the largest safe integersafe integer: An integer that binary64 holds exactly and shares with no other integer: from -(2^53 - 1) to 2^53 - 1, which is 9007199254740991. Number.isSafeInteger tests for it. More in “Numbers: precision, big integers and decimals”, 9,007,199,254,740,991, so both references became 400123456789012350 and compare equal. An empty withdrawal became 0, which looks like a real amount, and Number() also accepts 0x1A as 26 and blank text as 0, as the ECMAScript conversion rules allow.

The declared column of the output is what a program should hold. Each column has one converter, chosen once for the whole file: references, codes and phone numbers stay text, amounts become whole paise from their text, an empty amount becomes None rather than 0, and the date is read as day first because the bank’s documentation says so.

The converters belong to the file, not to each program that reads it: write them down once, with the sender’s description, and every reader gets the same values.

Indian amounts and day-first dates need parsers that refuse to guess

The amount 1,23,456.50 is grouped by locale, and so is the date 04/10/2026, and a parser has to know which locale wrote them. Digit grouping differs by locale: the Unicode CLDR pattern for English in India is #,##,##0.###, three digits at the right and then groups of two, while English in the United States groups by threes. Node’s Intl prints the same number and the same day for both:

// The same number and the same day written by Intl for two locales: what an export from each looks like.
const amount = 123456789.5;
const day = new Date(Date.UTC(2026, 9, 4));

for (const locale of ["en-IN", "en-US"]) {
  const number = new Intl.NumberFormat(locale).format(amount);
  const date = new Intl.DateTimeFormat(locale, { timeZone: "UTC" }).format(day);
  console.log(`${locale}: ${number.padEnd(15)} ${date}`);
}

en-IN: 12,34,56,789.5  4/10/2026
en-US: 123,456,789.5   10/4/2026

So an amount parser checks the form before it converts anything, and it stops at the first thing it did not expect. Step through four amounts:

The program behind the figure has two strict parsers, parse_inr_amount() and parse_ddmmyyyy(), and a third function that guesses, trying month first and then day first:

"""Strict parsers for amounts in Indian digit grouping and for day-first dates, and what guessing per row does."""

import re
from datetime import datetime

GROUPED = re.compile(r"(\d{1,2}(?:,\d{2})*,\d{3}|\d{1,3})(?:\.(\d{2}))?")
PLAIN = re.compile(r"(\d+)(?:\.(\d{2}))?")


def parse_inr_amount(text):
    """Whole paise from 1,23,456.50 or 123456.50, with an optional rupee sign; anything else is refused."""
    t = text.strip().removeprefix("₹").strip()
    m = GROUPED.fullmatch(t) or PLAIN.fullmatch(t)
    if not m:
        raise ValueError(f"not an amount in Indian grouping: {text!r}")
    return int(m.group(1).replace(",", "")) * 100 + int(m.group(2) or 0)


def parse_ddmmyyyy(text):
    """A date written DD/MM/YYYY, day first, as the bank's documentation says; anything else is refused."""
    if not re.fullmatch(r"\d{2}/\d{2}/\d{4}", text):
        raise ValueError(f"not a DD/MM/YYYY date: {text!r}")
    return datetime.strptime(text, "%d/%m/%Y").date()


def guess_date(text):
    """What a parser that guesses does: month first, and day first only when month first fails."""
    for pattern in ("%m/%d/%Y", "%d/%m/%Y"):
        try:
            return datetime.strptime(text, pattern).date()
        except ValueError:
            pass
    raise ValueError(f"no date: {text!r}")


for text in ["1,23,456.50", "₹ 12,34,56,789.50", "2500.00", "456", "123,456.50", "1,2,3", "12.5"]:
    try:
        print(f"{text!r:<21} {parse_inr_amount(text)} paise")
    except ValueError as e:
        print(f"{text!r:<21} refused: {e}")

print(f"{'date':<12} {'day first':<12} guessed")
for text in ["04/10/2026", "13/10/2026", "31/10/2026", "01/11/2026"]:
    print(f"{text:<12} {parse_ddmmyyyy(text).isoformat():<12} {guess_date(text).isoformat()}")
for text in ["2026-10-04", "4/10/2026"]:
    try:
        parse_ddmmyyyy(text)
    except ValueError as e:
        print("refused:", e)

'1,23,456.50'         12345650 paise
'₹ 12,34,56,789.50'   12345678950 paise
'2500.00'             250000 paise
'456'                 45600 paise
'123,456.50'          refused: not an amount in Indian grouping: '123,456.50'
'1,2,3'               refused: not an amount in Indian grouping: '1,2,3'
'12.5'                refused: not an amount in Indian grouping: '12.5'
date         day first    guessed
04/10/2026   2026-10-04   2026-04-10
13/10/2026   2026-10-13   2026-10-13
31/10/2026   2026-10-31   2026-10-31
01/11/2026   2026-11-01   2026-01-11
refused: not a DD/MM/YYYY date: '2026-10-04'
refused: not a DD/MM/YYYY date: '4/10/2026'

The strict parsers accepted every amount in Indian grouping or none, and refused 123,456.50, 1,2,3 and 12.5 with the cell in the message. The dates show why guessing is worse than refusing: the guesser read 13/10/2026 and 31/10/2026 correctly, because no month 13 or 31 exists, but it read 04/10/2026 as 2026-04-10 and 01/11/2026 as 2026-01-11. One column now holds dates in two orders, and nothing in the result says which rows are wrong. The strict parser reads every date of the column one way, and refuses 2026-10-04 and 4/10/2026 because neither is the documented form.

Delimiter and header detection are guesses, so check what they return

When nobody tells the reader the dialect, it can guess it from a sample of the text, which is called sniffingsniffing (CSV): Guessing a CSV file's delimiter, and whether it has a header, from a sample of its text, as Python's csv.Sniffer does. It is a heuristic that can pick a wrong character, so its answer is checked against the header and the rows.. Python’s csv.Sniffer returns a dialect from a sample, and its has_header() guesses whether the first row is a header: the documentation calls it a rough heuristic that gives false positives and false negatives. Step through how a careful reader uses those guesses:

The program runs Sniffer on the three samples of the figure and has_header() on two small files, then reads the price list with the delimiter it was told and checks the header and every row:

"""Python's csv.Sniffer on three samples and has_header on two, then a reader that is told the dialect and checks it."""

import csv
import io

SAMPLES = {
    "semicolons, with a header": "item;price;qty\r\nPen;12,50;3\r\nPad;7,25;10\r\n",
    "semicolons and decimal commas, no header": "Pen;12,50;3\r\nPad;7,25;10\r\nInk;3,10;2\r\n",
    "a note, not a table": "Thank you for banking with us.\r\nPlease keep this statement safe.\r\n",
}
sniffer = csv.Sniffer()
for name, sample in SAMPLES.items():
    try:
        print(f"{name}: delimiter {sniffer.sniff(sample).delimiter!r}")
    except csv.Error as e:
        print(f"{name}: csv.Error: {e}")
print("has_header, id,name / 1,Asha / 2,Ravi:    ", sniffer.has_header("id,name\r\n1,Asha\r\n2,Ravi\r\n"))
print("has_header, id,name / 1,Asha / 2,Ravindra:", sniffer.has_header("id,name\r\n1,Asha\r\n2,Ravindra\r\n"))


def read_declared(text, delimiter, header):
    """The rows of `text`, read with the delimiter the sender documented, after checking the header and every row."""
    reader = csv.reader(io.StringIO(text, newline=""), delimiter=delimiter)
    found = next(reader)
    if found != header:
        raise ValueError(f"line 1: expected the header {header}, found {found}")
    rows = []
    for fields in reader:
        if len(fields) != len(header):
            raise ValueError(f"line {reader.line_num}: {len(fields)} fields, the header has {len(header)}")
        rows.append(fields)
    return rows


PRICES = SAMPLES["semicolons, with a header"]
print("told ';':", read_declared(PRICES, ";", ["item", "price", "qty"]))
try:
    read_declared(PRICES, ",", ["item", "price", "qty"])
except ValueError as e:
    print("told ',':", e)

data = "id,branch\r\n1,Café Pune – main\r\n".encode("cp1252")
try:
    data.decode("utf-8")
except UnicodeDecodeError as e:
    print(f"as UTF-8: {e.reason} at byte {e.start}, {data[e.start]:#04x}")
print("as the documented cp1252:", data.decode("cp1252").splitlines()[1])

semicolons, with a header: delimiter ';'
semicolons and decimal commas, no header: delimiter ','
a note, not a table: delimiter 'f'
has_header, id,name / 1,Asha / 2,Ravi:     False
has_header, id,name / 1,Asha / 2,Ravindra: True
told ';': [['Pen', '12,50', '3'], ['Pad', '7,25', '10']]
told ',': line 1: expected the header ['item', 'price', 'qty'], found ['item;price;qty']
as UTF-8: invalid continuation byte at byte 16, 0xe9
as the documented cp1252: 1,Café Pune – main

Sniffer was right about the price list with a header. Without the header, it chose the comma, the decimal separator of every price. Given a note that is not a table at all, it still answered, with the letter f. has_header() said False for id,name / 1,Asha / 2,Ravi, which has a header: Asha and Ravi are as long as the word name, so only one of the two columns looks like it has a header, and one of two is not a majority. Change Ravi to Ravindra and it says True. Told ;, the reader returned both rows; told ,, its header check stopped at line 1, which is where a wrong guess should stop.

So take the delimiter, the header and the encoding from the sender’s documentation and pass them explicitly. When you must guess, limit the candidates and accept the guess only after the header and every row fit.

The encoding is a guess too. The last lines of the output decode a branch name written in Windows-1252: as UTF-8 it fails at byte 16, 0xe9, the é, and decoded with the encoding the sender documented it reads Café Pune – main. Decode strictly, with utf-8-sig for files that may start with a byte-order mark, and switch to a legacy encoding only when the sender says so. errors="ignore" or "replace" would read on and change the text instead.

Practise: clean a bank statement export

The exercise puts the lesson together: it decodes an export with a byte-order mark, keeps the narration in any script and the reference as text, flags a reference a spreadsheet has damaged, and turns day-first dates and Indian amounts into ISO dates and whole paise, refusing every other form with its line:

import csv
import io


def clean_statement(data):
    """One typed dict per transaction of a bank statement export (bytes)."""
    return list(csv.DictReader(io.StringIO(data.decode("utf-8"))))

import codecs

from statement import clean_statement

HEADER = "Txn Date,Narration,Ref No,Withdrawal,Deposit,Balance\r\n"


def export(*lines, bom=True):
    """The bytes of an export with the given records, as the bank writes it."""
    text = HEADER + "".join(line + "\r\n" for line in lines)
    return (codecs.BOM_UTF8 if bom else b"") + text.encode("utf-8")


def error_of(data):
    """The message of the ValueError clean_statement(data) raises, or None."""
    try:
        clean_statement(data)
    except ValueError as e:
        return str(e)
    return None


def test_typed_records():
    """types the date, the amounts and the balance of each record"""
    data = export(
        '04/10/2026,NEFT Asha Traders,400123456789012345,"1,23,456.50",,"3,76,543.50"',
        "13/10/2026,Cash deposit,400123456789012340,,2500.00,\"3,79,043.50\"",
    )
    assert clean_statement(data) == [
        {"date": "2026-10-04", "narration": "NEFT Asha Traders", "ref": "400123456789012345", "ref_ok": True,
         "amount_paise": -12345650, "balance_paise": 37654350},
        {"date": "2026-10-13", "narration": "Cash deposit", "ref": "400123456789012340", "ref_ok": True,
         "amount_paise": 250000, "balance_paise": 37904350},
    ]


def test_without_bom_and_in_hindi():
    """reads an export without a BOM and keeps a narration in Hindi exactly"""
    records = clean_statement(export('31/10/2026,चाय और नाश्ता,400123456789012399,450.00,,"3,78,593.50"', bom=False))
    assert records[0]["narration"] == "चाय और नाश्ता"
    assert records[0]["amount_paise"] == -45000


def test_damaged_reference():
    """keeps a reference a spreadsheet damaged, and flags it"""
    records = clean_statement(export('01/11/2026,Card refund,4.00123456789012E+17,,99.00,"3,78,692.50"'))
    assert records[0]["ref"] == "4.00123456789012E+17"
    assert records[0]["ref_ok"] is False


def test_western_grouping_refused():
    """refuses an amount grouped in threes, with its line"""
    message = error_of(export("04/10/2026,Rent,400123456789012345,\"123,456.50\",,\"2,53,087.00\""))
    assert message is not None and message.startswith("line 2:")


def test_month_first_date_refused():
    """refuses a date that is not day first, with its line"""
    message = error_of(export(
        "04/10/2026,Tea,400123456789012345,45.00,,1000.00",
        "10/13/2026,Coffee,400123456789012346,55.00,,945.00",
    ))
    assert message is not None and message.startswith("line 3:")


def test_one_amount_per_record():
    """refuses a record with both amounts, or with neither"""
    assert error_of(export("04/10/2026,Odd,400123456789012345,10.00,20.00,1000.00")).startswith("line 2:")
    assert error_of(export("04/10/2026,Empty,400123456789012345,,,1000.00")).startswith("line 2:")

Then try the questions on spreadsheets, declared types, Indian formats and detection. Your weakest idea comes up first, and every choice, right or wrong, comes with the reason from the lesson’s own runs:

Interview questions

Warm-up (fresher to 2–5 years): why should account numbers, phone numbers and PIN codes stay text when you read a CSV file?

Because they are identifiers, not quantities: nobody adds two phone numbers. As text they keep every character. As numbers they lose some: a leading zero disappears, so 0484 becomes 484, and a long reference loses digits, because a spreadsheet keeps 15 significant digits and a JavaScript number is exact only up to 9,007,199,254,740,991. In the lesson’s run, two different 18-digit references became the same JavaScript number, so a lookup would match the wrong transaction. Keep them as text from the reader onwards, compare them as text, and if you must check their form, use a pattern such as exactly 10 digits for a mobile number. Convert a column to a number only when you will calculate with it, as with an amount.

Two harder questions follow, about dates that moved after a second bank was added and about references that a partner’s file changed. Decide what you would check first, then open each first step:

Key takeaways

• A spreadsheet’s default conversions remove leading zeros, keep only 15 significant digits, read digits around E as powers of ten and turn some codes into dates, so keep such columns text from the moment the file is opened.
• Type inference in code makes the same losses: Python’s guess drops zeros and mixes types in a column, and JavaScript’s Number() loses digits above the safe integer limit and turns empty text into 0; declare one type per column instead.
• Amounts formatted for India are grouped by three and then by two, and their dates put the day first; parse both with strict parsers that check the form first and refuse anything else, because guessing per row mixes two date orders in one column.
• csv.Sniffer and has_header() are heuristics that can return a wrong delimiter, a letter or a wrong answer; pass the documented dialect and encoding, and accept a guess only after the header and every row fit.