Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 38 ยท Real-world Tasks

CSV Reports and Data Cleaning with AWK

Clean messy CSV with sed and AWK, then build reports: validate rows, trim and normalise values, fix dates, handle headers, group totals, percentages and top-N lists.

CSV is the world's most common data exchange format and almost never arrives clean. Columns have stray spaces, names appear as ANN, ann and Ann , dates come in three formats, some rows are short, some amounts say N/A. Before you can total anything you must clean. sed and AWK are good at both halves of the job: normalise the data, then summarise it.

The files in this lesson use simple CSV (no commas inside quoted values). If your data has quoted commas, use the quote-aware splitter from Multi-line Records, Paragraph Mode and CSV first.

Step 1: look before you leap

Never trust a data file. Check its shape: how many columns does each row have, and which rows differ?

Bash Profile the file

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with read or cat), it gets the next line from this box.

In a few lines we found one short row (line 6, no status), one non-numeric amount, three spellings of paid, trailing spaces and a different date format. That is a typical file.

Step 2: clean it

Cleaning rules are decisions. Here: trim spaces, normalise names to Title Case and statuses to lower case, convert MM/DD/YYYY to ISO dates, treat a missing or non-numeric amount as empty, and reject rows with the wrong number of fields into a separate file so nothing disappears silently.

Bash A cleaning script

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with read or cat), it gets the next line from this box.

Warnings go to standard error (> "/dev/stderr"), so they are not mixed into the cleaned data when you redirect standard output. Keeping the header row (NR == 1 { print; next }) is a pattern you will use constantly.

Step 3: report

Now the summary the business asked for: revenue per customer for paid orders, share of total, and the top customer.

Bash Revenue per customer

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with read or cat), it gets the next line from this box.

The customer name goes in the last column because names contain spaces: sort -k1,1nr then sorts on the leading revenue number only. The header is printed first by the shell, the rows are sorted, and the total is printed after sorting so it stays at the bottom. (An AWK-only version can print the header in BEGIN and send just the rows through print | "sort -k1,1nr", then close("sort -k1,1nr") before printing the total.)

Step 4: reshaping and converting

Bash Daily totals, month by month

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with read or cat), it gets the next line from this box.

This is how to produce sorted output inside AWK when you cannot use sort: collect the keys in an array and sort them with a tiny insertion sort. Dates in ISO format (YYYY-MM-DD) sort correctly as plain strings, which is one more reason to convert them.

Step 5: check the result

A report is only useful if it is right. Compare against an independent calculation: the sum of the report must equal the sum of the input.

Bash Reconciling totals

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with read or cat), it gets the next line from this box.

Common mistakes

  • Cleaning in place: always write a new file so you can rerun from the raw data.
  • Silently dropping bad rows. Reject to a file or stderr and count them.
  • Comparing amounts as strings; add +0 or check with a regex.
  • Forgetting OFS: after changing a field, AWK joins fields with a space unless OFS = ",".
  • Locale: numbers such as 1.234,50 need preprocessing before arithmetic.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why set both FS and OFS to a comma when rewriting CSV rows?

    When a field is assigned, AWK rebuilds $0 using OFS, which defaults to a space.
  2. 2. What does NR == 1 { print; next } do?

    It passes the header through unchanged and moves on to the next record without running later rules on it.
  3. 3. How do you keep rejected rows out of the cleaned output file?

    Redirecting the rejects keeps standard output clean and the problems visible.
  4. 4. Why are ISO dates like 2025-03-17 convenient?

    Year-month-day order means alphabetical order equals chronological order.

Summary

  • Profile first: count fields per row, list distinct values, find non-numeric amounts.
  • Clean with explicit rules: trim, normalise case, convert dates, blank invalid numbers, reject bad rows.
  • Keep the header with NR == 1 { print; next } and set FS = OFS = ",".
  • Report with arrays: totals, counts, shares and cumulative columns.
  • Reconcile report totals against the raw data.

Next: System Administration Text Tasks