SPB Git

Repository Public License

TextTests.swift 17.9 KB 219 lines swift

1// ORIX Convert — Packages/EngineData/Tests/EngineDataTests/TextTests.swift2// Description: Delimited text: dialect and encoding sniffing, type detection and its safeguards, reading and writing3//              options, and the Excel-friendly preset.4// Author: Simon-Pierre Boucher — contact@spboucher.ai5import Foundation6import Testing7import ConvertCore8@testable import EngineData910@Suite("Delimited text")11struct TextTests {12    func write(_ scratch: Scratch, _ name: String, _ text: String) throws -> URL {13        let u = scratch.file(name)14        try text.write(to: u, atomically: true, encoding: .utf8)15        return u16    }17    func field(_ info: FileInspection, _ key: String) -> String { info.fields.first { $0.key == key }?.value ?? "" }1819    @Test func semicolonAndDecimalComma() async throws {20        let s = Scratch("txt")21        let u = try write(s, "fr.csv", "id;prix;date;libellé\n1;1,5;31/12/2024;\"a;b\"\n2;2,25;01/02/2024;c\n3;1 234,5;15/06/2023;d\n")22        let info = try await DataEngine().inspect(u, format: .csv)23        #expect(field(info, "Delimiter") == "semicolon (detected)" && field(info, "Header row") == "yes" && info.rowCount == 3 && info.columnCount == 4)24        let plain = try await DataPreview.preview(url: u, format: .csv)25        #expect(plain.columns.map(\.kind) == [.integer, .string, .date, .string], "without the decimal-comma option 1,5 is text")26        #expect(plain.rows[0] == ["1", "1,5", "2024-12-31", "a;b"])27        let fr = try await DataPreview.preview(url: u, format: .csv, options: .init(["numbers.decimal.comma": .bool(true), "numbers.thousands": .string("space")]))28        #expect(fr.columns.map(\.kind) == [.integer, .double, .date, .string])29        #expect(fr.rows.map { $0[1] } == ["1.5", "2.25", "1234.5"])30        await #expect(throws: ConversionError.self) { _ = try await DataPreview.preview(url: u, format: .csv, options: .init(["numbers.decimal.comma": .bool(true), "numbers.thousands": .string("comma")])) }31    }3233    @Test func tabPipeAndQuotedNewlines() async throws {34        let s = Scratch("txt")35        let tab = try write(s, "t.tsv", "a\tb\n1\t\"x\ty\"\n2\t\"multi\nline \"\"q\"\"\"\n")36        let t = try await DataPreview.preview(url: tab, format: .tsv)37        #expect(t.rows == [["1", "x\ty"], ["2", "multi\nline \"q\""]] && t.totalRows == 2)38        let tabAsCSV = try await DataEngine().inspect(tab, format: .csv)39        #expect(field(tabAsCSV, "Delimiter") == "tab (detected)")40        let pipe = try write(s, "p.csv", "a|b|c\n1|x,y|2.5\n2|z;w|3.5\n")41        let p = try await DataPreview.preview(url: pipe, format: .csv)42        #expect(p.columns.map(\.name) == ["a", "b", "c"] && p.rows[0] == ["1", "x,y", "2.5"])43        #expect(field(try await DataEngine().inspect(pipe, format: .csv), "Delimiter") == "pipe (detected)")44        // An override wins over detection.45        let forced = try await DataPreview.preview(url: pipe, format: .csv, options: .init(["csv.delimiter.input": .string("comma"), "csv.lenient": .bool(true), "header": .string("yes")]))46        #expect(forced.columns.map(\.name) == ["a|b|c", "column2"] && forced.rows == [["1|x", "y|2.5"], ["2|z;w|3.5", nil]])47        // Without leniency, rows of unequal length stop the conversion with an explanation.48        do { _ = try await DataPreview.preview(url: pipe, format: .csv, options: .init(["csv.delimiter.input": .string("comma")])); Issue.record("no error") } catch let e as ConversionError {49            guard case .malformedInput(let m) = e else { Issue.record("unexpected \(e)"); return }50            #expect(m.contains("same number of fields"))51        }52        #expect(DelimiterSniffer.detect(Array("a;b\n\"x;y\";\"multi\nline, with, commas\"\n1;2\n".utf8)) == 0x3B)53        #expect(DelimiterSniffer.detect(Array("single column\nno delimiter\n".utf8)) == nil)54    }5556    @Test func encodingsAreDetected() async throws {57        let s = Scratch("enc")58        let text = "nom,ville\nRené,Québec\nZoë,Köln\n"59        func check(_ name: String, _ data: Data, _ expected: String, forced: String? = nil, rows: [[String?]] = [["René", "Québec"], ["Zoë", "Köln"]]) async throws {60            let u = s.file(name)61            try data.write(to: u)62            let opts = ConversionOptions(forced.map { ["encoding.input": .string($0)] } ?? [:])63            if forced == nil { #expect(field(try await DataEngine().inspect(u, format: .csv), "Encoding") == expected, "\(name)") }64            let p = try await DataPreview.preview(url: u, format: .csv, options: opts)65            #expect(p.rows == rows, "\(name)")66            #expect(p.columns.map(\.name) == ["nom", "ville"], "\(name)")67        }68        try await check("utf8.csv", Data(text.utf8), "UTF-8")69        try await check("bom.csv", Data([0xEF, 0xBB, 0xBF]) + Data(text.utf8), "UTF-8 with BOM")70        try await check("u16le.csv", Data([0xFF, 0xFE]) + text.data(using: .utf16LittleEndian)!, "UTF-16 LE with BOM")71        try await check("u16be.csv", Data([0xFE, 0xFF]) + text.data(using: .utf16BigEndian)!, "UTF-16 BE with BOM")72        try await check("u16nobom.csv", text.data(using: .utf16LittleEndian)!, "UTF-16 LE (heuristic)")73        try await check("latin1.csv", text.data(using: .isoLatin1)!, "ISO-8859-1 (Latin-1) (heuristic)")74        let cp = "nom,ville\nL’été “chaud” – 5 €,Québec\n"75        try await check("cp1252.csv", cp.data(using: .windowsCP1252)!, "Windows-1252 (heuristic)", rows: [["L’été “chaud” – 5 €", "Québec"]])76        let mac = "nom,ville\nPréférence élève côté,Québec\n"77        try await check("mac.csv", mac.data(using: .macOSRoman)!, "Mac Roman (heuristic)", rows: [["Préférence élève côté", "Québec"]])78        // Forcing the encoding overrides detection (Latin-1 bytes read as Mac Roman give other letters).79        try await check("forced.csv", text.data(using: .isoLatin1)!, "", forced: "macintosh", rows: [["RenÈ", "QuÈbec"], ["ZoÎ", "Kˆln"]])80        // Detection that rests on a heuristic is said so in the conversion.81        let c = try await runConversion(s.file("latin1.csv"), .csv, to: .json, in: s)82        #expect(c.warnings.codes.contains("encoding-guessed"))83        #expect(try String(contentsOf: c.file, encoding: .utf8).contains("Québec"))84        #expect(TextEncoding.detect(sample: Data("plain ascii".utf8)) == TextEncodingDetection(encoding: .utf8, hasBOM: false, heuristic: false))85        #expect(TextEncoding.isValidUTF8([0xC3, 0xA9, 0xF0, 0x9F, 0x98, 0x80], allowTruncatedTail: false))86        #expect(!TextEncoding.isValidUTF8([0xC0, 0xAF], allowTruncatedTail: false) && !TextEncoding.isValidUTF8([0xED, 0xA0, 0x80], allowTruncatedTail: false) && !TextEncoding.isValidUTF8([0xE9, 0x20], allowTruncatedTail: true))87        #expect(TextEncoding.isValidUTF8([0x41, 0xE2, 0x82], allowTruncatedTail: true) && !TextEncoding.isValidUTF8([0x41, 0xE2, 0x82], allowTruncatedTail: false))88    }8990    @Test func typeDetectionIsConservative() async throws {91        let s = Scratch("types")92        let u = try write(s, "t.csv", """93        zip,int,big,huge,dec,exact,sci,flag,day,ts,tsz,clock,plus,mixed,blank,ambiguous,us94        00501,1,9007199254740993,123456789012345678901234567890,1.50,0.12345678901234567890,1e3,TRUE,2024-02-29,2024-02-29 13:45:10,2024-02-29T13:45:10+02:00,13:45:10.5,+1,1,,01/02/2024,12/31/202495        02134,-2,-9223372036854775808,-1,-0.25,1.5,2.5E-3,false,1999-12-31,2001-01-01T00:00:00.123456,2001-01-01T00:00:00Z,00:00:00,+33,x,,03/04/2024,01/15/202496        10001,0,0,0,7,2,1E+2,True,1970-01-01,1985-07-04T23:59,1985-07-04T23:59:59-05:00,23:59:59,-7,2.5,,05/06/2024,02/28/202397        """ + "\n")98        let p = try await DataPreview.preview(url: u, format: .csv)99        let types = Dictionary(uniqueKeysWithValues: p.columns.map { ($0.name, $0.type) })100        #expect(types["zip"] == "VARCHAR", "leading zeros stay text")101        #expect(types["int"] == "BIGINT" && types["big"] == "BIGINT")102        #expect(types["huge"] == "DECIMAL(38,0)", "integers beyond 64 bits are exact decimals")103        #expect(types["dec"] == "DOUBLE")104        #expect(types["exact"] == "DECIMAL(38,20)", "more than 15 significant digits are kept exactly")105        #expect(types["sci"] == "DOUBLE" && types["flag"] == "BOOLEAN" && types["day"] == "DATE" && types["ts"] == "TIMESTAMP")106        #expect(types["tsz"] == "TIMESTAMP WITH TIME ZONE" && types["clock"] == "TIME")107        #expect(types["plus"] == "VARCHAR", "+33 is a phone prefix, not a number")108        #expect(types["mixed"] == "VARCHAR" && types["blank"] == "VARCHAR")109        #expect(types["ambiguous"] == "VARCHAR", "01/02/2024 is not guessed")110        #expect(types["us"] == "DATE", "12/31/2024 can only be month/day/year")111        #expect(p.notes.contains { $0.code == "ambiguous-date-format" && $0.message.contains("“ambiguous”") })112        #expect(p.notes.contains { $0.code == "timezone-normalized" && $0.message.contains("“tsz”") })113        let row = Dictionary(uniqueKeysWithValues: zip(p.columns.map(\.name), p.rows[0]))114        #expect(row["zip"] == "00501" && row["big"] == "9007199254740993" && row["huge"] == "123456789012345678901234567890" && row["exact"] == "0.12345678901234567890")115        #expect(row["tsz"] == "2024-02-29T11:45:10Z" && row["ts"] == "2024-02-29T13:45:10" && row["us"] == "2024-12-31" && row["clock"] == "13:45:10.5")116        #expect(p.columns.first { $0.name == "blank" }?.nullCount == 3)117        // Explicit date format, forced types, and "all text".118        let dm = try await DataPreview.preview(url: u, format: .csv, options: .init(["dates.input.format": .string("%d/%m/%Y"), "types.columns": .string("int=text, zip=integer")]))119        let dmTypes = Dictionary(uniqueKeysWithValues: dm.columns.map { ($0.name, $0.type) })120        #expect(dmTypes["ambiguous"] == "DATE" && dmTypes["int"] == "VARCHAR" && dmTypes["zip"] == "BIGINT")121        #expect(dm.rows[0][dm.columns.firstIndex { $0.name == "ambiguous" } ?? 0] == "2024-02-01")122        let text = try await DataPreview.preview(url: u, format: .csv, options: .init(["types.detect": .bool(false)]))123        #expect(text.columns.allSatisfy { $0.type == "VARCHAR" } && text.rows[0][4] == "1.50")124        await #expect(throws: ConversionError.self) { _ = try await runConversion(u, .csv, to: .parquet, in: s, options: ["types.columns": .string("mixed=integer")]) }125        await #expect(throws: ConversionError.self) { _ = try await runConversion(u, .csv, to: .parquet, in: s, options: ["types.columns": .string("nope=integer")]) }126        await #expect(throws: ConversionError.self) { _ = try await runConversion(u, .csv, to: .parquet, in: s, options: ["types.columns": .string("int=complex")]) }127    }128129    @Test func detectionIsVerifiedBeyondTheSample() async throws {130        let s = Scratch("types")131        // 30 000 integer-looking rows, then a value that is not an integer and an identifier with a leading zero.132        var lines = ["n,code,when"]133        for i in 0..<30_000 { lines.append("\(i),\(1000 + i),2024-01-01") }134        lines.append("not a number,0042,not a date")135        let u = try write(s, "late.csv", lines.joined(separator: "\n") + "\n")136        let c = try await runConversion(u, .csv, to: .parquet, in: s)137        #expect(c.output.facts["rows"] == "30001")138        #expect(c.warnings.all.first { $0.code == "type-detection-reverted" }?.message.contains("“n”, “code”, “when”") == true)139        let p = try await DataPreview.preview(url: c.file, format: .parquet, rows: 5)140        #expect(p.columns.allSatisfy { $0.type == "VARCHAR" } && p.totalRows == 30_001)141        for check in try await DataEngine().validate(c.output, request: c.request, source: nil) { #expect(check.passed, "\(check.name): \(check.detail)") }142        let progress = c.warnings.progressValues.compactMap { $0 }143        #expect(progress.last == 1 && progress == progress.sorted(), "progress is measured and never goes backwards: \(progress.suffix(5))")144    }145146    @Test func headersNullsAndSkippedRows() async throws {147        let s = Scratch("hdr")148        let none = try write(s, "n.csv", "1,a\n2,b\n")149        let a = try await DataPreview.preview(url: none, format: .csv)150        #expect(a.columns.map(\.name) == ["column1", "column2"] && a.rows.count == 2)151        let asHeader = try await DataPreview.preview(url: none, format: .csv, options: .init(["header": .string("yes")]))152        #expect(asHeader.columns.map(\.name) == ["1", "a"] && asHeader.rows == [["2", "b"]])153        let titled = try write(s, "t.csv", "Report 2024\nexported by x\nid,name,name,,id\n1,NA,\"\",N/A,2\n3,\"NA\",x,-,4\n")154        let t = try await DataPreview.preview(url: titled, format: .csv, options: .init(["header.row": .int(3), "nulls.input": .string("NA, N/A, -")]))155        #expect(t.columns.map(\.name) == ["id", "name", "name_2", "column4", "id_2"])156        #expect(t.notes.contains { $0.code == "columns-renamed" && $0.message.contains("3 empty or duplicate") })157        // NA unquoted is NULL; "NA" quoted is text; "" is an empty string, not NULL.158        #expect(t.rows == [["1", nil, "", nil, "2"], ["3", "NA", "x", nil, "4"]])159        let ragged = try write(s, "r.csv", "a,b,c\n1,2,3\n4,5\n")160        await #expect(throws: ConversionError.self) { _ = try await runConversion(ragged, .csv, to: .json, in: s) }161        let lenient = try await DataPreview.preview(url: ragged, format: .csv, options: .init(["csv.lenient": .bool(true)]))162        #expect(lenient.rows == [["1", "2", "3"], ["4", "5", nil]])163        let empty = s.file("empty.csv")164        try Data().write(to: empty)165        await #expect(throws: ConversionError.malformedInput("The file is empty.")) { _ = try await runConversion(empty, .csv, to: .json, in: s) }166    }167168    @Test func writingOptions() async throws {169        let s = Scratch("out")170        let src = try await Canonical.fixture(.parquet, in: s)171        let excel = try await runConversion(src, .parquet, to: .csv, in: s, options: ["csv.preset": .string("excel")])172        let bytes = try Data(contentsOf: excel.file)173        #expect(bytes.starts(with: [0xEF, 0xBB, 0xBF]))174        let text = String(decoding: bytes.dropFirst(3), as: UTF8.self)175        #expect(text.hasPrefix("id,name,big,price,flag,day,at,zip,note\r\n1,héllo wörld,9007199254740993,1.5,true,2024-02-29,2024-02-29T13:45:10.123,00501,\"\"\r\n"))176        #expect(text.contains("\"line1\nline2\""), "a line break inside a quoted field is data, not a row terminator")177        #expect(text.components(separatedBy: "\r\n").count == 6)178        for check in try await DataEngine().validate(excel.output, request: excel.request, source: nil) { #expect(check.passed, "\(check.name): \(check.detail)") }179180        let custom = try await runConversion(src, .parquet, to: .csv, in: s, options: ["csv.delimiter": .string("semicolon"), "csv.header": .bool(false), "csv.quote.all": .bool(true), "nulls.output": .string("NULL"), "dates.output.format": .string("%d/%m/%Y")])181        let lines = try String(contentsOf: custom.file, encoding: .utf8).components(separatedBy: "\n")182        #expect(lines[0] == "\"1\";\"héllo wörld\";\"9007199254740993\";\"1.5\";\"true\";\"29/02/2024\";\"29/02/2024\";\"00501\";\"\"")183        #expect(lines[1].hasSuffix(";\"02134\";NULL"))184        for check in try await DataEngine().validate(custom.output, request: custom.request, source: nil) where check.name != "sample-values" { #expect(check.passed, "\(check.name): \(check.detail)") }185186        let utf16 = try await runConversion(src, .parquet, to: .tsv, in: s, options: ["encoding.output": .string("utf-16le"), "encoding.bom": .bool(true)])187        let u16 = try Data(contentsOf: utf16.file)188        #expect(u16.starts(with: [0xFF, 0xFE]) && String(data: u16.dropFirst(2), encoding: .utf16LittleEndian)?.hasPrefix("id\tname\t") == true)189        for check in try await DataEngine().validate(utf16.output, request: utf16.request, source: nil) { #expect(check.passed, "\(check.name): \(check.detail)") }190191        let latin = try await runConversion(src, .parquet, to: .csv, in: s, options: ["encoding.output": .string("iso-8859-1")])192        #expect(latin.warnings.all.first { $0.code == "unencodable-characters" }?.severity == .dataLoss)193        #expect(String(data: try Data(contentsOf: latin.file), encoding: .isoLatin1)?.contains("héllo wörld") == true)194    }195196    @Test func csvInjectionGuardIsOptIn() async throws {197        let s = Scratch("inj")198        let u = try write(s, "i.jsonl", #"{"a":"=HYPERLINK(\"http://x\")","b":"+1","c":"-2","d":"@SUM(A1)","e":"safe","n":-5}"# + "\n")199        let plain = try await runConversion(u, .jsonl, to: .csv, in: s)200        #expect(try String(contentsOf: plain.file, encoding: .utf8).contains("\n\"=HYPERLINK(\"\"http://x\"\")\",+1,-2,@SUM(A1),safe,-5\n"))201        #expect(!plain.warnings.codes.contains("csv-injection-guarded"))202        let guarded = try await runConversion(u, .jsonl, to: .csv, in: s, options: ["formula.guard": .bool(true)])203        #expect(try String(contentsOf: guarded.file, encoding: .utf8).contains("\n\"'=HYPERLINK(\"\"http://x\"\")\",'+1,'-2,'@SUM(A1),safe,-5\n"), "text cells are prefixed; the number -5 is not")204        #expect(guarded.warnings.codes.contains("csv-injection-guarded"))205        let sheet = try await runConversion(u, .jsonl, to: .xlsx, in: s, options: ["formula.guard": .bool(true)])206        #expect(try await DataPreview.preview(url: sheet.file, format: .xlsx).rows == [["'=HYPERLINK(\"http://x\")", "'+1", "'-2", "'@SUM(A1)", "safe", "-5"]])207        #expect(sheet.warnings.all.first { $0.code == "csv-injection-guarded" }?.message.hasPrefix("4 ") == true)208    }209210    @Test func columnSelectionAndRenaming() async throws {211        let s = Scratch("cols")212        let src = try await Canonical.fixture(.csv, in: s)213        let c = try await runConversion(src, .csv, to: .json, in: s, options: ["columns.select": .string("zip, id"), "columns.rename": .string("zip=postal code")])214        #expect(try String(contentsOf: c.file, encoding: .utf8).contains("{\"postal code\":\"00501\",\"id\":1}"))215        #expect(c.warnings.codes.contains("columns-not-selected"))216        await #expect(throws: ConversionError.self) { _ = try await runConversion(src, .csv, to: .json, in: s, options: ["columns.select": .string("missing")]) }217    }218}219
Files main
Menu

Theme