Skip to main content

CSV ETL Pipeline

An ETL (Extract, Transform, Load) pipeline reads raw data, applies cleaning and transformation rules, and exports a clean output. VisiData handles this interactively — and records every step in a CommandLog you can replay on any new file.

Learning Focus

Follow this pipeline end-to-end once. The pattern — open → inspect → clean → transform → export → save CommandLog — is reusable for any CSV workflow.

Sample Raw Data​

mkdir -p ~/github/practice-folder/visidata/06-etl

cat > ~/github/practice-folder/visidata/06-etl/01-raw-orders.csv << 'EOF'
order_id,customer,region,amount,currency,status,created_at
1001,Alice Tan,SG,150.00,SGD,COMPLETE,2025-01-15
1002,bob smith,MY,200.50,MYR,complete,2025-01-16
1003,Carol Lee,SG,,SGD,PENDING,2025-01-17
1004,dave wong,ID,75.25,IDR,Complete,2025-01-17
1005,Eve Chong,SG,300.00,SGD,COMPLETE,2025-01-18
EOF

vd ~/github/practice-folder/visidata/06-etl/01-raw-orders.csv

Problems visible in this data:

  • customer names have inconsistent capitalization
  • status has three variants: COMPLETE, complete, Complete
  • amount is empty for row 3 (Carol)
  • currency is irrelevant for internal reporting

Step 1 — Inspect with Describe Sheet​

Shift+I

Profile result:

name        type  nulls  min      max
order_id    str   0
customer    str   0
region      str   0
amount      str   1               ← null for Carol
currency    str   0
status      str   0
created_at  str   0

Issues confirmed: amount has 1 null; all types are string.

Step 2 — Cast Column Types​

# Press q → back to source
# Move to 'order_id' → press #  (integer)
# Move to 'amount'   → press %  (float)   ← Carol row shows #ERR
# Move to 'created_at' → press @ (date)

Step 3 — Remove Rows with Invalid Amount​

# Select rows where amount failed to cast (None after float cast)
z|
# Enter: amount is None

# Delete selected rows
gd
# Press U to undo if needed

Step 4 — Normalize Customer Names​

# Select all rows
gs

# Normalize customer name to Title Case
g=
# Enter: customer.title()

Before:

customer
Alice Tan
bob smith
dave wong

After g= with customer.title():

customer
Alice Tan
Bob Smith
Dave Wong

Step 5 — Normalize Status to Lowercase​

# All rows still selected
g=
# Move to 'status' column first
g=
# Enter: status.lower()

Result — all status values: complete, pending

Step 6 — Remove Unnecessary Column​

# Move to 'currency' column
d
# Column deleted (undo with U if needed)

Step 7 — Create Derived Column: Amount in USD​

# Move to 'amount' column
=
# Enter: amount * 0.74 if region == 'MY' else amount * 0.00006 if region == 'ID' else amount
# Name: amount_usd

Step 8 — Export Clean Data​

Ctrl+S
# Enter: ~/github/practice-folder/visidata/06-etl/01-clean-orders.csv

Step 9 — Save the Workflow as CommandLog​

Shift+D       # view CommandLog
Ctrl+S
# Enter: ~/github/practice-folder/visidata/06-etl/clean_orders.vdj
q             # return to source

Step 10 — Replay on a New File​

vd --play ~/github/practice-folder/visidata/06-etl/clean_orders.vdj \
   --batch ~/github/practice-folder/visidata/06-etl/02-raw-orders.csv \
   -o ~/github/practice-folder/visidata/06-etl/02-clean-orders.csv

This runs the entire pipeline headlessly on any new raw file.

Complete Pipeline Summary​

vd ~/github/practice-folder/visidata/06-etl/01-raw-orders.csv

  1. Shift+I         →  inspect nulls and types
  2. # % @           →  cast order_id, amount, created_at
  3. z| amount None → gd  →  remove invalid rows
  4. gs → g= customer.title()  →  normalize names
  5. g= status.lower()          →  normalize status
  6. d on 'currency'            →  drop unused column
  7. = → amount_usd expr        →  derived column
  8. Ctrl+S                     →  export clean CSV
  9. Shift+D → Ctrl+S           →  save CommandLog
 10. vd --play *.vdj            →  replay on next batch

What's Next​