vba 解析非结构化文本文件并将其导入 Microsoft Access（文件具有潜在的分隔符）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19252925/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parse and import unstructured text files into Microsoft Access (file has potential delimiters)
提问by WCD
I have a bunch of text files that I need to import into MS Access (thousands) - can use 2007 or 2010. The text files have categories that are identified in square brackets and have relevant data between the categories - for example:
我有一堆文本文件需要导入到 MS Access（数千个）中 - 可以使用 2007 或 2010。文本文件具有在方括号中标识的类别，并在类别之间具有相关数据 - 例如：
[Location]Tenessee[Location][Model]042200[Model][PartNo]113342A69447B6[PartNo].
[位置]田纳西州[位置][型号]042200[型号][零件编号]113342A69447B6[零件编号].
I need to capture both the categories and the data between them and import them into Access - the categories to one table, the data to another. There are hundreds of these categories in a single file and the text file has no structure - they are all run together as in the example above. The categories in the brackets are the only clear delimiters.
我需要捕获类别和它们之间的数据，并将它们导入 Access - 类别到一个表，数据到另一个。单个文件中有数百个这样的类别，文本文件没有结构——它们都像上面的例子一样一起运行。括号中的类别是唯一明确的分隔符。
Through research on the web I have come up with a script for VBS (I am not locked into VBS, willing to use VBA or another method), but when I run it, I am getting a VBS info window with nothing displaying in it. Any advice or guidance would be most gratefully appreciated (I do not tend to use VBS and VBA) and I thank you.
通过在网上的研究，我想出了一个 VBS 脚本（我没有被锁定在 VBS 中，愿意使用 VBA 或其他方法），但是当我运行它时，我得到了一个 VBS 信息窗口，里面没有任何显示。任何建议或指导将不胜感激（我不倾向于使用 VBS 和 VBA），我感谢你。
The Script:
剧本：
Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Users\testGuy\Documents\dmc_db_test\DMC-TEST-A-00-00-00-00A-022A-D_000 - Copy01.txt", ForReading)
strContents = objFile.ReadAll
objFile.Close
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Global = True
objRegEx.Pattern = "\[.{0,}\]"
Set colMatches = objRegEx.Execute(strContents)
If colMatches.Count > 0 Then
For Each strMatch in colMatches
strMatches = strMatches & strMatch.Value
Next
End If
strMatches = Replace(strMatches, "]", vbCrlf)
strMatches = Replace(strMatches, "[", "")
Wscript.Echo strMatches
回答by Gord Thompson
Regular expressions are wonderful things, but in your case it looks like they might be overkill. The following code uses plain old InStr()to find the [Tags]and parses the file(s) out to a single CSV file. That is, for input files
正则表达式是很棒的东西，但在你的情况下，它们看起来可能有点矫枉过正。以下代码使用普通旧代码InStr()查找[Tags]文件并将其解析为单个 CSV 文件。也就是说，对于输入文件
testfile1.txt:
测试文件1.txt：
[Location]Tennessee[Location][Model]042200[Model][PartNo]113342A69447B6[PartNo]
[Location]Mississippi[Location][Model]042200[Model][SerialNo]3212333222355[SerialNo]
and testfile2.txt:
和 testfile2.txt：
[Location]Missouri[Location][Model]042200[Model][PartNo]AAABBBCCC111222333[PartNo]
...the code will write the following output file...
...代码将写入以下输出文件...
"FileName","LineNumber","ItemNumber","FieldName","FieldValue"
"testfile1.txt",1,1,"Location","Tennessee"
"testfile1.txt",1,2,"Model","042200"
"testfile1.txt",1,3,"PartNo","113342A69447B6"
"testfile1.txt",2,1,"Location","Mississippi"
"testfile1.txt",2,2,"Model","042200"
"testfile1.txt",2,3,"SerialNo","3212333222355"
"testfile2.txt",1,1,"Location","Missouri"
"testfile2.txt",1,2,"Model","042200"
"testfile2.txt",1,3,"PartNo","AAABBBCCC111222333"
...which you can then import into Access (or whatever) and proceed from there. This is VBA code, but it could easily be tweaked to run as a VBScript.
...然后您可以将其导入 Access（或其他）并从那里继续。这是 VBA 代码，但可以轻松调整以作为 VBScript 运行。
Sub ParseSomeFiles()
Const InFolder = "C:\__tmp\parse\in\"
Const OutFile = "C:\__tmp\parse\out.csv"
Dim fso As FileSystemObject, f As File, tsIn As TextStream, tsOut As TextStream
Dim s As String, Lines As Long, Items As Long, i As Long
Set fso = New FileSystemObject
Set tsOut = fso.CreateTextFile(OutFile, True)
tsOut.WriteLine """FileName"",""LineNumber"",""ItemNumber"",""FieldName"",""FieldValue"""
For Each f In fso.GetFolder(InFolder).Files
Debug.Print "Parsing """ & f.Name & """..."
Set tsIn = f.OpenAsTextStream(ForReading)
Lines = 0
Do While Not tsIn.AtEndOfStream
s = Trim(tsIn.ReadLine)
Lines = Lines + 1
Items = 0
Do While Len(s) > 0
Items = Items + 1
tsOut.Write """" & f.Name & """," & Lines & "," & Items
i = InStr(1, s, "]", vbBinaryCompare)
' write out FieldName
tsOut.Write ",""" & Replace(Mid(s, 2, i - 2), """", """""", 1, -1, vbBinaryCompare) & """"
s = Mid(s, i + 1)
i = InStr(1, s, "[", vbBinaryCompare)
' write out FieldValue
tsOut.Write ",""" & Replace(Mid(s, 1, i - 1), """", """""", 1, -1, vbBinaryCompare) & """"
s = Mid(s, i)
i = InStr(1, s, "]", vbBinaryCompare)
' (no need to write out ending FieldName tag)
s = Mid(s, i + 1)
tsOut.WriteLine
Loop
Loop
tsIn.Close
Set tsIn = Nothing
Next
Set f = Nothing
tsOut.Close
Set tsOut = Nothing
Set fso = Nothing
Debug.Print "Done."
End Sub
