使用 MS Access 导入多个文本文件 [英] Using MS Access to import multiple text files

问题描述

我有大约 600 个带有标题的文本文件，我真的不想将它们一个一个地手动导入 MS Access.

I have about 600 text files with headers, and I don't really feel like importing them one by one manually into MS Access.

我不想将文本文件附加到一个 Access 表中.如果我有 600 个文本文件，我希望结果是 600 个 Access 表.

I don't want to append the text files into one Access table. If I have 600 text files, I want the result to be 600 Access tables.

我为此进行了高低搜索，最接近的是一些将我的文本文件附加到一个访问表中的代码.我不想要那个.

I've searched high and low for this, the closest I've come is some code that would take my text files and append them into one access table. I don't want that.

推荐答案

考虑使用 DoCmd.TransferText 命令，该命令遍历文件夹目录中的所有文本文件，并以表示 1-600 的后缀导入它们.

Consider a For/Loop VBA using the DoCmd.TransferText command that iterates through all text files in a folder directory and imports them with suffix to denote 1-600.

Dim FSO as Object, objFolder As Object, objFile as Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder("C:\Path\To\TextFiles")

i = 1
For each objFile in objFolder.Files
  If Right(objFile.Name, 3) = "txt" Then
    DoCmd.TransferText acImportDelim, , "File_" & i, objFolder & "\" & objFile.Name, True
  End if
  i = i + 1
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing

在 TransferText 的空参数中，您可以使用在手动导入文本文件期间创建的预定义规范对象.这允许您命名导入的文本文件的字段、定义数据类型等.

In the empty argument in TransferText you can use a pre-defined specification object which you create during one manual import of the text file. This allows you to name fields, define data types, etc. of the imported text file.

最后，如果所有文件的结构都相同，请考虑再次导入到一个表中，并根据需要使用相关字段的查询将其拆分为 600 个分组.要使用一张表，只需替换 "File_" &i 上面带有表字符串名称的参数:"dataFiles".您节省了表命名空间、更少数据库对象的存储空间，并且总体上有助于更好地组织以及关系模型.

Finally, if all files are structured the same, consider again importing into one table and use queries by relevant fields to break up in the 600 groupings if needed. To use one table, simply replace the "File_" & i argument above with a table string name: "dataFiles". You save on table namespaces, storage from less database objects, and overall helps in better organization as well as the relational model.

这篇关于使用 MS Access 导入多个文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！