knoopx/nu-shell-tabular-data

csv-tsv-nushell

Read, filter, transform, and manipulate CSV/TSV files using Nushell's structured data pipeline.

Quick Start

nu -c 'open data.csv | where rating > 4.0'     # Filter CSV
nu -c 'open data.tsv -s "\t" | get column1'    # Read TSV with tab separator
open file.csv | save output.json               # Convert format

Core Commands

Opening Files

# Auto-detects format from extension
open data.csv              # CSV file
open data.tsv              # TSV file (tab-separated)
open data.xlsx             # Excel file

# With flags
open data.csv --raw        # Raw text, not parsed
open data.csv --trim       # Trim whitespace from headers and values

Parsing with from csv

# Parse CSV string with custom options
"col1,col2
1,2" | from csv

# Custom separator (for TSV or other delimiters)
open data.txt | from csv --separator "\t"    # Tab-separated
open data.txt | from csv --separator ";"     # Semicolon-separated

# Handle variable columns
open data.csv | from csv --flexible          # Allow different column counts

# Skip header row
open data.csv | from csv --noheaders         # First row is data, not headers

# Ignore comments
open data.csv | from csv --comment "#"       # Skip lines starting with #

# Trim whitespace
open data.csv | from csv --trim all          # Trim headers and values
open data.csv | from csv --trim headers      # Trim only headers
open data.csv | from csv --trim fields       # Trim only values

Data Operations

Filtering and Selecting

# Filter rows by condition
open data.csv | where rating > 4.0
open data.csv | where status == "active"
open data.csv | where price > 100 and category == "electronics"

# Select specific columns
open data.csv | select name price category
open data.csv | select -exclude internal_id notes

# Sort data
open data.csv | sort-by price --reverse
open data.csv | sort-by category name      # Multi-column sort

# Limit results
open data.csv | first 10                   # First 10 rows
open data.csv | last 5                     # Last 5 rows
open data.csv | skip 10 | first 20         # Rows 11-30

Transforming Data

# Add new column
open data.csv | each { |row| { ...$row, tax: ($row.price * 0.1) } }

# Rename columns
open data.csv | rename name price category | rename name customer_name

# Update column values
open data.csv | update price { |row| $row.price * 1.09 }  # Add 9%

# Merge columns
open data.csv | each { |row| { ...$row, full_name: ($row.first_name + " " + $row.last_name) } }

# Insert column at position
open data.csv | insert tax 0               # Insert at beginning

Aggregation and Statistics

# Count rows
open data.csv | length

# Sum values
open data.csv | get price | sum

# Average
open data.csv | get price | average

# Min/Max
open data.csv | get price | min
open data.csv | get price | max

# Unique values with counts
open data.csv | get category | uniq --count
# Returns table with 'value' and 'count' columns

# Find duplicates
open data.csv | get Title | uniq --count | where count > 1
# Shows: Sala Apolo (2), El Patio (2)

Practical Examples

Clean and Validate CSV

# Load, trim whitespace, remove empty rows, validate required fields
let clean_data = (open orders.csv
    | from csv --trim all
    | where customer != "" and email != ""
    | update email { |row| $row.email | str downcase | str trim })

# Check for duplicates
let duplicates = ($clean_data | get email | uniq --count | where count > 1)
if ($duplicates | length) > 0 {
    echo $"Found ($duplicates | length) duplicate emails"
}

$clean_data | save orders_clean.csv

# Convert string numbers to floats for comparison
let with_ratings = (open places.csv
    | where Rating != ""
    | each { |r| { ...$r, Rating_num: ($r.Rating | into float) } })

# Now filter by numeric value
$with_ratings | where Rating_num > 4.0 | select Title Rating_num

Merge Multiple CSVs

# Merge files with same structure
let all_data = (
    open january.csv |
    open february.csv |
    open march.csv
) | each { |file| open $file } | flatten

# Or with glob pattern
ls *.csv | each { |f| open $f.name } | flatten | save combined.csv

TSV to CSV Conversion

# Convert TSV to CSV
open data.tsv | from csv --separator "\t" | save data.csv

# Convert CSV to TSV
open data.csv | save output.tsv --separator "\t"

Data Quality Checks

# Find empty cells
open data.csv | each { |row|
    $row | columns | each { |col|
        if $row | get $col == "" { $"($col) is empty" }
    }
} | flatten | where $it != null

# Find rows with missing required fields
open data.csv | where name == "" or email == "" or phone == ""

# Check data types
open data.csv | get price | each { |p| $p | into decimal } | describe

Conditional Updates

# Update based on conditions
open products.csv | update status { |row|
    if $row.stock < 10 { "low" }
    else if $row.stock == 0 { "out_of_stock" }
    else { "in_stock" }
}

# Multiple conditional updates
open orders.csv | update total { |row|
    if $row.total > 100 { $row.total * 0.95 }  # 5% discount
    else { $row.total }
}

Save Options

# Save as CSV (default)
open data.json | save output.csv

# Save as TSV
open data.csv | save output.tsv --separator "\t"

# Save as other formats
open data.csv | to json | save output.json
open data.csv | save output.yaml

# Append to file
open new_data.csv | save --append existing.csv

# With pretty print
open data.csv | to json --pretty | save output.json

Column Operations

# Rename columns
open data.csv | rename old_name new_name

# Reorder columns
open data.csv | select category price name  # Specify order

# Delete columns
open data.csv | drop internal_id notes

# Insert new column
open data.csv | insert calculated_field { |row| $row.price * $row.quantity }

# Move column
open data.csv | move name --after category

Handling Edge Cases

# Files without headers
open data.csv | from csv --noheaders | rename col1 col2 col3

# Inconsistent column counts
open data.csv | from csv --flexible

# Quoted fields with commas
open data.csv | from csv --quote '"'  # Default, handles "field,with,commas"

# Escape characters
open data.csv | from csv --escape "\\"

# Different line endings
open data.csv | lines | from csv --noheaders  # Handle manually if needed

Custom Commands for CSV Work

# Create reusable CSV cleaning command
def "clean-csv" [file: path] {
    open $file
    | from csv --trim all
    | where ($in | columns | any { |col| $in | get $col != "" })
    | update date { |row| $row.date | str trim }
}

# CSV validation command
def "validate-csv" [file: path, required_columns: list] {
    let data = (open $file)
    let cols = ($data | columns)

    $required_columns | each { |req_col|
        if not ($cols | any { |c| $c == $req_col }) {
            echo $"ERROR: Missing required column: ($req_col)"
        }
    }

    $data | where ($required_columns | any { |col| $in | get $col == "" }) | length
}

# Quick CSV stats
def "csv-stats" [file: path] {
    let data = (open $file)
    {
        rows: ($data | length)
        columns: ($data | columns | length)
        column_names: ($data | columns)
    }
}

Common Patterns

Extract Specific Data

# Get all unique categories with counts
open products.csv | get category | uniq --count | sort-by count --reverse

# Find top 10 by value
open sales.csv | sort-by amount --reverse | first 10 | select name amount

# Filter by multiple conditions
open users.csv | where status == "active" | where last_login > "2024-01-01"

Data Transformation Pipeline

# Complete ETL pipeline
open raw_data.csv
| from csv --trim all                          # Load and clean
| where status != "deleted"                   # Filter
| update created_at { |row| $row.created_at | date to-table }  # Transform dates
| each { |row| { ...$row, total: ($row.price * $row.quantity) } }  # Calculate
| sort-by created_at --reverse                # Sort
| save cleaned_data.csv                       # Output

Constraints

• Column names with spaces require quotes: open data.csv | get "column name"
• Use --flexible for CSVs with inconsistent column counts
• from csv is needed for custom separators; open auto-detects for .csv files
• Empty cells are represented as empty string - filter with where column != "" before numeric conversion
• Convert string numbers to floats: $value | into float (filter empty strings first)
• Date parsing: $row.date | into datetime
• Use into int for integers, into float for decimals, into datetime for dates
• Multi-condition where clauses need parentheses: ($row.Title == "X" and ($row.Address | str contains "Y"))
• uniq --count returns table with value and count columns, not grouped records
• In-place file updates require collect | save --force to avoid read/write conflicts
• Use $row.column not $in.column inside update blocks
• Update one entry at a time to avoid bash quoting issues with special characters
• Single if/else works in update blocks; avoid else if chains in -c commands

Real-World Usage Patterns

Conditional Updates by Title

# Update specific rows based on title match (one at a time to avoid bash quoting issues)
open "Favorite Places.csv"
| update Description { |row|
    if $row.Title == "Parc Sant Salvador" {
        "New description here"
    } else {
        $row.Description  # Keep original
    }
}
| collect | save --force "Favorite Places.csv"

# For multiple entries, run separate update commands:
# nu -c 'open "data.csv" | update col { |row| if $row.Title == "A" { "Desc A" } else { $row.col } } | collect | save --force "data.csv"'
# nu -c 'open "data.csv" | update col { |row| if $row.Title == "B" { "Desc B" } else { $row.col } } | collect | save --force "data.csv"'

Multi-Condition Updates

# Handle duplicate titles with additional condition (use parentheses for complex conditions)
open "Favorite Places.csv"
| update Description { |row|
    if ($row.Title == "El Patio" and ($row.Address | str contains "Sevilla")) {
        "Specific description for this location"
    } else {
        $row.Description
    }
}
| collect | save --force "Favorite Places.csv"

Numeric Filtering with String Columns

# Convert string ratings to floats for comparison
open "Favorite Places.csv"
| where Rating != ""
| each { |r| { ...$r, Rating_num: ($r.Rating | into float) } }
| where Rating_num > 4.0
| select Title Rating_num

Find and Update Multiple Entries

# Update entries one at a time to avoid bash quoting issues
# Run separate commands for each entry:

nu -c 'open "data.csv" | update col { |row| if $row.Title == "A" { "Desc A" } else { $row.col } } | collect | save --force "data.csv"'

nu -c 'open "data.csv" | update col { |row| if $row.Title == "B" { "Desc B" } else { $row.col } } | collect | save --force "data.csv"'

nu -c 'open "data.csv" | update col { |row| if $row.Title == "C" { "Desc C" } else { $row.col } } | collect | save --force "data.csv"'

Verify Changes

# Check a specific entry after update
open "data.csv" | where Title == "EntryName" | get Description | first

Filter Short Descriptions

# Find entries with descriptions under 100 chars
open "Favorite Places.csv"
| each { |row| if ($row.Description | str length) < 100 { $row } else { null } }
| where $it != null
| select Title Description

Exclude Pattern Matches

# Filter out entries matching pattern
open "Favorite Places.csv"
| where Title !~ "FPV"  # Exclude FPV sites
| each { |row| if ($row.Description | str length) < 150 { $row } else { null } }
| where $it != null

String Operations in Conditions

# Use str contains for partial matches (requires each/where pattern)
open "data.csv"
| each { |row| if ($row.Address | str contains "Barcelona") { $row } else { null } }
| where $it != null
| each { |row| if ($row.Description | str contains "restaurant") { $row } else { null } }
| where $it != null

Save with Same Filename

# Overwrite original file (requires collect to avoid read/write conflict)
open "data.csv"
| update column { ... }
| collect | save --force "data.csv"

# Or save as new file
open "data.csv"
| update column { ... }
| save "data_enriched.csv"