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