CSV 接口 - VBA 的 CSV 解析器。稳定、高效、灵活且符合标准
为了满足从 VBA 直接将信息导入 CSV 文件到内存的需求，并且要高效、安全、稳定，就需要专门的库来确保易用性和数据完整性。
引言
对许多用户来说，设计一个 CSV 库似乎是一件不幸的事情，这是基于存在诸如 Excel 的 Power Query 和 Microsoft Access 强大的 SQL 语言等工具的前提。然而，如果我们的需求是从 CSV 文件读取信息并将其存储在内存中，那么在 Excel 中使用 Power Query 会导致以下步骤：建立到文件的物理连接，将数据转储到电子表格，将数据复制到 VBA 数组，删除转储到工作表的数据，然后删除到文件的连接。显然，对于上述情况，最好的选择是使用一个允许我们将数据直接从 CSV 文件转储到内存的库。这正是促使我设计 CSV 接口 的需求。
功能
- 符合 RFC-4180 规范及更多。该解析器可适应各种情况，尽管最好遵循规范：支持带引号和多行字段，用户可以跳过注释行和空行。
- 稳定。完全经过测试驱动开发 (TDD) 的库，（64/64 个测试通过），包含 650 多行测试代码。请参阅 Tim Hall 的 VBA 测试库。
- 内存友好。CSV/TSV 文件使用自定义流技术进行处理，一次仅占用 0.5MB 内存。
- 健壮。解析器和写入器接受 类 Unix 的引号转义序列。
- 易于使用。几行代码即可完成工作！
- 自动分隔符猜测。如果您忘记了文件配置，请不必担心。该接口拥有一个可靠的策略来猜测分隔符！
- 高度可配置。用户可以配置解析器以处理各种 CSV 文件。
- CSV 数据过滤。仅保存满足指定要求的 CSV 数据。
- 类似 SQL 查询 CSV 文件。添加您自己的逻辑来模拟 SQL 查询，并通过条件（=, <>, >=, <=, AND, OR）过滤数据。
- 灵活。仅导入给定文件中的特定记录范围，按索引或名称导入字段（列），以顺序模式读取记录。
- 动态类型支持。将 CSV 数据字段转换为所需的 VBA 数据类型。
- 数据排序。使用超快速的 Yaroslavskiy 双轴快速排序（类似 Java）对导入的 CSV 数据进行排序。
- Microsoft Access 兼容。该库有一个版本，适合那些习惯通过 DAO 数据库工作的人，从这里下载。
RFC-4180 规范
目前，CSV 文件没有定义明确的标准，存在多种实现和变体。尽管如此，还是存在 RFC-4180 等规范，它定义了这些文件的基本结构，而该标准的其他变体则在 美国国会图书馆 的规范中定义。
根据规范，CSV 可以包含
- 包含字段分隔符字符、记录分隔符字符（多行字段）或转义字符（通常是双引号）的字段（列）。此类字段必须使用转义字符进行封装（“转义”）。转义字段中的文字转义字符也必须通过复制每个转义字符或在每个转义字符前加上反斜杠（Unix 风格）进行转义。
- 注释行或空记录（行/线）。
- 制表符 (\t) 或分号 (;) 作为字段分隔符，以及 CRLF (\r\n)、CR (\r) 和 LF (\n) 中的一个字符作为记录分隔符。在特殊情况下，我们可能会发现 CSV 文件记录分隔符混合的情况。
该库支持撇号 (') 作为转义字符，但受限于规范，包含俚语 "isn't" 的字段应存储在 CSV 文件中为 "isn''t"，这会造成混淆。在这种情况下，用户可以选择使用 Unix 转义机制，并将俚语存储为 "isn\'t"，这是一种更易读的选项。
一个示例，展示了符合 RFC-4180 规范的 CSV 结构如下
"rec1, fld1",,"rec1"",""fld3.1
"",
fld3.2","rec1
fld4"
"rec2, fld1.1
fld1.2","rec2 fld2.1""fld2.2""fld2.3","",rec2 fld4
上面的示例应生成下表中显示的字段和记录（为方便演示，添加了标题）
| 第 1 列 | 第 2 列 | 第 3 列 | 第 4 列 |
rec1, fld1 |
|
| |
|
| rec2 fld2.1"fld2.2"fld2.3 | rec2 fld4 |
在表中，记录 #1 的字段 #3 是多行的，记录 #1 的字段 #4 也是如此，记录 #2 的字段 #1 也是如此。根据 RFC-4180 规范，文字转义字符会通过重复自身来转义，因此记录 #1 的字段 #3 和记录 #2 的字段 #2 包含必须由解析器进行转义的转义字符。另一方面，许多字段包含字段分隔符本身，这必须由解析器处理。
因此，这个示例是测试任何 CSV 解析器的完美场景，并且它已经包含在 VBA CSV 接口的测试用例中。
解决方案核心
许多作者建议避免将文件块加载到缓冲区中，以减少内存占用，原因是这种替代方案可能会非常复杂。尽管如此，缓冲是实现能够高效处理 CSV 文件的解析器的途径。
Using the Code
本节将尝试分析 CSV 接口的所有功能。
导入整个 CSV 文件
Sub CSVimport()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
With csvinf
.ImportFromCSV .parseConfig ' Import the CSV to internal object
End With
End Sub
现在假设从文件 "Sample.csv" 中，用户只想导入特定范围的记录。可以编写如下所示的代码
Sub CSVimportRecordsRange()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
.startingRecord = 10 ' Start import on the tenth record
.endingRecord = 20 ' End of importation in the 20th record
End With
With csvinf
.ImportFromCSV .parseConfig ' Import the CSV to internal object
End With
End Sub
如果用户想对导入的数据进行排序，可以编写如下代码
Sub CSVimportAndSort()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
With CSVint
.ImportFromCSV .parseConfig ' Import the CSV to internal object
.Sort SortColumn:=1, Descending:=True ' Sort imported data on first column
End With
End Sub
CSV 数据主要被视为文本字符串，如果用户想对从给定文件中获取的数据进行一些计算怎么办？在这种情况下，用户可以更改解析器的行为以在动态类型模式下工作。这是一个例子
Sub CSVimportAndTypeData()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
.dynamicTyping = True ' Enable dynamic typing mode
'@---------------------------------------------------------
' Configure dynamic typing
.DefineTypingTemplate TypeConversion.ToDate, _
TypeConversion.ToLong, _
TypeConversion.ToDouble
.DefineTypingTemplateLinks 6, _
7, _
10
' The dynamic typing mode will perform the following:
' * Over column 6 ---> String To Date data Type conversion
' * Over column 7 ---> String To Long data Type conversion
' * Over column 10 ---> String To Double data Type conversion
End With
With CSVint
.ImportFromCSV .parseConfig ' Import the CSV to internal object
End With
End Sub
转义字符可以根据枚举定义为其中一个
Sub SetEscapeChar()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.escapeToken = EscapeTokens.DoubleQuotes ' 2 = ["] (Default)
'.escapeToken = EscapeTokens.Apostrophe ' 1 = [']
'.escapeToken = EscapeTokens.Tilde ' 3 = [~]
End With
End Sub
一旦数据被导入并保存到内部对象中，用户就可以像访问标准 VBA 数组一样访问它。一个例子是
Sub LoopData(ByRef CSVint As CSVinterface)
With CSVint
Dim iCounter As Long
Dim cRecord() As Variant ' Records are stored as a one-dimensional array.
Dim cField As Variant
For iCounter = 0 To CSVint.count - 1
cRecord() = .item(iCounter) ' Retrieves a record
cField = .item(iCounter, 2) ' Retrieves the 2nd field of the current record
Next
End With
End Sub
然而，将数据存储在 VBA 数组以外的容器中有时是不利的。当需要将信息写入 Excel 自有对象（如电子表格）或 VBA 用户窗体（如列表框）时，这一点尤其明显，因为列表框可以使用数组在一个指令中进行填充。然后，用户可以使用如下代码从内部对象复制信息
Sub DumpData(ByRef CSVint As CSVinterface)
Dim oArray() As Variant
With CSVint
.DumpToArray oArray ' Dump the internal data into a two-dimensional array
.DumpToJaggedArray oArray ' Dump the internal data into a jagged array
.DumpToSheet ' Dump the internal data into a new sheet
' using ThisWorkbook
'@-------------------------------------------------------------------
' *NOTE: ONLY AVAILABLE FOR THE ACCESS VERSION OF THE CSV INTERFACE
' Dump the internal data into the Table1 in oAccessDB database.
' The method would create indexes in the 2nd and 3th fields.
.DumpToAccessTable oAccessDB, _
"Table1", _
2, 3
End With
End Sub
到目前为止，在所讨论的示例中，用户被允许在两个操作之间选择
- 导入 CSV 文件中包含的所有记录。
- 导入一个记录集，从记录 X 开始到记录 Y 结束。
在这两种选项中，用户都必须导入文件中存在的所有字段（列）。大多数 CSV 文件解析器只提供第一种选项，但如果用户只想保存相关信息怎么办？如果用户打算只将满足特定要求条件的记录存储在内存中又该如何？
用户可能需要从 CSV 文件导入 12 列中的 2 列，在这种情况下，用户可以使用类似以下的代码
Sub CSVimportDesiredColumns()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
With CSVint
.ImportFromCSV .parseConfig, _
1, "Revenue" ' Import 1st and "Revenue" fields ONLY
End With
End Sub
好的，现在想象一下，用户想在保存数据之前应用一些逻辑，在这种情况下，他们可以使用顺序读取器逐条遍历 CSV 文件中的记录，如下例所示
Sub CSVsequentialImport()
Dim CSVint As CSVinterface
Dim csvRecord As ECPArrayList
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
With CSVint
.OpenSeqReader .parseConfig, _
1, "Revenue" ' Import the 1st and "Revenue" fields using
' seq. reader
Do
Set csvRecord = .GetRecord
'//////////////////////////////////////////////
'Implement your logic here
'//////////////////////////////////////////////
Loop While Not csvRecord Is Nothing ' Loop until the end of the file is reached
End With
End Sub
是否有办法一次性顺序获取一组记录而不是一条记录？目前，没有内置的方法可以像上面的示例那样用一个指令完成，但只需几行额外的代码和库提供的工具，就可以实现这个目标。下面的示例说明了如何流式处理 CSV 文件
Sub CSVimportChunks()
Dim CSVint As CSVinterface
Dim StreamReader As ECPTextStream
Set CSVint = New CSVinterface
With CSVint.parseConfig
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
Set StreamReader = New ECPTextStream
With StreamReader
.endStreamOnLineBreak = True ' Instruct to find line breaks
.OpenStream "C:\Sample.csv" ' Connect to CSV file
Do
.ReadText ' Read a CSV chunk
CSVint.ImportFromCSVString .bufferString, _
CSVint.parseConfig, _
1, "Revenue" ' Import a set of records
'//////////////////////////////////////
'Implement your logic here
'//////////////////////////////////////
Loop While Not .atEndOfStream ' Continue until reach
' the end of the CSV file.
End With
Set CSVint = Nothing
Set StreamReader = Nothing
End Sub
到目前为止，已经概述了如何顺序导入 CSV 文件中的记录，下一个示例展示了如何以类似 SQL 的方式根据用户设定的标准来过滤记录
Sub QueryCSV(path As String, ByVal keyIndex As Long, queryFilters As Variant)
Dim CSVint As CSVinterface
Dim CSVrecords As ECPArrayList
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv"
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
If path <> vbNullString Then
'@-----------------------------------------------
' The following instruction will filter the data
' on the keyIndex(th) field.
Set CSVrecords = CSVint.GetCSVsubset(path, _
queryFilters, _
keyIndex)
CSVint.DumpToSheet DataSource:=CSVrecords ' Dump result to new WorkSheet
Set CSVint = Nothing
Set CSVrecords = Nothing
End If
End Sub
在某些情况下，我们可能会遇到一个 CSV 文件，其中包含 vbCrLf、vbCr 和 vbLf 的组合作为记录分隔符。这可能由于多种原因发生，但最常见的是在添加数据到现有 CSV 文件时，没有检查先前存储信息的配置。这些情况会破坏许多健壮的 CSV 解析器的逻辑，包括每周下载 737K 次的 Papa Parse 的演示。下一个示例展示了用户如何导入具有混合换行符作为记录分隔符的 CSV 文件，该选项使用了 parseConfig 对象的 turnStreamRecDelimiterToLF 属性来处理这些特殊的 CSV 文件。
Sub ImportMixedLineEndCSV()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Mixed Line Breaks.csv"
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
.turnStreamRecDelimiterToLF = True ' All delimiters will be turned into vbLf
End With
With CSVint
.ImportFromCSV .parseConfig
End With
Set CSVint = Nothing
End Sub
在以上所有示例中，都做了一个隐含的假设，那就是用户知道要导入的 CSV 文件的配置，所以问题来了：用户是否有可能不知道要导入的文件的配置？这当然是可能的，那么 CSV 接口在这种情况下如何提供帮助？
该工具包含一个用于猜测字段分隔符、记录分隔符和转义字符的实用程序。这可以通过类似以下的代码完成
Sub DelimitersGuessing()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
End With
With CSVint
.GuessDelimiters .parseConfig ' Try to guess delimiters and save to internal
' parser configuration object.
'@--------------------------------------------------------------
' *NOTE: the user can also create a custom configuration object
' and try to guess the delimiter with it.
End With
End Sub
关注点
猜测分隔符或确定 CSV 文件的方言，可能被看作是一个简单的问题，但事实并非如此。即使在撰写本文时，该主题仍有开放的研究。例如，G.J.J. van den Burg 的研究最终实现了一个名为 CleverCSV 的健壮系统，该系统允许确定 CSV 文件的方言。研究人员提出的解决方案通过评估行的模式来确定 CSV 的方言，以便检查其一致性以及可以转换为系统中既定数据类型的字段数量。然后，当尝试定义使用未分段数据的文件的方言时，其阿喀琉斯之踵就出现了，如在其 issue #37 和 issue #35 中所述。
# This CSV has caused CleaverCSV issue 37
# The parser guess delimiter [:] instead of [,]
"{""fake"": ""json"", ""fake2"":""json2""}",13:31:38,06:00:04+01:00
"{""fake"": ""json"", ""fake2"":""json2""}",22:13:29,14:20:11+02:00
"{""fake"": ""json"", ""fake2"":""json2""}",04:37:27,22:04:28+03:00
"{""fake"": ""json"", ""fake2"":""json2""}",04:25:28,23:12:53+01:00
"{""fake"": ""json"", ""fake2"":""json2""}",21:04:15,08:23:58+02:00
"{""fake"": ""json"", ""fake2"":""json2""}",10:37:03,11:06:42+05:30
"{""fake"": ""json"", ""fake2"":""json2""}",10:17:24,23:38:47+06:00
"{""fake"": ""json"", ""fake2"":""json2""}",00:02:51,20:04:45-06:00
另一方面，Papa Parse 在面对逗号 ((',')) 用作十进制分隔符而分号 ((';')) 用作字段分隔符的 CSV 文件时，无法区分字段分隔符。以下是一些阻止 Matt Holt 的强大代码正确确定分隔符的 CSV 文件
Prüfung1;Prüfung2;Prüfung3
1,5;33,33;15,55
2,5;25,44;30,1
3,5;16,67;45,2
4,5;12;60,3
'Neroductions Group';£ 1,80;£ 9000,50
'Hatchworks Ltd.';£ 2,00;£ 100000,30
id;value
1;3,4,5
2;6,7,8
3;9,10,11
4;13,14,15
5;"15,16,17;also;that"
这是否意味着我们无法完全确定方言或猜测 CSV 的分隔符？答案是，对于由分隔符和转义字符的随机配置生成的表，没有万无一失的解决方案来消除歧义。无论如何，在 CSV 接口 中实现的字段、记录和表的统计评分方法，完美地处理了导致 CleverCSV 和 Papa Parse 出现问题的那些文件。
在 CSV 接口的情况下，当两种方言产生具有相同一致性的表时，歧义问题变得无法解决，这种情况可能发生在没有标题的 CSV 文件中，如下所示
1|2;3|4;5
3;a|c;6|6
对于使用垂直线作为字段分隔符的方言和使用分号作为该目的的方言，CSV 接口的评分方法将产生完全相同的结果，对于这些特定情况，机器或人工都无法消除歧义。
历史
- 2021年7月11日：初始版本