fileprep

fileprep

日本語 | Español | Français | 한국어 | Русский | 中文

fileprep is a Go library for cleaning, normalizing, and validating structured data—CSV, TSV, LTSV, Parquet, and Excel—through lightweight struct-tag rules, with seamless support for gzip, bzip2, xz, zstd, zlib, snappy, s2, and lz4 streams.

Why fileprep?

I developed nao1215/filesql, which allows you to execute SQL queries on files like CSV, TSV, LTSV, Parquet, and Excel. I also created nao1215/csv for CSV file validation.

While studying machine learning, I realized: "If I extend nao1215/csv to support the same file formats as nao1215/filesql, I could combine them to perform ETL-like operations." This idea led to the creation of fileprep—a library that bridges data preprocessing/validation with SQL-based file querying.

Features

• Multiple file format support: CSV, TSV, LTSV, Parquet, Excel (.xlsx)
• Compression support: gzip (.gz), bzip2 (.bz2), xz (.xz), zstd (.zst), zlib (.z), snappy (.snappy), s2 (.s2), lz4 (.lz4)
• Name-based column binding: Fields auto-match snake_case column names, customizable via name tag
• Struct tag-based preprocessing (prep tag): trim, lowercase, uppercase, default values
• Struct tag-based validation (validate tag): required, and more
• Seamless filesql integration: Returns io.Reader for direct use with filesql
• Detailed error reporting: Row and column information for each error

Installation

go get github.com/nao1215/fileprep

Requirements

• Go Version: 1.24 or later
• Operating Systems:
  • Linux
  • macOS
  • Windows

Quick Start

package main

import (
    "fmt"
    "os"
    "strings"

    "github.com/nao1215/fileprep"
)

// User represents a user record with preprocessing and validation
type User struct {
    Name  string `prep:"trim" validate:"required"`
    Email string `prep:"trim,lowercase"`
    Age   string
}

func main() {
    csvData := `name,email,age
  John Doe  ,[email protected],30
Jane Smith,[email protected],25
`

    processor := fileprep.NewProcessor(fileprep.FileTypeCSV)
    var users []User

    reader, result, err := processor.Process(strings.NewReader(csvData), &users)
    if err != nil {
        fmt.Printf("Error: %v\n", err)
        return
    }

    fmt.Printf("Processed %d rows, %d valid\n", result.RowCount, result.ValidRowCount)

    for _, user := range users {
        fmt.Printf("Name: %q, Email: %q\n", user.Name, user.Email)
    }

    // reader can be passed directly to filesql
    _ = reader
}

Output:

Processed 2 rows, 2 valid
Name: "John Doe", Email: "[email protected]"
Name: "Jane Smith", Email: "[email protected]"

Advanced Examples

Complex Data Preprocessing and Validation

This example demonstrates the full power of fileprep: combining multiple preprocessors and validators to clean and validate real-world messy data.

package main

import (
    "fmt"
    "strings"

    "github.com/nao1215/fileprep"
)

// Employee represents employee data with comprehensive preprocessing and validation
type Employee struct {
    // ID: pad to 6 digits, must be numeric
    EmployeeID string `name:"id" prep:"trim,pad_left=6:0" validate:"required,numeric,len=6"`

    // Name: clean whitespace, required alphabetic with spaces
    FullName string `name:"name" prep:"trim,collapse_space" validate:"required,alphaspace"`

    // Email: normalize to lowercase, validate format
    Email string `prep:"trim,lowercase" validate:"required,email"`

    // Department: normalize case, must be one of allowed values
    Department string `prep:"trim,uppercase" validate:"required,oneof=ENGINEERING SALES MARKETING HR"`

    // Salary: keep only digits, validate range
    Salary string `prep:"trim,keep_digits" validate:"required,numeric,gte=30000,lte=500000"`

    // Phone: extract digits, validate E.164 format after adding country code
    Phone string `prep:"trim,keep_digits,prefix=+1" validate:"e164"`

    // Start date: validate datetime format
    StartDate string `name:"start_date" prep:"trim" validate:"required,datetime=2006-01-02"`

    // Manager ID: required only if department is not HR
    ManagerID string `name:"manager_id" prep:"trim,pad_left=6:0" validate:"required_unless=Department HR"`

    // Website: fix missing scheme, validate URL
    Website string `prep:"trim,lowercase,fix_scheme=https" validate:"url"`
}

func main() {
    // Messy real-world CSV data
    csvData := `id,name,email,department,salary,phone,start_date,manager_id,website
  42,  John   Doe  ,[email protected],engineering,$75,000,555-123-4567,2023-01-15,000001,company.com/john
7,Jane Smith,[email protected],  Sales  ,"$120,000",(555) 987-6543,2022-06-01,000002,WWW.LINKEDIN.COM/in/jane
123,Bob Wilson,[email protected],HR,45000,555.111.2222,2024-03-20,,
99,Alice Brown,[email protected],Marketing,$88500,555-444-3333,2023-09-10,000003,https://alice.dev
`

    processor := fileprep.NewProcessor(fileprep.FileTypeCSV)
    var employees []Employee

    _, result, err := processor.Process(strings.NewReader(csvData), &employees)
    if err != nil {
        fmt.Printf("Fatal error: %v\n", err)
        return
    }

    fmt.Printf("=== Processing Result ===\n")
    fmt.Printf("Total rows: %d, Valid rows: %d\n\n", result.RowCount, result.ValidRowCount)

    for i, emp := range employees {
        fmt.Printf("Employee %d:\n", i+1)
        fmt.Printf("  ID:         %s\n", emp.EmployeeID)
        fmt.Printf("  Name:       %s\n", emp.FullName)
        fmt.Printf("  Email:      %s\n", emp.Email)
        fmt.Printf("  Department: %s\n", emp.Department)
        fmt.Printf("  Salary:     %s\n", emp.Salary)
        fmt.Printf("  Phone:      %s\n", emp.Phone)
        fmt.Printf("  Start Date: %s\n", emp.StartDate)
        fmt.Printf("  Manager ID: %s\n", emp.ManagerID)
        fmt.Printf("  Website:    %s\n\n", emp.Website)
    }
}

Output:

=== Processing Result ===
Total rows: 4, Valid rows: 4

Employee 1:
  ID:         000042
  Name:       John Doe
  Email:      [email protected]
  Department: ENGINEERING
  Salary:     75000
  Phone:      +15551234567
  Start Date: 2023-01-15
  Manager ID: 000001
  Website:    https://company.com/john

Employee 2:
  ID:         000007
  Name:       Jane Smith
  Email:      [email protected]
  Department: SALES
  Salary:     120000
  Phone:      +15559876543
  Start Date: 2022-06-01
  Manager ID: 000002
  Website:    https://www.linkedin.com/in/jane

Employee 3:
  ID:         000123
  Name:       Bob Wilson
  Email:      [email protected]
  Department: HR
  Salary:     45000
  Phone:      +15551112222
  Start Date: 2024-03-20
  Manager ID: 000000
  Website:

Employee 4:
  ID:         000099
  Name:       Alice Brown
  Email:      [email protected]
  Department: MARKETING
  Salary:     88500
  Phone:      +15554443333
  Start Date: 2023-09-10
  Manager ID: 000003
  Website:    https://alice.dev

Detailed Error Reporting

When validation fails, fileprep provides precise error information including row number, column name, and specific validation failure reason.

package main

import (
    "fmt"
    "strings"

    "github.com/nao1215/fileprep"
)

// Order represents an order with strict validation rules
type Order struct {
    OrderID    string `name:"order_id" validate:"required,uuid4"`
    CustomerID string `name:"customer_id" validate:"required,numeric"`
    Email      string `validate:"required,email"`
    Amount     string `validate:"required,number,gt=0,lte=10000"`
    Currency   string `validate:"required,len=3,uppercase"`
    Country    string `validate:"required,alpha,len=2"`
    OrderDate  string `name:"order_date" validate:"required,datetime=2006-01-02"`
    ShipDate   string `name:"ship_date" validate:"datetime=2006-01-02,gtfield=OrderDate"`
    IPAddress  string `name:"ip_address" validate:"required,ip_addr"`
    PromoCode  string `name:"promo_code" validate:"alphanumeric"`
    Quantity   string `validate:"required,numeric,gte=1,lte=100"`
    UnitPrice  string `name:"unit_price" validate:"required,number,gt=0"`
    TotalCheck string `name:"total_check" validate:"required,eqfield=Amount"`
}

func main() {
    // CSV with multiple validation errors
    csvData := `order_id,customer_id,email,amount,currency,country,order_date,ship_date,ip_address,promo_code,quantity,unit_price,total_check
550e8400-e29b-41d4-a716-446655440000,12345,[email protected],500.00,USD,US,2024-01-15,2024-01-20,192.168.1.1,SAVE10,2,250.00,500.00
invalid-uuid,abc,not-an-email,-100,US,USA,2024/01/15,2024-01-10,999.999.999.999,PROMO-CODE-TOO-LONG!!,0,0,999
550e8400-e29b-41d4-a716-446655440001,,bob@test,50000,EURO,J1,not-a-date,,2001:db8::1,VALID20,101,-50,50000
123e4567-e89b-42d3-a456-426614174000,99999,[email protected],1500.50,JPY,JP,2024-02-28,2024-02-25,10.0.0.1,VIP,5,300.10,1500.50
`

    processor := fileprep.NewProcessor(fileprep.FileTypeCSV)
    var orders []Order

    _, result, err := processor.Process(strings.NewReader(csvData), &orders)
    if err != nil {
        fmt.Printf("Fatal error: %v\n", err)
        return
    }

    fmt.Printf("=== Validation Report ===\n")
    fmt.Printf("Total rows:     %d\n", result.RowCount)
    fmt.Printf("Valid rows:     %d\n", result.ValidRowCount)
    fmt.Printf("Invalid rows:   %d\n", result.RowCount-result.ValidRowCount)
    fmt.Printf("Total errors:   %d\n\n", len(result.ValidationErrors()))

    if result.HasErrors() {
        fmt.Println("=== Error Details ===")
        for _, e := range result.ValidationErrors() {
            fmt.Printf("Row %d, Column '%s': %s\n", e.Row, e.Column, e.Message)
        }
    }
}

Output:

=== Validation Report ===
Total rows:     4
Valid rows:     1
Invalid rows:   3
Total errors:   23

=== Error Details ===
Row 2, Column 'order_id': value must be a valid UUID version 4
Row 2, Column 'customer_id': value must be numeric
Row 2, Column 'email': value must be a valid email address
Row 2, Column 'amount': value must be greater than 0
Row 2, Column 'currency': value must have exactly 3 characters
Row 2, Column 'country': value must have exactly 2 characters
Row 2, Column 'order_date': value must be a valid datetime in format: 2006-01-02
Row 2, Column 'ip_address': value must be a valid IP address
Row 2, Column 'promo_code': value must contain only alphanumeric characters
Row 2, Column 'quantity': value must be greater than or equal to 1
Row 2, Column 'unit_price': value must be greater than 0
Row 2, Column 'ship_date': value must be greater than field OrderDate
Row 2, Column 'total_check': value must equal field Amount
Row 3, Column 'customer_id': value is required
Row 3, Column 'email': value must be a valid email address
Row 3, Column 'amount': value must be less than or equal to 10000
Row 3, Column 'currency': value must have exactly 3 characters
Row 3, Column 'country': value must contain only alphabetic characters
Row 3, Column 'order_date': value must be a valid datetime in format: 2006-01-02
Row 3, Column 'quantity': value must be less than or equal to 100
Row 3, Column 'unit_price': value must be greater than 0
Row 3, Column 'ship_date': value must be greater than field OrderDate
Row 4, Column 'ship_date': value must be greater than field OrderDate

Preprocessing Tags (prep)

Multiple tags can be combined: prep:"trim,lowercase,default=N/A"

Basic Preprocessors

String Transformation

Character Filtering

Padding

Advanced Preprocessors

Validation Tags (validate)

Multiple tags can be combined: validate:"required,email"

Basic Validators

Character Type Validators

Numeric Comparison Validators

String Validators

String Content Validators

Format Validators

Network Validators

Cross-Field Validators

Conditional Required Validators

Supported File Formats

Supported Compression Formats

Note on Parquet compression: The external compression (.parquet.gz, etc.) is for the container file itself. Parquet files may also use internal compression (Snappy, GZIP, LZ4, ZSTD) which is handled transparently by the parquet-go library.

Note on Excel files: Only the first sheet is processed. Multi-sheet workbooks will have subsequent sheets ignored.

Integration with filesql

// Process file with preprocessing and validation
processor := fileprep.NewProcessor(fileprep.FileTypeCSV)
var records []MyRecord

reader, result, err := processor.Process(file, &records)
if err != nil {
    return err
}

// Check for validation errors
if result.HasErrors() {
    for _, e := range result.ValidationErrors() {
        log.Printf("Row %d, Column %s: %s", e.Row, e.Column, e.Message)
    }
}

// Pass preprocessed data to filesql using Builder pattern
ctx := context.Background()
builder := filesql.NewBuilder().
    AddReader(reader, "my_table", filesql.FileTypeCSV)

validatedBuilder, err := builder.Build(ctx)
if err != nil {
    return err
}

db, err := validatedBuilder.Open(ctx)
if err != nil {
    return err
}
defer db.Close()

// Execute SQL queries on preprocessed data
rows, err := db.QueryContext(ctx, "SELECT * FROM my_table WHERE age > 20")

Design Considerations

Name-Based Column Binding

Struct fields are mapped to file columns by name, not by position. Field names are automatically converted to snake_case to match CSV column headers:

// File columns: user_name, email_address, phone_number (any order)
type User struct {
    UserName     string  // → matches "user_name" column
    EmailAddress string  // → matches "email_address" column
    PhoneNumber  string  // → matches "phone_number" column
}

Column order doesn't matter - fields are matched by name, so you can reorder columns in your CSV without changing your struct.

Custom Column Names with name Tag

Use the name tag to override the auto-generated column name:

type User struct {
    UserName string `name:"user"`       // → matches "user" column (not "user_name")
    Email    string `name:"mail_addr"`  // → matches "mail_addr" column (not "email")
    Age      string                     // → matches "age" column (auto snake_case)
}

Missing Columns Behavior

If a CSV column doesn't exist for a struct field, the field value is treated as an empty string. Validation still runs, so required will catch missing columns:

type User struct {
    Name    string `validate:"required"`  // Error if "name" column is missing
    Country string                        // Empty string if "country" column is missing
}

Case Sensitivity and Duplicate Headers

Header matching is case-sensitive and exact. A struct field UserName maps to user_name, but headers like User_Name, USER_NAME, or userName will not match:

type User struct {
    UserName string  // ✓ matches "user_name"
                     // ✗ does NOT match "User_Name", "USER_NAME", "userName"
}

This applies to all file formats: CSV, TSV, LTSV keys, and Parquet/XLSX column names must match exactly.

Duplicate column names: If a file contains duplicate header names (e.g., id,id,name), the first occurrence is used for binding:

id,id,name
first,second,John  → struct.ID = "first" (first "id" column wins)

Format-Specific Notes

LTSV, Parquet, and XLSX follow the same case-sensitive matching rules. Keys/column names must match exactly:

type Record struct {
    UserID string                 // expects "user_id" key/column
    Email  string `name:"EMAIL"`  // use name tag for non-snake_case columns
}

If your LTSV keys use hyphens (user-id) or Parquet/XLSX columns use camelCase (userId), use the name tag to specify the exact column name.

Memory Usage

fileprep loads the entire file into memory for processing. This enables random access and multi-pass operations but has implications for large files:

For compressed inputs (gzip, bzip2, xz, zstd, zlib, snappy, s2, lz4), memory usage is based on decompressed size.

Performance

Benchmark results processing CSV files with a complex struct containing 21 columns. Each field uses multiple preprocessing and validation tags:

Preprocessing tags used: trim, lowercase, uppercase, keep_digits, pad_left, strip_html, strip_newline, collapse_space, truncate, fix_scheme, default

Validation tags used: required, alpha, numeric, email, uuid, ip_addr, url, oneof, min, max, len, printascii, ascii, eqfield

# Quick benchmark (100 and 1,000 records)
make bench

# Full benchmark (all sizes including 50,000 records)
make bench-all

Tested on AMD Ryzen AI MAX+ 395, Go 1.24, Linux. Results vary by hardware.

Related or inspired Projects

• nao1215/filesql - sql driver for CSV, TSV, LTSV, Parquet, Excel with gzip, bzip2, xz, zstd support.
• nao1215/fileframe - DataFrame API for CSV/TSV/LTSV, Parquet, Excel.
• nao1215/csv - read csv with validation and simple DataFrame in golang.
• go-playground/validator - Go Struct and Field validation, including Cross Field, Cross Struct, Map, Slice and Array diving
• shogo82148/go-header-csv - go-header-csv is encoder/decoder csv with a header.

Contributing

Contributions are welcome! Please see the Contributing Guide for more details.

Support

If you find this project useful, please consider:

• Giving it a star on GitHub - it helps others discover the project
• Becoming a sponsor - your support keeps the project alive and motivates continued development

Your support, whether through stars, sponsorships, or contributions, is what drives this project forward. Thank you!

License

This project is licensed under the MIT License - see the LICENSE file for details.