Repository Public License
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