CSV Automation

Agree a small file specification first

Choose one source and one destination for the first workflow. Ask for an anonymised example export and the destination’s import requirements. List the column names, required fields, accepted date format, delimiter and text encoding. A file extension alone does not tell you these details.

Define what a row represents. An order export may contain one row per line item rather than one row per order. In that case, order number alone is an unsafe duplicate key. Use the source’s stable line identifier or an agreed combination that uniquely identifies the item.

Preserve codes such as 00127 as text. Decide whether blank means unknown, not applicable or an instruction to clear a destination field. Keep zero separate from missing. These choices prevent silent changes before any automation tool is selected.

Read CSV fields without breaking quoted content

A customer or product name can contain a comma. A quoted field can also contain a line break. Splitting each line at every comma can therefore shift values into the wrong columns. Use a CSV parser with the source’s agreed quoting and delimiter rules.

Python’s standard csv module provides readers and dictionary-based readers for this purpose. Its documentation specifies opening file objects with newline='' for correct newline handling. Parsing reads the structure; it does not prove that dates, quantities or identifiers are valid.

Wait until the source file is complete before reading it. A practical handover is an upload followed by an explicit ready marker or a move from a temporary location. Retain the original file unchanged and create a separate processed output so errors can be investigated.

Check a small order-line example

Worked example: a shop receives a daily CSV of order lines. Each line needs an order ID, line ID, SKU and positive whole-number quantity. The combination of order ID and line ID must be unique. The shop’s agreed rule rejects an exact repeat, while a changed repeat goes to review.

The four-row example below produces two accepted lines with a total quantity of five. One row repeats an existing line and one has a blank quantity. The exception report identifies the row and reason. It should never silently replace the blank quantity with zero.

Choose whether errors hold the whole file or allow a partial import. For this pilot, the owner holds the entire file until the source is corrected. This avoids a later operator assuming the order batch was complete.

Make validation reasons useful to the person fixing them

Check required headers before reading rows. An unexpected header change should produce a clear file-level error. Then validate required values, allowed formats, known product codes and agreed ranges. Keep the row number, stable identifier and reason in the exception report.

For dates, require the agreed format rather than guessing whether 03/04 means March or April. For decimal quantities or prices, define separators and rounding with the destination owner. Apply calculations only to fields that have passed their type checks.

If people open an output in spreadsheet software, consider how that software interprets untrusted cell content. OWASP documents CSV formula injection: a value may be interpreted as a formula. Use an export strategy appropriate to the receiving application and test it there; ordinary CSV quoting alone is not a complete defence.

Keep file-level and row-level duplicate checks

A content hash can recognise an identical file arriving twice, even if its filename changes. Record the source, arrival time, hash and processing outcome. A hash alone cannot detect the same business records inside a later file with reordered rows or corrected values.

Use the stable row key at the destination too. Distinguish a new record, an unchanged repeat and a permitted correction. Keep the earlier value and reason where the business needs an audit trail. Do not assume an import endpoint will protect you from duplicates.

Before releasing a completed batch, compare input rows, accepted rows, rejected rows and confirmed destination records. Check a useful control total such as quantity. If the destination times out, inspect its import result before resending the file.

Test a bounded CSV workflow before daily use

Test an empty file, a missing header, a quoted comma, a leading-zero code, a changed date format and a repeated batch. Include a deliberately invalid row. Confirm that the operator can find the cause and resume after correcting the source.

Automations For Business can build a custom file intake and checking workflow around your existing exports. Bring the source specification, an anonymised sample and a destination example. We can define the acceptance rules and human review queue before connecting a live import.

The spreadsheet data-entry article covers record collection more broadly. This CSV workflow focuses on the file boundary: exactly what arrives, what is accepted and how the import is reconciled. Use the worksheet below to agree those rules with the person who owns the records.

Your ready-to-use checklist

Make the next step clear.

Sources and notes

Sources checked on October 24, 2026.

• Python: CSV file reading and writing

  Official CSV parsing, reader and newline-handling documentation.

• OWASP: CSV Injection

  Why spreadsheet exports need checks for formula interpretation.