CSV sniffer

use csv_sniff.qweruse csv_sniff, csv_sniff_file from csv_sniff      // Quillo 0.6: only these

// An import page that works out how an uploaded CSV is written before reading it.////   quillo run csv_sniff.qwe      then open http://localhost:8080/bankuse "csv_sniff.qwer"// A Dutch bank export: semicolons, decimal commas, a header, quotes around cells holding a `;`.route GET /bank = csv_sniff_file("csv_sniff_sample.csv")?route GET /samples = {    us: csv_sniff("id,name,total\n1,Ada,120.50\n2,Grace,99.00\n")?,    tabs_no_header: csv_sniff("1042\tAda Lovelace\t120.50\n1043\tGrace Hopper\t99.00\n1044\tAlan Turing\t15.00\n")?,    pipes: csv_sniff("sku|title|qty\nB7|Blue mug|2\nC2|Red mug|5\n")?.delimiter,}route POST /import {    let s = csv_sniff(request.body)?    if s.confidence < 0.8 { reply 400 "The rows do not split evenly: is this a CSV file?" }    { delimiter: s.delimiter, columns: s.header ?? range(s.columns) | map((i) => `column ${i + 1}`), decimal_comma: s.decimal_comma }}

//! Guess how an uploaded CSV file is written before importing it: its delimiter (`,` `;` tab `|`), quote character, whether the first row is a header, decimal commas and line endings.//! version: 1.0.0//!//! [dependencies]use serde_json::{json, Value};/// At most this much of the text is looked at: 64 KiB, or 100 rows.const SAMPLE: usize = 64 * 1024;const ROWS: usize = 100;fn split_rows(text: &str, delim: char, quote: char) -> Vec<Vec<String>> {    let mut rows = vec![];    let mut row = vec![];    let mut cell = String::new();    let mut quoted = false;    let mut chars = text.chars().peekable();    while let Some(c) = chars.next() {        if quoted {            if c == quote {                if chars.peek() == Some(&quote) {                    cell.push(quote);                    chars.next();                } else {                    quoted = false;                }            } else {                cell.push(c);            }        } else if c == quote && cell.trim().is_empty() {            cell.clear();            quoted = true;        } else if c == delim {            row.push(std::mem::take(&mut cell));        } else if c == '\n' || c == '\r' {            if c == '\r' && chars.peek() == Some(&'\n') {                chars.next();            }            row.push(std::mem::take(&mut cell));            if !(row.len() == 1 && row[0].is_empty()) {                rows.push(std::mem::take(&mut row));            } else {                row.clear();            }            if rows.len() >= ROWS {                return rows;            }        } else {            cell.push(c);        }    }    // The last row, unless the sample cut it.    if !cell.is_empty() || !row.is_empty() {        row.push(cell);        rows.push(row);    }    rows}fn number(s: &str, decimal_comma: bool) -> bool {    let t = s.trim().trim_start_matches(['-', '+']).trim_start_matches(['$', '€', '£']).trim_end_matches('%');    let t = if decimal_comma { t.replace('.', "").replace(',', ".") } else { t.replace(',', "") };    !t.is_empty() && t.parse::<f64>().is_ok()}fn mode(counts: &[usize]) -> (usize, usize) {    let mut best = (0, 0);    for &c in counts {        let n = counts.iter().filter(|&&x| x == c).count();        if n > best.1 || (n == best.1 && c > best.0) {            best = (c, n);        }    }    best}/// How `text` (the start of a CSV file is enough) is written: `{ delimiter, quote, has_header, header, columns,/// decimal_comma, line_ending, rows_sampled, confidence }`. `header` is the first row's cells when `has_header`, else null;/// `decimal_comma` is true for European numbers (`12,50` with `;` between cells); `confidence` from 0 to 1 says how evenly/// the rows split. Only the first 64 KiB and 100 rows are read. An error for text that has no rows.pub fn csv_sniff(text: &str) -> Result<Value, String> {    let mut end = text.len().min(SAMPLE);    while !text.is_char_boundary(end) {        end -= 1;    }    let sample = text[..end].trim_start_matches('\u{feff}');    if sample.trim().is_empty() {        return Err("the text is empty".into());    }    let quote = if sample.matches('"').count() >= sample.matches('\'').count() / 2 + 1 || !sample.contains('\'') { '"' } else { '\'' };    let mut best: Option<(f64, char, Vec<Vec<String>>, usize)> = None;    for d in [',', ';', '\t', '|'] {        let rows = split_rows(sample, d, quote);        let counts: Vec<usize> = rows.iter().map(|r| r.len()).collect();        let (cols, n) = mode(&counts);        if cols < 2 || rows.is_empty() {            continue;        }        let score = n as f64 / rows.len() as f64;        let better = match &best {            None => true,            Some((s, _, _, c)) => score > *s + 1e-9 || ((score - s).abs() < 1e-9 && cols > *c),        };        if better {            best = Some((score, d, rows, cols));        }    }    let (confidence, delim, rows, cols) = match best {        Some(b) => b,        // One column: no delimiter appears.        None => (1.0, ',', split_rows(sample, ',', quote), 1),    };    let decimal_comma = delim != ',' && rows.iter().skip(1).flatten().any(|c| {        let t = c.trim();        t.contains(',') && !t.contains(' ') && t.replace(['.', ','], "").chars().all(|x| x.is_ascii_digit()) && t.rsplit(',').next().is_some_and(|f| (1..=3).contains(&f.len()))    });    // A header: for each column, a first cell unlike the cells below it (text over numbers, or another length of text).    let mut votes = 0i64;    if rows.len() >= 2 {        for c in 0..cols {            let below: Vec<&String> = rows[1..].iter().filter_map(|r| r.get(c)).filter(|s| !s.trim().is_empty()).collect();            let Some(first) = rows[0].get(c) else { continue };            if below.is_empty() {                continue;            }            let numeric = below.iter().filter(|s| number(s, decimal_comma)).count() * 2 > below.len();            if numeric {                votes += if number(first, decimal_comma) { -1 } else { 1 };            } else {                let lens: std::collections::HashSet<usize> = below.iter().map(|s| s.chars().count()).collect();                if lens.len() == 1 {                    votes += if lens.contains(&first.chars().count()) { -1 } else { 1 };                }                let unique = !below.iter().any(|s| s.trim().eq_ignore_ascii_case(first.trim()));                if !unique {                    votes -= 1;                }            }        }    }    let first_row_distinct = rows.first().is_some_and(|r| {        let set: std::collections::HashSet<&str> = r.iter().map(|s| s.trim()).collect();        set.len() == r.len() && r.iter().all(|s| !s.trim().is_empty())    });    let has_header = votes > 0 || (votes == 0 && first_row_distinct && rows.len() >= 2 && rows[0].iter().all(|s| !number(s, decimal_comma)));    let line_ending = if sample.contains("\r\n") { "\r\n" } else if sample.contains('\r') && !sample.contains('\n') { "\r" } else { "\n" };    Ok(json!({        "delimiter": delim.to_string(),        "quote": quote.to_string(),        "has_header": has_header,        "header": if has_header { rows.first().map(|r| r.iter().map(|s| s.trim().to_string()).collect::<Vec<_>>()) } else { None },        "columns": cols,        "decimal_comma": decimal_comma,        "line_ending": line_ending,        "rows_sampled": rows.len(),        "confidence": (confidence * 100.0).round() / 100.0,    }))}/// The start of a file sniffed as csv_sniff sniffs text (64 KiB are read, whatever the file's size). Bytes that are not/// UTF-8 (Excel's Windows-1252 exports) are read as Latin-1 for the guess.pub fn csv_sniff_file(path: &str) -> Result<Value, String> {    use std::io::Read;    let mut f = std::fs::File::open(path).map_err(|e| format!("{}: {}", path, e))?;    let mut buf = vec![0u8; SAMPLE];    let mut n = 0;    while n < SAMPLE {        let k = f.read(&mut buf[n..]).map_err(|e| format!("{}: {}", path, e))?;        if k == 0 {            break;        }        n += k;    }    buf.truncate(n);    let text = match std::str::from_utf8(&buf) {        Ok(t) => t.to_string(),        // A UTF-8 character cut at the end of the sample is not an error.        Err(e) if e.error_len().is_none() => String::from_utf8_lossy(&buf[..e.valid_up_to()]).into_owned(),        Err(_) => buf.iter().map(|&b| b as char).collect(),    };    csv_sniff(&text)}