CSV revenue totals broken by BOM, CRLF and quoted commas

Three separate problems, each with a different symptom.

• BOM. The file starts with the three bytes EF BB BF, which represent U+FEFF. cut treats them as part of the first field of the first line, so the header's first field becomes a name with an invisible leading character and is counted as a region. Diagnostic: head -c 3 sales.csv | od -An -tx1 or file sales.csv.
• CRLF. Windows line endings leave a carriage return at the end of the last field. awk -F, sees the revenue value with a trailing control character, so numeric conversion and sums fail or the last column is treated as a string. Diagnostic: head -1 sales.csv | cat -A shows ^M$ at the end of the line.
• Commas inside quoted fields. cut -d, and awk -F, are not CSV parsers. In a field such as a quoted Widget, large, the comma inside the quotes is a field separator to them, so every later column shifts. Diagnostic: compare the number of fields per row with the header using awk -F, '{print NF}' sales.csv | sort | uniq -c; rows with quoted commas have one more field than the header.

The invisible leading character and the impossible revenue totals are therefore two different failures: encoding and line-ending handling, and quoting.

Use Python's standard csv module, which is already present and understands quoting correctly:

python3 - <<'PY'
import csv, collections
from decimal import Decimal
totals = collections.defaultdict(Decimal)
with open('sales.csv', newline='', encoding='utf-8-sig') as f:
    reader = csv.DictReader(f)
    for row in reader:
        region = row['region'].strip()
        totals[region] += Decimal(row['revenue'].strip())
for region in sorted(totals):
    print(region, totals[region])
PY

Why it is correct:

• utf-8-sig consumes the BOM, so the first header name is clean.
• newline='' lets the CSV module handle CRLF instead of leaving carriage returns in the last field.
• csv.DictReader maps values by header name, so a comma inside a quoted product does not shift the revenue field.
• The CSV module implements the quoting rules, including doubled double quotes, as a state machine rather than a fragile regular expression.
• Decimal avoids floating-point rounding in a financial report.

If you must stay in awk, gawk 5.2 has a CSV mode: gawk --csv 'NR>1 {rev[$1]+=$4} END {for (r in rev) print r, rev[r]}' sales.csv. Validate either output against wc -l before publishing.

Not reliably. A regex such as sed -E ':a; s/("[^",]*),([^"]*")/\1\2/g; ta' can handle a single simple quoted comma, but it breaks on doubled quotes, on multiple commas inside one field, on quoted fields containing both quotes and commas, and on any multiline field. CSV quoting is a small state machine: inside quotes, a doubled quote is a literal quote, and a comma is data. sed has no robust persistent in-quote state across the line for this grammar.

The dangerous part is the failure mode: it will work for most rows and silently corrupt a subset, producing plausible but wrong totals. That is worse than a visible error during a monthly close. Use the Python csv module, or gawk --csv if you must stay in awk, and validate the row count and a sample of high-value rows against the source. Do not spend the two-hour window debugging a sed expression.