导入制表符分隔的txt到使用VBA Access表 [英] import tab-delimited txt into Access table using VBA

问题描述

我想导入制表符分隔txt文件到使用VBA Access表。在我的code，我想将其插入到尚未创建的表。

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.

下面是我想这样做。注 - 我是能够使这项工作有一个CSV，没有包括这一点：数据类型：= xlDelimited，标签：= TRUE

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

Sub InsertData()

    'import CSV into temp table
    DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tbl_TEMP", _
    FileName:=FileNameVariable, HasFieldNames:=True, DataType:=xlDelimited, Tab:=True

End Sub

当我运行该块，我得到数据类型以下错误：= xlDelimited，标签：= TRUE

When I run this block, I get the following error on DataType:=xlDelimited, Tab:=True

编译错误：未找到命名参数

Compile error: Named argument not found

我应该如何改变这种以拉制表符分隔的txt文件，所以从TXT每列都有自己的列访问？

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?

推荐答案

正如您从这个专题的其他文章看到，实在是没有导入制表符分隔文本文件的通用方法。所有我见过的其他解决方案的说，你应该导入制表符分隔的文本文件一次，保存导入规范，然后使用该导入规格为所有后续进口。这个问题存在，如果要导入一个不同的制表符分隔文件规范不匹配。

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.

我发现这样做一般（短用滚你自己的$ C $的C FileSystemObject的，分裂的唯一途径（ S，vbTab）等）是创建一个完全通用的规范，所有的255种可能的字段并使用它。它需要一个一次性设置如下：

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:

复制从这里的引擎收录 CSV数据，将其粘贴到文本编辑器，并将其保存为 GenericTabSpecification.csv 。

在Excel中打开该文件，选择所有256行4列，然后点击<大骨节病>控制 + <大骨节病> C 复制。

Open that file in Excel, select all 256 rows and 4 columns, then hit Ctrl+C to copy.

在Access中，开始为文本文件导入向导，然后选择任何制表符分隔文件。 （我们实际上不会导入它。）

In Access, start the import wizard for text files and choose any tab-delimited file. (We won't actually be importing it.)

当你在向导的第一页上，单击高级按钮。

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:

按<大骨节病>控制 + <大骨节病> V 从Excel中的数据粘贴到电网。网格现在应该包含255行。

Hit Ctrl+V to paste the data from Excel into the grid. The grid should now contain 255 rows.

点击另存为...按钮，并命名规范 GenericTabSpecification 。一旦做到这一点，抵消向导。

Click the "Save As..." button and name the specification GenericTabSpecification. Once that is done, cancel out of the wizard.

现在，我们可以用这样的语句从VBA做一个普通的进口

Now we can do a generic import from VBA using a statement like this

DoCmd.TransferText _
        TransferType:=acImportDelim, _
        SpecificationName:="GenericTabSpecification", _
        TableName:="newTable", _
        FileName:="C:\Users\Gord\Desktop\foo.txt", _
        HasFieldNames:=False

这篇关于导入制表符分隔的txt到使用VBA Access表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！