使用 VBA 导入带有分隔符的文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15067136/
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
Importing a text with separators using VBA
提问by Gutanoth
I am trying to automate the adding of new text files, which all have the same (known) layout.
我正在尝试自动添加新的文本文件，这些文件都具有相同的（已知）布局。
The columns are separated using tabs (the TAB button). My question is, is it possible to do this in VBA? Like, in the access wizard for importing text files?
使用制表符（TAB 按钮）分隔列。我的问题是，是否可以在 VBA 中执行此操作？例如，在用于导入文本文件的访问向导中？
I am using the DoCmd.TransferTextmethod in VBA
我DoCmd.TransferText在 VBA 中使用该方法
回答by Brad
You'll need to go through the wizard once to make your specification file. TO do this import your text file like normal but before you get too deep into the wizard click on the bottom left, the "Advanced..." button. This is where you make your spec file.
您需要通过向导一次来制作您的规范文件。要执行此操作，请像往常一样导入文本文件，但在深入了解向导之前，请单击左下角的“高级...”按钮。这是您制作规范文件的地方。
Make ll these columns match your input file, data types and all. Be sure to select the {tab}field delimiter and the appropriate text qualifier if you are using one.
使所有这些列与您的输入文件、数据类型和所有内容相匹配。{tab}如果您正在使用字段分隔符和适当的文本限定符，请务必选择其中之一。
Save your spec (which can later be edited by coming back to this same screen and clicking Specs...then saving over your old one)
保存您的规范（稍后可以通过返回同一屏幕并单击Specs...然后保存旧的来对其进行编辑）
Now you can use in VBA like this
现在你可以像这样在 VBA 中使用
DoCmd.TransferText acImportDelim, "your spec name", "destination table name", sourceFilePath
There is a parameter HasFieldNamesthat you'll have to decide if it is trueor falsebased on your file.
有一个参数HasFieldNames，您必须决定它是否是true或false基于您的文件。
回答by Yawar
With the import wizard the downside is that for even the slightest change in file format, you'll have to click through all those steps yet againto get the import working.
使用导入向导的缺点是，即使在文件格式丝毫的改变，你必须通过所有这些步骤，单击再次以获取导入工作。
Check out @Remou's answer in ms Access import table from file in a queryfor a way to do it in straight SQL. I am actually using the same method in a project of mine. I use something like this (see my link for the details):
查看@Remou 在ms Access import table from file in a query 中的回答，以了解在直接 SQL 中执行此操作的方法。我实际上在我的一个项目中使用了相同的方法。我使用这样的东西（有关详细信息，请参阅我的链接）：
insert into MyTable (column-list...)
select (column-list...)
from [data-source-specifications].[file-name]
any-other-clauses...;
Just one caveat. If you put this SQL syntax into a normal Access query object, there's a good chance that Access will mangle it to the point where it won't even be able to open the query object. So compose and save the query in a text file while you try it out in Access. Once the query is tested and working, save it in a VBA subroutine so that Access will run it exactly as is, like so:
只是一个警告。如果将此 SQL 语法放入普通的 Access 查询对象中，则 Access 很有可能会将其破坏到甚至无法打开查询对象的程度。因此，当您在 Access 中试用时，请编写查询并将其保存在文本文件中。查询经过测试并正常工作后，将其保存在 VBA 子例程中，以便 Access 将按原样运行它，如下所示：
sub MyTableImport()
sqlStr = " insert into MyTable (column-list) " ' leave a space at the
sqlStr = sqlStr & "select (column-list...) " ' end of each line of the string
sqlStr = sqlStr & "from [data-source-specifications].[file-name] "
sqlStr = sqlStr & "any-other-clauses... ;"
DoCmd.RunSQL sqlStr
end sub
