Import files through a staging table

How to do it

Parse with a CSV library, never by splitting on commas. RFC 4180 lets a quoted field hold commas, line breaks and doubled quotes. Read the file as UTF-8 and drop a leading byte order mark, which some spreadsheet programs write. Cap the rows and the bytes per file. Match each row on a natural key, such as an SKU. Then a second import updates rows instead of adding copies, as Make writes safe to repeat says. Store the import's ID on every row it wrote, so one bad import can be undone whole. Keep cells that start with = as plain text, and escape them on the way out as Neutralise formulas in spreadsheet exports says.

Examples

Python example

import csv
import io

MAX_ROWS = 10_000

def read_import(raw: bytes):
    reader = csv.DictReader(io.StringIO(raw.decode("utf-8-sig"), newline=""))
    good, bad = [], []
    for row in reader:
        if len(good) + len(bad) == MAX_ROWS:
            raise ImportTooLarge(MAX_ROWS)
        problem = check_row(row)
        if problem:
            bad.append({"line": reader.line_num, "problem": problem})
        else:
            good.append(row)
    return good, bad

Go example

const maxRows = 10_000

func readImport(raw []byte) (good [][]string, bad []RowError, err error) {
	reader := csv.NewReader(bytes.NewReader(bytes.TrimPrefix(raw, []byte("\xef\xbb\xbf"))))
	header, err := reader.Read()
	if err != nil {
		return nil, nil, err
	}
	for {
		record, err := reader.Read()
		if err == io.EOF {
			return good, bad, nil
		}
		if err != nil {
			return nil, nil, err
		}
		if len(good)+len(bad) == maxRows {
			return nil, nil, ErrTooManyRows
		}
		line, _ := reader.FieldPos(0)
		if problem := checkRow(header, record); problem != "" {
			bad = append(bad, RowError{Line: line, Problem: problem})
		} else {
			good = append(good, record)
		}
	}
}

How to check

Import a test file with a byte order mark, a quoted comma, a line break in a quoted field and a bad line 7. The preview names line 7 alone, and the other rows land once. Import the same file again, and the row count stays the same.

Sources

• RFC 4180 Common Format for CSV Files

Related rules

• API-32 Accept idempotency keys on POST that creates or charges in API design
• APS-99 Neutralise formulas in spreadsheet exports in Application security