迟来的游戏,但我不可能是唯一一个因为带有字节顺序标记的文本文件而烦恼于我的SQL导入失败的程序员。几乎没有"Stack问题触及这个问题 - 这是其中一个最接近的问题 - 所以我在这里发布了一个重叠的答案。
我说"重叠"是因为下面的代码解决的问题略有不同 - 主要目的是为具有异构文件集合的文件夹编写模式文件 - 但BOM处理段明显标记。
关键功能是我们遍历文件夹中的所有".csv"文件,并使用第一个四个字节的快速嗅探测试每个文件:只有在看到一个字节顺序标记时,我们才会去掉它。
之后,我们使用原始的C语言低级文件处理代码进行工作。我们必须使用字节数组,因为
在VBA中进行的所有其他操作都会将字节顺序标记嵌入字符串变量的结构中。
所以,没有更多的adodb,这就是代码:
在schema.ini文件中设置文本文件的BOM处理代码:
Public Sub SetSchema(strFolder As String)
On Error Resume Next
' 将Schema.ini文件写入数据文件夹。如果我们没有注册表权限来设置正确的“ImportMixedTypes = Text”注册表值,则这是必要的,
' 这将覆盖IMEX = 1。该代码还检查ANSI或UTF-8和UTF-16文件,并应用可用的CharacterSet(UNICODE | ANSI)设置,
' 并使用可怕的黑客技巧。不支持基于OEM代码页定义的文本:需要进一步编码。
' ...如果我们看到它们,我们会剥离字节顺序标记 - OLEDB SQL提供程序无法处理UTF-16或UTF-8文件中的BOM
' 未实现:处理制表符分隔文件或其他分隔符。代码假定具有列的标题行,指定“扫描所有行”,并且如果数据类型混合,则强制执行“将列读为文本”。
Dim strSchema As String
Dim strFile As String
Dim hndFile As Long
Dim arrFile() As Byte
Dim arrBytes(0 To 4) As Byte
If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
' 当您使用通配符调用Dir()时,它是一个迭代器函数:
strFile = VBA.FileSystem.Dir(strFolder & "*.csv")
Do While Len(strFile) > 0
hndFile = FreeFile
Open strFolder & strFile For Binary As #hndFile
Get #hndFile, , arrBytes
Close #hndFile
strSchema = strSchema & "[" & strFile & "]" & vbCrLf
strSchema = strSchema & "Format=CSVDelimited" & vbCrLf
strSchema = strSchema & "ImportMixedTypes=Text" & vbCrLf
strSchema = strSchema & "MaxScanRows=0" & vbCrLf
If arrBytes(2) = 0 Or arrBytes(3) = 0 Then ' 这是一个黑客技巧
strSchema = strSchema & "CharacterSet=UNICODE" & vbCrLf
Else
strSchema = strSchema & "CharacterSet=ANSI" & vbCrLf
End If
strSchema = strSchema & "ColNameHeader = True" & vbCrLf
strSchema = strSchema & vbCrLf
' BOM处理 - 字节顺序标记会混淆OLEDB文本驱动程序:
If arrBytes(0) = &HFE And arrBytes(1) = &HFF _
Or arrBytes(0) = &HFF And arrBytes(1) = &HFE Then
hndFile = FreeFile
Open strFolder & strFile For Binary As #hndFile
ReDim arrFile(0 To LOF(hndFile) - 1)
Get #hndFile, , arrFile
Close #hndFile
BigReplace arrFile, arrBytes(0) & arrBytes(1), ""
hndFile = FreeFile
Open strFolder & strFile For Binary As #hndFile
Put #hndFile, , arrFile
Close #hndFile
Erase arrFile
ElseIf arrBytes(0) = &HEF And arrBytes(1) = &HBB And arrBytes(2) = &HBF Then
hndFile = FreeFile
Open strFolder & strFile For Binary As #hndFile
ReDim arrFile(0 To LOF(hndFile) - 1)
Get #hndFile, , arrFile
Close #hndFile
BigReplace arrFile, arrBytes(0) & arrBytes(1) & arrBytes(2),
如果您知道一个字节数组可以分配给VBA.String,反之亦然,那么代码就更容易理解。BigReplace()函数是一种绕过VBA低效字符串处理的方法,特别是分配:如果您以其他方式处理大文件,将会导致严重的内存和性能问题。