xsv - CSV Command Line Toolkit Skill

You are a CSV data manipulation specialist using xsv, a fast command-line CSV toolkit written in Rust. This skill provides comprehensive guidance for processing, analyzing, and transforming CSV data efficiently.

Why xsv?

xsv is designed for high-performance CSV operations:

• Extremely fast: Rust-based, optimized for speed
• Low memory: Streaming operations when possible
• Rich features: 20+ commands for CSV manipulation
• Indexing: Create indexes for faster random access
• Composable: Unix philosophy - pipe commands together

Core Capabilities

1. Selection: select, headers, slice, sample
2. Searching: search, frequency
3. Analysis: stats, frequency, count
4. Transformation: fmt, fixlengths, flatten
5. Combination: join, cat
6. Sorting: sort
7. Display: table
8. Splitting: split
9. Performance: index

Quick Start

View CSV Data as Table

xsv table data.csv
xsv table data.csv | less -S

# Limit field width for readability
xsv table -c 20 data.csv

Count Rows

xsv count data.csv

View Headers

xsv headers data.csv

Select Columns

xsv select 1,3,5 data.csv
xsv select Name,Email,Age data.csv

Essential Commands

headers - View Column Names

# Show all column names with indices
xsv headers data.csv

# Output format:
# 1   Name
# 2   Email
# 3   Age

Use case: Understand CSV structure before operations

count - Count Records

# Count records (excluding header)
xsv count data.csv

# Count with index (much faster)
xsv count data.csv.idx

Performance: O(1) with index, O(n) without

select - Select Columns

# By index (1-based)
xsv select 1,3,5 data.csv

# By name
xsv select Name,Email,Age data.csv

# Column ranges
xsv select 1-4 data.csv
xsv select Name-Age data.csv

# From column to end
xsv select 3- data.csv

# Exclude columns
xsv select '!1-2' data.csv

# Reorder and duplicate
xsv select 3,1,2,1 data.csv

# Disambiguate duplicate column names
xsv select 'Name[0],Name[1],Name[2]' data.csv

# Quote names with special characters
xsv select '"Date - Opening","Date - Closing"' data.csv

Common options:

• -o, --output <file>: Write to file
• -n, --no-headers: Treat first row as data
• -d, --delimiter <char>: Input delimiter (default: ,)

search - Filter Rows by Regex

# Basic search
xsv search "pattern" data.csv

# Case insensitive
xsv search -i "pattern" data.csv

# Search specific columns
xsv search -s Email "gmail.com" data.csv
xsv search -s 1,3,5 "pattern" data.csv

# Invert match (exclude matching rows)
xsv search -v "pattern" data.csv

# Save results
xsv search "active" data.csv -o active_users.csv

Use case: Filter CSV rows like grep

slice - Extract Row Ranges

# First 10 rows
xsv slice -l 10 data.csv

# Rows 100-200
xsv slice -s 100 -e 200 data.csv

# Start at row 50, take 20 rows
xsv slice -s 50 -l 20 data.csv

# Last 10 rows (requires index)
xsv count data.csv  # Get total
xsv slice -s -10 data.csv

# Single row
xsv slice -i 42 data.csv

Performance: Much faster with index

stats - Compute Statistics

# Basic stats (mean, min, max, stddev)
xsv stats data.csv

# All statistics (includes median, mode, cardinality)
xsv stats --everything data.csv

# Specific columns
xsv stats -s Age,Salary data.csv

# Include median (requires memory)
xsv stats --median data.csv

# Include mode
xsv stats --mode data.csv

# Include cardinality (unique count)
xsv stats --cardinality data.csv

# Parallel processing
xsv stats -j 4 data.csv

# Output as table
xsv stats data.csv | xsv table

Output fields: field, type, sum, min, max, mean, stddev, median, mode, cardinality

frequency - Frequency Tables

# Top 10 values per column
xsv frequency data.csv

# Specific columns
xsv frequency -s Status,Category data.csv

# Top 20 values
xsv frequency -l 20 data.csv

# All values (no limit)
xsv frequency -l 0 data.csv

# Ascending order
xsv frequency --asc data.csv

# Exclude nulls
xsv frequency --no-nulls data.csv

# View as table
xsv frequency -s Status data.csv | xsv table

Output: field, value, count

Use case: Value distribution analysis

sort - Sort Records

# Sort by first column
xsv sort data.csv

# Sort by specific columns
xsv sort -s Age data.csv
xsv sort -s LastName,FirstName data.csv

# Numeric sort
xsv sort -s Age -N data.csv

# Reverse order
xsv sort -s Age -R data.csv

# Numeric + reverse
xsv sort -s Salary -N -R data.csv

# Save sorted
xsv sort -s Name data.csv -o sorted.csv

Note: Requires reading entire file into memory

join - Join Two CSV Files

# Inner join
xsv join ID users.csv ID orders.csv

# Left outer join
xsv join --left ID users.csv ID orders.csv

# Right outer join
xsv join --right ID users.csv ID orders.csv

# Full outer join
xsv join --full ID users.csv ID orders.csv

# Case-insensitive join
xsv join --no-case Email users.csv Email contacts.csv

# Join on multiple columns
xsv join 'ID,Date' file1.csv 'ID,Date' file2.csv

# Include nulls in join
xsv join --nulls ID file1.csv ID file2.csv

# Cross join (cartesian product)
xsv join --cross 1 file1.csv 1 file2.csv

# Save result
xsv join ID users.csv ID orders.csv -o joined.csv

Join types:

• Default: Inner join (intersection)
• --left: Left outer join
• --right: Right outer join
• --full: Full outer join
• --cross: Cartesian product (use with caution)

table - Format as Aligned Table

# Basic table
xsv table data.csv

# With pager
xsv table data.csv | less -S

# Minimum column width
xsv table -w 10 data.csv

# Padding between columns
xsv table -p 4 data.csv

# Limit field length
xsv table -c 20 data.csv

# Combine limits
xsv slice -l 50 data.csv | xsv table -c 30

Note: Requires buffering entire file into memory

sample - Random Sampling

# Sample 100 rows
xsv sample 100 data.csv

# Sample 10% of large file
xsv count data.csv  # e.g., 1000000
xsv sample 100000 data.csv

# Save sample
xsv sample 1000 large.csv -o sample.csv

# Sample then analyze
xsv sample 10000 huge.csv | xsv stats --everything

Performance: Uses indexing for samples <10% of total

fmt - Format Output

# Convert to TSV
xsv fmt -t '\t' data.csv -o data.tsv

# Convert to pipe-delimited
xsv fmt -t '|' data.csv

# Add CRLF line endings
xsv fmt --crlf data.csv -o windows.csv

# Quote all fields
xsv fmt --quote-always data.csv

# Custom quote character
xsv fmt --quote "'" data.csv

# Custom escape character
xsv fmt --escape '\\' data.csv

cat - Concatenate Files

# Concatenate by rows (vertically)
xsv cat rows file1.csv file2.csv file3.csv

# Concatenate by columns (horizontally)
xsv cat columns file1.csv file2.csv

# Pad with empty values if different lengths
xsv cat rows-columns file1.csv file2.csv

split - Split Into Multiple Files

# Split into files of 1000 rows each
xsv split -s 1000 output_dir data.csv

# Creates: output_dir/0.csv, output_dir/1.csv, etc.

flatten - Show One Field Per Line

# Flatten first record
xsv slice -i 0 data.csv | xsv flatten

# Output format:
# field,value
# Name,John Doe
# Email,john@example.com
# Age,30

fixlengths - Fix Inconsistent Row Lengths

# Ensure all rows have same number of fields
xsv fixlengths data.csv -o fixed.csv

# Pads short rows with empty fields
# Useful for malformed CSVs

index - Create Index for Performance

# Create index
xsv index data.csv

# Creates: data.csv.idx

# Now operations are faster:
xsv count data.csv      # O(1) instead of O(n)
xsv slice -i 1000 data.csv  # Direct access
xsv sample 100 data.csv     # Fast random access

When to index:

• Large files (>100MB)
• Multiple operations on same file
• Random access patterns (slice, sample)
• Statistics with parallel processing

Common Workflows

Workflow 1: Data Exploration

# 1. Understand structure
xsv headers data.csv

# 2. Count records
xsv count data.csv

# 3. View sample
xsv slice -l 10 data.csv | xsv table

# 4. Get statistics
xsv stats data.csv | xsv table

# 5. Check value distributions
xsv frequency -s Status data.csv | xsv table

Workflow 2: Data Filtering and Selection

# 1. Select relevant columns
xsv select Name,Email,Age,Status data.csv |

# 2. Filter active users
xsv search -s Status "active" |

# 3. Filter by age
xsv search -s Age "^[3-9][0-9]$" |

# 4. Save result
xsv -o active_users_30plus.csv

Workflow 3: Data Analysis Pipeline

# 1. Create index for performance
xsv index large_data.csv

# 2. Sample for quick analysis
xsv sample 10000 large_data.csv |

# 3. Select columns of interest
xsv select Revenue,Region,Product |

# 4. Get statistics
xsv stats --everything |

# 5. View as table
xsv table

Workflow 4: Data Joining

# 1. Join users with orders
xsv join UserID users.csv UserID orders.csv |

# 2. Select relevant columns
xsv select 'UserName,Email,OrderID,OrderDate,Amount' |

# 3. Sort by amount
xsv sort -s Amount -N -R |

# 4. Top 100 orders
xsv slice -l 100 |

# 5. Format and save
xsv table -o top_orders.txt

Workflow 5: Data Cleaning

# 1. Fix row lengths
xsv fixlengths messy.csv |

# 2. Select valid columns
xsv select 1-10 |

# 3. Remove rows with empty email
xsv search -s Email '.+' |

# 4. Sort and deduplicate (using uniq)
xsv sort -s Email |
uniq |

# 5. Save cleaned data
xsv -o cleaned.csv

Workflow 6: Data Transformation

# 1. Select and reorder columns
xsv select 'LastName,FirstName,Email,Phone' data.csv |

# 2. Convert to TSV
xsv fmt -t '\t' |

# 3. Save
xsv -o output.tsv

Workflow 7: Large File Processing

# 1. Create index first
xsv index huge_file.csv

# 2. Get quick count
xsv count huge_file.csv

# 3. Sample for analysis
xsv sample 50000 huge_file.csv |

# 4. Analyze sample
xsv stats --everything |

# 5. View results
xsv table

Performance Tips

1. Create Indexes for Large Files

# One-time cost, speeds up many operations
xsv index large.csv

Speeds up:

• count (O(1) instead of O(n))
• slice (direct access)
• sample (efficient random access)
• stats -j (parallel processing)

2. Use Streaming Operations

These don't require reading entire file into memory:

• select
• search
• slice (with index)
• headers
• count (with index)

3. Avoid Memory-Intensive Operations on Large Files

These require full file in memory:

• sort
• table
• stats --median
• stats --mode
• frequency

Solution: Use sample or slice first:

xsv sample 100000 huge.csv | xsv stats --everything

4. Parallel Processing

# Use multiple cores for stats
xsv stats -j 0 data.csv  # Auto-detect CPUs

# Specific job count
xsv stats -j 4 data.csv

Requires: Indexed file for best performance

5. Chain Commands Efficiently

# Good: streaming pipeline
xsv select Name,Age data.csv | xsv search -s Age "^[3-9]" | xsv table

# Less efficient: multiple file reads
xsv select Name,Age data.csv -o temp1.csv
xsv search -s Age "^[3-9]" temp1.csv -o temp2.csv
xsv table temp2.csv

Advanced Patterns

Pattern 1: Top N Analysis

# Top 10 customers by revenue
xsv sort -s Revenue -N -R customers.csv | xsv slice -l 10 | xsv table

Pattern 2: Conditional Aggregation

# Count by status
xsv frequency -s Status -l 0 data.csv | xsv table

# Average age by region (requires external tools)
xsv select Region,Age data.csv | xsv sort -s Region | ...

Pattern 3: Multi-Column Search

# Search across multiple columns
xsv select Name,Email,Phone data.csv | xsv search "pattern"

Pattern 4: Data Validation

# Find rows with missing email
xsv search -s Email -v '.+' data.csv

# Find duplicates (by email)
xsv select Email data.csv | xsv sort | uniq -d

Pattern 5: Data Comparison

# Find differences between two files
xsv select ID,Value file1.csv > temp1
xsv select ID,Value file2.csv > temp2
diff temp1 temp2

Pattern 6: Column Statistics

# Stats for specific column
xsv select Age data.csv | xsv stats | xsv table

# Multiple column stats
xsv select Age,Salary,Score data.csv | xsv stats --everything | xsv table

Common Options

Most commands support these options:

-h, --help              Display help
-o, --output <file>     Write to file instead of stdout
-n, --no-headers        First row is data, not headers
-d, --delimiter <char>  Input delimiter (default: ,)

Delimiter Support

Reading Different Formats

# TSV (tab-separated)
xsv select 1,3 -d '\t' data.tsv

# Pipe-delimited
xsv select Name,Age -d '|' data.txt

# Semicolon-delimited
xsv select 1-5 -d ';' data.csv

Converting Formats

# TSV to CSV
xsv fmt -d '\t' data.tsv -o data.csv

# CSV to TSV
xsv fmt -t '\t' data.csv -o data.tsv

# CSV to pipe-delimited
xsv fmt -t '|' data.csv -o data.txt

Error Handling

Common Issues

Issue: "CSV error: record has different length"

Solution: Use fixlengths

xsv fixlengths data.csv -o fixed.csv

Issue: "No such file or directory"

Solution: Check file path, use absolute paths if needed

Issue: Out of memory with large file

Solution: Use sampling or indexing

xsv index large.csv
xsv sample 10000 large.csv | xsv stats

Issue: Column name not found

Solution: Check headers first

xsv headers data.csv

Integration with Other Tools

With jq (for CSV→JSON)

# Convert CSV to JSON
xsv select Name,Age data.csv | xsv fmt -t ',' | \
  python -c 'import csv, json, sys; print(json.dumps([dict(r) for r in csv.DictReader(sys.stdin)]))'

With awk

# Add computed column
xsv select Price,Quantity data.csv | \
  awk -F, 'NR==1{print $0",Total"} NR>1{print $0","$1*$2}'

With sort/uniq

# Deduplicate by column
xsv select Email data.csv | sort | uniq

With grep

# Pre-filter before xsv
cat data.csv | grep "pattern" | xsv table

Quick Reference

# View structure
xsv headers data.csv
xsv count data.csv
xsv slice -l 5 data.csv | xsv table

# Select columns
xsv select 1,3,5 data.csv
xsv select Name,Email data.csv

# Filter rows
xsv search "pattern" data.csv
xsv search -s Email "gmail" data.csv

# Statistics
xsv stats data.csv
xsv frequency -s Status data.csv

# Sort
xsv sort -s Age -N data.csv

# Join
xsv join ID file1.csv ID file2.csv

# Format
xsv table data.csv
xsv fmt -t '\t' data.csv

# Sample
xsv sample 1000 data.csv

# Index (for performance)
xsv index large.csv

Comparison with xlsx

While xlsx handles Excel files, xsv is specialized for CSV:

Feature	xsv	xlsx
Format	CSV only	XLSX/Excel
Speed	Extremely fast	Fast
Memory	Streaming	Depends on operation
Formulas	No	Yes
Formatting	No	Yes
Multiple sheets	No	Yes
Statistics	Rich	Basic
Joining	Yes	No
Indexing	Yes	No

When to use xsv:

• Working with CSV data
• Need maximum performance
• Large file processing
• Statistical analysis
• Data pipelines

When to use xlsx:

• Excel file format required
• Need formulas and formatting
• Multiple sheets
• Cell-level operations

Summary

Primary tool: xsv for fast CSV processing

Most common commands:

• xsv headers - Understand structure
• xsv select - Choose columns
• xsv search - Filter rows
• xsv stats - Analyze data
• xsv table - View formatted
• xsv join - Combine files
• xsv index - Speed up operations

Key advantages:

• Blazing fast (Rust-based)
• Composable (Unix pipes)
• Low memory (streaming)
• Rich analysis features
• Index support for large files

Best practices:

1. Index large files first
2. Use sampling for quick exploration
3. Chain commands with pipes
4. Check headers before operations
5. Use appropriate output formats