SPB Git

Repository Public

CSVTests.swift 19.3 KB 321 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetIO/Tests/SheetIOTests/CSVTests.swift10// Module           : SheetIOTests11// Repository       : https://git.spboucher.ai/orix-sheet12// Created          : 2026-11-2113// Last Modified    : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author           : Simon-Pierre Boucher20// Position         : Professor of Finance21// Institution      : Université du Québec en Outaouais (UQO)22// Department       : Département des sciences administratives23// Organization     : SP Boucher24//25// Website          : https://spboucher.ai26// Git              : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General          : info@spboucher.ai33// Professional     : contact@spboucher.ai34// Direct           : simon@spboucher.ai35// Research         : research@spboucher.ai36// Development      : dev@spboucher.ai37// Support          : support@spboucher.ai38// Security/Admin   : admin@spboucher.ai39// Notifications    : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification   : Public66// Distribution     : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License          : None specified (no license file in this repository)80// Maintainer       : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90@testable import SheetIO9192private func csv(_ text: String, tsv: Bool = false, _ configure: (inout LoadOptions) -> Void = { _ in }) throws -> Workbook {93    var o = LoadOptions()94    configure(&o)95    return try CSVReader.load(data: Data(text.utf8), tsv: tsv, sheetName: "S", options: o, progress: nil).workbook96}97private func grid(_ wb: Workbook) -> [[String]] {98    let s = wb.sheets[0]99    guard let used = s.cells.usedRange() else { return [] }100    return (0...used.r1).map { r in (0...used.c1).map { c in s.value(r, c).description } }101}102103@Suite struct CSVTests {104    @Test func rfc4180Parsing() throws {105        let text = "name,note,qty\r\n\"Smith, John\",\"said \"\"hi\"\"\",3\r\n\"multi\r\nline\",,\"4\"\r\nlast,\"\",5"106        let wb = try csv(text)107        #expect(grid(wb) == [["name", "note", "qty"], ["Smith, John", "said \"hi\"", "3"], ["multi\r\nline", "", "4"], ["last", "", "5"]])108        #expect(cellValue(wb, 0, "C2") == .number(3) && cellValue(wb, 0, "C3") == .number(4))109        #expect(wb.sheets[0].cell(1, 1)?.value == .text("said \"hi\""))110        // line endings: LF, CR, CRLF, with and without a final newline; blank lines keep their row111        #expect(grid(try csv("a,b\n1,2\n")) == [["a", "b"], ["1", "2"]])112        #expect(grid(try csv("a,b\r1,2\r")) == [["a", "b"], ["1", "2"]])113        #expect(grid(try csv("a,b\n\n1,2")) == [["a", "b"], ["", ""], ["1", "2"]])114        #expect(grid(try csv("a,,c,\n,,,")) == [["a", "", "c"]])115        #expect(try csv("").sheets[0].cells.count == 0)116        // malformed quoting is tolerated without losing data117        #expect(grid(try csv("\"abc\"def,2\n\"unterminated,3")) == [["abcdef", "2"], ["unterminated,3", ""]])118    }119120    @Test func delimiterSniffing() throws {121        #expect(grid(try csv("a;b;c\n1;2;3\n4;5;6")) == [["a", "b", "c"], ["1", "2", "3"], ["4", "5", "6"]])122        #expect(grid(try csv("a\tb\n1\t2")) == [["a", "b"], ["1", "2"]])123        #expect(grid(try csv("a|b|c\n1|2|3")) == [["a", "b", "c"], ["1", "2", "3"]])124        // commas inside quoted fields do not fool the sniffer125        #expect(grid(try csv("\"a,x\";b\n\"1,5\";2\n\"3,25\";4")) == [["a,x", "b"], ["1.5", "2"], ["3.25", "4"]])126        #expect(grid(try csv("single column\nvalue")) == [["single column"], ["value"]])127        #expect(grid(try csv("a,b;c\n1,2;3", { $0.csv.delimiter = ";" })) == [["a,b", "c"], ["1.2", "3"]])128        #expect(grid(try csv("a,b\tc\n1\t2", tsv: true)) == [["a,b", "c"], ["1", "2"]])129    }130131    @Test func encodings() throws {132        func load(_ d: Data, _ enc: TextEncoding? = nil) throws -> LoadResult {133            var o = LoadOptions(); o.csv.encoding = enc134            return try CSVReader.load(data: d, tsv: false, sheetName: "S", options: o, progress: nil)135        }136        let text = "nom,ville\nZoé,Québec\n日本,東京 😀\n"137        let expected = [["nom", "ville"], ["Zoé", "Québec"], ["日本", "東京 😀"]]138        #expect(grid(try load(Data([0xEF, 0xBB, 0xBF]) + Data(text.utf8)).workbook) == expected)139        #expect(grid(try load(Data([0xFF, 0xFE]) + text.data(using: .utf16LittleEndian)!).workbook) == expected)140        #expect(grid(try load(Data([0xFE, 0xFF]) + text.data(using: .utf16BigEndian)!).workbook) == expected)141        // UTF-16 without BOM is recognised from its NUL pattern142        let ascii = "name,city\nZoe,Quebec\nBob,Paris\n"143        #expect(grid(try load(ascii.data(using: .utf16LittleEndian)!).workbook) == [["name", "city"], ["Zoe", "Quebec"], ["Bob", "Paris"]])144        #expect(grid(try load(ascii.data(using: .utf16BigEndian)!).workbook) == [["name", "city"], ["Zoe", "Quebec"], ["Bob", "Paris"]])145        // invalid UTF-8 falls back to Windows-1252 and says so146        let latin = "nom,prix\nZoé,10 €\n".data(using: .windowsCP1252)!147        let r = try load(latin)148        #expect(grid(r.workbook) == [["nom", "prix"], ["Zoé", "10 €"]])149        #expect(r.issues.contains { $0.feature == "Encoding" })150        #expect(grid(try load("é".data(using: .isoLatin1)!, .latin1).workbook) == [["é"]])151        #expect(CSVReader.isValidUTF8(Data("é日😀".utf8)) && !CSVReader.isValidUTF8(Data([0xC3])) && !CSVReader.isValidUTF8(Data([0xED, 0xA0, 0x80])) && !CSVReader.isValidUTF8(Data([0xC0, 0xAF])))152    }153154    @Test func typeInference() throws {155        let wb = try csv("12,-3.5,1e3,+7,.5,1.5E-3\n\"1,234.50\",50%,TRUE,false,#N/A,#DIV/0!\n2024-02-29,2024-02-29T13:30:15,2024-02-29 06:00,12:30,007,12345678901234567890\n=1+1,abc,1.2.3,1e,--5,\"  42  \"\n0,0.0,-0,1e400,1_000,12abc")156        let s = wb.sheets[0]157        #expect((0..<6).map { s.value(0, $0) } == [.number(12), .number(-3.5), .number(1000), .number(7), .number(0.5), .number(0.0015)])158        #expect(s.value(1, 0) == .number(1234.5) && s.value(1, 1) == .number(0.5) && wb.styles[s.cell(1, 1)!.style].numberFormat == "0.00%")159        #expect(s.value(1, 2) == .bool(true) && s.value(1, 3) == .bool(false) && s.value(1, 4) == .error(.na) && s.value(1, 5) == .error(.div0))160        let d = DateSerial.serial(year: 2024, month: 2, day: 29)161        #expect(s.value(2, 0) == .number(d) && wb.styles[s.cell(2, 0)!.style].numberFormat == "yyyy-mm-dd")162        #expect(abs((s.value(2, 1).numberValue ?? 0) - (d + (13 * 3600 + 30 * 60 + 15) / 86400.0)) < 1e-9 && NumberFormats.kind(of: wb.styles[s.cell(2, 1)!.style].numberFormat) == .dateTime)163        #expect(s.value(2, 2) == .number(d + 0.25) && s.value(2, 3) == .number(12.5 / 24))164        #expect(s.value(2, 4) == .text("007") && s.value(2, 5) == .text("12345678901234567890"))165        #expect(s.value(3, 0) == .text("=1+1") && s.cell(3, 0)?.formula == nil)                    // no formula injection by default166        #expect(s.value(3, 1) == .text("abc") && s.value(3, 2) == .text("1.2.3") && s.value(3, 3) == .text("1e") && s.value(3, 4) == .text("--5"))167        #expect(s.value(3, 5) == .number(42))168        #expect(s.value(4, 0) == .number(0) && s.value(4, 1) == .number(0) && s.value(4, 2) == .number(0) && s.value(4, 3) == .text("1e400") && s.value(4, 4) == .text("1_000") && s.value(4, 5) == .text("12abc"))169        // options170        let raw = try csv("1,TRUE,2024-01-01", { $0.csv.inferTypes = false })171        #expect((0..<3).map { raw.sheets[0].value(0, $0) } == [.text("1"), .text("TRUE"), .text("2024-01-01")])172        let quoted = try csv("\"1\",2", { $0.csv.quotedFieldsAreText = true })173        #expect(quoted.sheets[0].value(0, 0) == .text("1") && quoted.sheets[0].value(0, 1) == .number(2))174        let zeros = try csv("007", { $0.csv.keepLeadingZeros = false })175        #expect(zeros.sheets[0].value(0, 0) == .number(7))176        let f = try csv("\"=SUM(1,2)\",=bad((", { $0.csv.parseFormulas = true })177        #expect(f.sheets[0].cell(0, 0)?.formula?.expr.formulaText() == "SUM(1,2)" && f.sheets[0].value(0, 1) == .text("=bad(("))178        // exactness of the fast path against the standard conversion179        var rng = SplitMix64(seed: 3)180        for _ in 0..<2000 {181            let v = Double(Int.random(in: -999_999_999...999_999_999, using: &rng)) / pow(10, Double(Int.random(in: 0...9, using: &rng)))182            let t = "\(v)"183            guard !t.contains("e") else { continue }184            #expect(try csv(t + ",x").sheets[0].value(0, 0) == .number(Double(t)!), "\(t)")185        }186    }187188    @Test func decimalCommaLocales() throws {189        // detected with a semicolon delimiter190        let wb = try csv("produit;prix;qté\nA;1,5;2\nB;1.234,56;3\nC;12;4,25\n")191        #expect(cellValue(wb, 0, "B2") == .number(1.5) && cellValue(wb, 0, "B3") == .number(1234.56) && cellValue(wb, 0, "C4") == .number(4.25))192        // English numbers in a semicolon file stay English193        let en = try csv("a;b\n1.5;2.25\n3.75;4\n")194        #expect(cellValue(en, 0, "A2") == .number(1.5) && cellValue(en, 0, "A3") == .number(3.75))195        // forced196        let forced = try csv("\"1,5\",\"2.000\"", { $0.csv.decimalComma = true })197        #expect(cellValue(forced, 0, "A1") == .number(1.5) && cellValue(forced, 0, "B1") == .number(2000))198        let dot = try csv("1,5;2.5", { $0.csv.decimalComma = false; $0.csv.delimiter = ";" })199        #expect(cellValue(dot, 0, "A1") == .text("1,5") && cellValue(dot, 0, "B1") == .number(2.5))200    }201202    @Test func sizeLimitsPointToTheDataEngine() throws {203        let text = (0..<50).map { "r\($0),\($0)" }.joined(separator: "\n")204        do { _ = try csv(text, { $0.csv.maxRows = 20 }); Issue.record("limit ignored") }205        catch let e as SheetIOError {206            guard case .tooLarge(let m) = e else { Issue.record("\(e)"); return }207            #expect(m.contains("data engine") && m.contains("20"))208        }209        #expect(try csv(text, { $0.csv.maxRows = 50 }).sheets[0].cells.count == 100)210        #expect(throws: SheetIOError.self) { _ = try csv("a,b,c,d", { $0.csv.maxColumns = 3 }) }211    }212213    @Test func writerOptions() throws {214        let wb = Workbook()215        let s = wb.addSheet(named: "Out")216        let other = wb.addSheet(named: "Other")217        setCell(other, "A1", .number(1))218        var date = CellStyle(); date.numberFormat = "yyyy-mm-dd"219        let dateID = wb.styles.intern(date)220        setCell(s, "A1", .text("name")); setCell(s, "B1", .text("va,lue")); setCell(s, "C1", .text("say \"x\""))221        setCell(s, "A2", .number(1.5)); setCell(s, "B2", .bool(true)); setCell(s, "C2", .error(.na)); setCell(s, "E2", .text("multi\nline"))222        setFormula(s, "A3", "A2*2", cached: .number(3)); setCell(s, "B3", .number(45351), style: dateID); setCell(s, "C3", .text(" padded "))223        func out(_ configure: (inout CSVWriteOptions) -> Void = { _ in }, tsv: Bool = false) throws -> (String, [CompatibilityIssue]) {224            var o = CSVWriteOptions(); o.sheetIndex = 0225            configure(&o)226            let (d, i) = try CSVWriter.data(wb, tsv: tsv, options: o)227            return (String(decoding: d, as: UTF8.self), i)228        }229        let (text, issues) = try out()230        #expect(text == "name,\"va,lue\",\"say \"\"x\"\"\",,\r\n1.5,TRUE,#N/A,,\"multi\nline\"\r\n3,2024-02-29,\" padded \",,\r\n")231        #expect(issues.contains { $0.feature == "Formulas" && $0.severity == .loss } && issues.contains { $0.feature == "Sheets" && $0.severity == .loss })232        #expect(try out({ $0.delimiter = ";"; $0.lineEnding = .lf; $0.decimalComma = true }).0.hasPrefix("name;va,lue;\"say \"\"x\"\"\";;\n1,5;TRUE;"))233        #expect(try out({ $0.quoting = .all; $0.lineEnding = .lf }).0.hasPrefix("\"name\",\"va,lue\",\"say \"\"x\"\"\",,\n\"1.5\",\"TRUE\""))234        #expect(try out({ $0.quoting = .nonNumeric; $0.lineEnding = .lf }).0.contains("\n1.5,\"TRUE\",\"#N/A\""))235        #expect(try out(tsv: true).0.hasPrefix("name\tva,lue\t\"say \"\"x\"\"\"\t\t\r\n"))236        let formatted = try out({ $0.formatter = { cell, style in "<\(cell.computed.description)|\(style.numberFormat)>" }; $0.lineEnding = .lf }).0237        #expect(formatted.contains("<1.5|General>") && formatted.contains("<45351|yyyy-mm-dd>"))238        // encodings239        func bytes(_ e: TextEncoding) throws -> Data { var o = CSVWriteOptions(); o.encoding = e; o.sheetIndex = 1; return try CSVWriter.data(wb, tsv: false, options: o).0 }240        setCell(other, "A1", .text("é€"))241        #expect(try bytes(.utf8BOM).prefix(3) == Data([0xEF, 0xBB, 0xBF]))242        #expect(try bytes(.utf16LE).prefix(2) == Data([0xFF, 0xFE]) && (try bytes(.utf16BE)).prefix(2) == Data([0xFE, 0xFF]))243        #expect(try bytes(.windows1252) == Data([0xE9, 0x80, 0x0D, 0x0A]))244        setCell(other, "A1", .text("日本"))245        var o = CSVWriteOptions(); o.encoding = .latin1; o.sheetIndex = 1246        #expect(try CSVWriter.data(wb, tsv: false, options: o).1.contains { $0.feature == "Encoding" && $0.severity == .loss })247        // every encoding reads back248        setCell(other, "A1", .text("Zoé"))249        for e in [TextEncoding.utf8, .utf8BOM, .utf16LE, .utf16BE, .windows1252] {250            let back = try CSVReader.load(data: try bytes(e), tsv: false, sheetName: "S", options: LoadOptions(), progress: nil).workbook251            #expect(cellValue(back, 0, "A1") == .text("Zoé"), "\(e)")252        }253    }254255    @Test func randomRoundTripThroughFiles() throws {256        let dir = Scratch.dir("csv")257        defer { try? FileManager.default.removeItem(at: dir) }258        var g = RandomWorkbook(seed: 11)259        let texts = ["plain", "with,comma", "with;semi", "quote\"inside", "multi\nline", "crlf\r\ninside", "tab\there", " lead", "trail ", "é日😀", "#notanerror", "TRUEish", "1x"]260        for (format, ext) in [(FileFormat.csv, "csv"), (.tsv, "tsv")] {261            let wb = Workbook(sheetNames: ["data"])262            for r in 0..<300 { for c in 0..<8 where g.bool(0.8) {263                let v: Value264                switch g.int(0...3) {265                case 0: v = .number(g.double())266                case 1: v = .text(g.pick(texts))267                case 2: v = .bool(g.bool())268                default: v = .number(Double(g.int(-10_000...10_000)))269                }270                wb.sheets[0].cells.set(row: r, col: c, Cell(value: v))271            } }272            let url = dir.appendingPathComponent("data.\(ext)")273            try WorkbookWriter.save(wb, to: url, format: format)274            #expect(FileFormat.detect(url: url) == format)275            var progress = [Double]()276            let back = try WorkbookReader.load(url: url, progress: { progress.append($0) })277            #expect(back.workbook.sheets[0].name == "data" && progress.last == 1)278            var o = WorkbookCompareOptions(); o.styles = false; o.layout = false; o.features = false279            let diff = WorkbookDiff.differences(wb, back.workbook, options: o)280            #expect(diff.isEmpty, "\(ext): \(diff.prefix(5))")281        }282    }283284    @Test(.enabled(if: Tools.hasPython)) func agreesWithPandas() throws {285        let dir = Scratch.dir("pandas")286        defer { try? FileManager.default.removeItem(at: dir) }287        // pandas writes, we read288        let script = """289        import sys, json, pandas as pd290        df = pd.DataFrame({"name": ["a,b", 'q"q', "multi\\nline", "é日", "  pad "], "n": [1, -2, 3, 40000000, 5], "x": [0.1, 2.5e-7, -1e22, 3.0, 1/3], "flag": [True, False, True, True, False]})291        df.to_csv(sys.argv[1], index=False)292        print(json.dumps(df.values.tolist()))293        back = pd.read_csv(sys.argv[2])294        print(json.dumps(back.values.tolist()))295        """296        let ours = dir.appendingPathComponent("ours.csv")297        let wb = Workbook(sheetNames: ["s"])298        let rows: [[Value]] = [[.text("k"), .text("v"), .text("t")], [.number(1), .number(0.1), .text("a,b")], [.number(2), .number(-1e-9), .text("x\"y")], [.number(3), .number(123456.789), .text("line\nbreak")]]299        for (r, row) in rows.enumerated() { for (c, v) in row.enumerated() { wb.sheets[0].cells.set(row: r, col: c, Cell(value: v)) } }300        try WorkbookWriter.save(wb, to: ours, format: .csv)301        let theirs = dir.appendingPathComponent("pandas.csv")302        let res = try Tools.run(Tools.python, ["-I", "-c", script, theirs.path, ours.path])303        #expect(res.status == 0, "\(res.err)")304        let lines = res.out.split(separator: "\n")305        let expected = try JSONSerialization.jsonObject(with: Data(lines[0].utf8)) as! [[Any]]306        let got = try WorkbookReader.load(url: theirs).workbook307        for (r, row) in expected.enumerated() {308            #expect(cellValue(got, 0, "A\(r + 2)").textValue == row[0] as? String)309            #expect(cellValue(got, 0, "B\(r + 2)").numberValue == (row[1] as? NSNumber)?.doubleValue)310            #expect(cellValue(got, 0, "C\(r + 2)").numberValue == (row[2] as? NSNumber)?.doubleValue, "row \(r)")311            #expect(cellValue(got, 0, "D\(r + 2)") == .bool((row[3] as? Bool) ?? false))312        }313        // we write, pandas reads314        let seen = try JSONSerialization.jsonObject(with: Data(lines[1].utf8)) as! [[Any]]315        for (r, row) in seen.enumerated() {316            #expect((row[0] as? NSNumber)?.doubleValue == rows[r + 1][0].numberValue && (row[1] as? NSNumber)?.doubleValue == rows[r + 1][1].numberValue)317            #expect(row[2] as? String == rows[r + 1][2].textValue)318        }319    }320}321
Files main
Menu

Theme