从一个文件夹中的多个文本文件提取数据到Excel工作表 [英] extract data from multiple text files in a folder into excel worksheet

问题描述

我有多个数据表文本文件，与工作中的程序一起使用，需要从中收集值并将其全部组合到电子表格中。

I have multiple "datasheet" text files that are used with a program at work and need to harvest values from them and combine it all into a spreadsheet.

文本文件格式化为：

[File]
    DescText = "1756-IF16H 16 Channel Hart Analog Input Module";
    CreateDate = 04-07-10;
    CreateTime = 10:29;
    Revision = 1.1; 
    HomeURL = "http://www.ab.com/networks/eds/XX/0001000A00A30100.eds";

[Device]
    VendCode = 1;
    VendName = "Allen-Bradley";
    ProdType = 10;
    ProdTypeStr = "Multi-Channel Analog I/O with HART";
    ProdCode = 163;
    MajRev = 1;
    MinRev = 1;
    ProdName = "1756-IF16H/A";
    Catalog = "1756-IF16H/A";
    Icon = "io_brown.ico";

标签在所有文件中是一致的，每行以分号结尾。 ]所以我假设这应该很容易。我需要将DescText，VendCode，ProdType，MajRev，MinRev和ProdName分成不同的列。

The Tags are consistent through all the files and each lines ends with a semicolon [ ; ] so I'm assuming this should be pretty easy. I need to pull "DescText","VendCode","ProdType","MajRev","MinRev",and"ProdName" into separate columns.

100个单独的数据文件，每个都有一个无意义的文件名，所以我想让宏只是通过并打开文件夹中的每一个。

There are about 100 individual data files, each with a nonsensical filename, so I'm looking to have the macro just go through and open each one in the folder.

推荐答案

这里我将如何解决完整的任务：

here how I would solve the complete task:

Private Sub importFiles(ByVal pFolder As String)
    ' create FSO
    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    ' create folder
    Dim oFolder As Object
    Set oFolder = oFSO.getFolder(pFolder)

    ' go thru the folder
    Dim oFile As Object
    For Each oFile In oFolder.Files
        ' check if is a text file
        If UCase(Right(oFile.Name, 4)) = ".TXT" Then
            Debug.Print "process file: " & oFolder.Path & "\" & oFile.Name
            readFile oFolder.Path & "\" & oFile.Name
        End If
    Next

    ' clean up
    Set oFolder = Nothing
    Set oFSO = Nothing
End Sub

Private Sub readFile(ByVal pFile As String)
    ' get new file handle
    Dim hnd As Integer
    hnd = FreeFile

    ' open file
    Open pFile For Input As hnd

    Dim sContent As String
    Dim sLine As String

    ' read file
    Do Until EOF(hnd)
        Line Input #hnd, sLine
        sContent = sContent & sLine
    Loop

    ' close file
    Close hnd

    ' extract requiered data
    Debug.Print getValue(sContent, "ProdName")
    Debug.Print getValue(sContent, "DescText")
End Sub

Private Function getValue(ByVal pContent As String, ByVal pValueName As String) As String
    Dim sRet As String

    sRet = ""
    If InStr(pContent, pValueName) Then
        pContent = Mid(pContent, InStr(pContent, pValueName) + Len(pValueName) + 2)
        sRet = Left(pContent, InStr(pContent, ";") - 1)
        sRet = Trim(sRet)
    End If

    getValue = sRet
End Function

总体来说，解决方案包含3个不同的过程：

Overall the solution contains 3 different procedures:

• importFiles读取给定目录的内容（必须作为参数传递），如果找到.txt文件，它调用readFile文件的完整路径

• importFiles reads the content of a given directory (which has to be handed over as parameter) and if it finds a .txt file it calls readFile() and passes the full path of the file to it

readFile（）打开文本文件并将内容存储在字符串变量中。完成后，对于您嵌入的每个值调用getValue。

readFile() opens the text file and stores the content in a string variable. After this is done it calles getValue for each value you are interessted in.

getValue分析给定的内容并提取给定的值。 li>

getValue analyses the given content and extractes the given value.

只需调整getValue（）的调用，即可获取所有的值，并存储它们，而不是通过debug.print使用正确的目录调用第一个过程，例如 importFilesC：\ Temp

Simply adjust the calls of getValue() so that you get all values you are interessted in and store them instead of showing via debug.print and call the first procedure with the right directory like importFiles "C:\Temp"

这篇关于从一个文件夹中的多个文本文件提取数据到Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！