使用 VBA 将制表符分隔的 txt 导入 Access 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19962000/
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
import tab-delimited txt into Access table using VBA
提问by sion_corn
I am trying to import a tab-delimited txt file into an Access table using VBA. In my code, I want to insert it into a table that has not yet been created.
我正在尝试使用 VBA 将制表符分隔的 txt 文件导入到 Access 表中。在我的代码中，我想将其插入到尚未创建的表中。
Here is what I tried doing. Note - I was able to make this work with a CSV, and without including this: DataType:=xlDelimited, Tab:=True
这是我尝试做的。注意 - 我能够使用 CSV 完成这项工作，并且不包括以下内容：DataType:=xlDelimited, Tab:=True
Sub InsertData()
'import CSV into temp table
DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tbl_TEMP", _
FileName:=FileNameVariable, HasFieldNames:=True, DataType:=xlDelimited, Tab:=True
End Sub
When I run this block, I get the following error on DataType:=xlDelimited, Tab:=True
当我运行这个块时，我收到以下错误 DataType:=xlDelimited, Tab:=True
Compile error: Named argument not found
编译错误：未找到命名参数
How should I change this in order to pull in the tab-delimited txt file so each column from the txt has its own column in Access?
我应该如何更改它以提取制表符分隔的 txt 文件，以便 txt 中的每一列在 Access 中都有自己的列？
回答by Gord Thompson
As you have seen from the other articles on the topic, there really isn't a generic way to import tab-delimited text files. All of the other solutions I've seen say that you should import the tab-delimited text file once, save the import specification, and then use that import specification for all subsequent imports. The problem there is that if you want to import a different tab-delimited file the specification may not match.
正如您从有关该主题的其他文章中看到的那样，确实没有通用的方法来导入制表符分隔的文本文件。我见过的所有其他解决方案都说您应该导入一次制表符分隔的文本文件，保存导入规范，然后对所有后续导入使用该导入规范。问题在于，如果您想导入不同的制表符分隔文件，则规范可能不匹配。
The only way I've found to do it generically (short of "rolling your own" code using FileSystemObject, Split(s, vbTab), etc.) is to create a completely generic specification for all 255 possible fields and use that. It requires a one-time setup as follows:
我发现一般做到这一点的唯一方法（短的使用“滚你自己的”代码FileSystemObject，Split(s, vbTab)等等）是为所有人创造255个可能的字段一个完全通用的规范和使用。它需要一次性设置，如下所示：
Copy the CSV data from the Pastebin here, paste it into your favorite text editor, and save it as GenericTabSpecification.csv.
从此处的 Pastebin 复制 CSV 数据，将其粘贴到您喜欢的文本编辑器中，然后将其另存为.csv文件GenericTabSpecification.csv。
Open that file in Excel, select all 256 rows and 4 columns, then hit Ctrl+Cto copy.
在 Excel 中打开该文件，选择所有 256 行和 4 列，然后点击Ctrl+C进行复制。
In Access, start the import wizard for text files and choose any tab-delimited file. (We won't actually be importing it.)
在 Access 中，启动文本文件的导入向导并选择任何制表符分隔的文件。（我们实际上不会导入它。）
When you get to the first page in the wizard, click the "Advanced..." button.
当您到达向导的第一页时，单击“高级...”按钮。
In the Import Specification dialog, verify the settings (Field Delimiter, Text Qualifier, etc.) then click the top-left corner of the Field Information grid so all rows are selected:
在“导入规范”对话框中，验证设置（字段分隔符、文本限定符等），然后单击“字段信息”网格的左上角，以便选择所有行：
Hit Ctrl+Vto paste the data from Excel into the grid. The grid should now contain 255 rows.
点击Ctrl+V将 Excel 中的数据粘贴到网格中。网格现在应该包含 255 行。
Click the "Save As..." button and name the specification GenericTabSpecification. Once that is done, cancel out of the wizard.
单击“另存为...”按钮并命名规范GenericTabSpecification。完成后，取消向导。
Now we can do a generic import from VBA using a statement like this
现在我们可以使用这样的语句从 VBA 进行通用导入
DoCmd.TransferText _
TransferType:=acImportDelim, _
SpecificationName:="GenericTabSpecification", _
TableName:="newTable", _
FileName:="C:\Users\Gord\Desktop\foo.txt", _
HasFieldNames:=False
