循环浏览目录中的子文件夹，并从.CSV文件导入指定的列 [英] Loop through sub folders in a directory and import specified columns from .CSV files

问题描述

我试图遍历特定目录的子文件夹，并从.CSV文件导入指定的列。

我有一种编码解决方案，该解决方案不能遍历子目录

它包含一个工作表，该工作表在三个单独的列中具有文件路径，文件目标和列号，但是子文件夹是动态的。它们的名称和数量正在改变。

文件路径表：

代码：

 将DL作为工作表
 Dim DFI作为工作表
 
设置DL = ThisWorkbook.Sheets（ DataList）
设置DFI = ThisWorkbook.Sheets（ DataFeedInput）
 
 DL。 Rows（ $ 3：$ 202）。ClearContents 
 
使用DL.QueryTables.Add（Connection：= TEXT; C：\Users\ ... \MQL4\Files\Hist_ ＃Corn_1440.csv，目标位置：= Range（ $ A $ 3））
 .Name = Hist_＃Corn_1441 
 .FieldNames = True 
 .RowNumbers = False 
 .FillAdjacentFormulas =假
 .PreserveFormatting =真
 .RefreshOnFileOpen =假
 .RefreshStyle = xlInsertDeleteCells 
 .SavePassword =假
 .SaveData =真
 .AdjustColumnWidth =真
 .RefreshPeriod = 0 
 .TextFilePromptOnRefresh =假
 .TextFilePlatform = 866 
 .TextFileStartRow = 1 
 .TextFileParseType = xlDelimited 
。 $ b .TextFileColumnDataTypes = Array（9，1，9，9，9，9，9，1，9，9，9，9，9，9，9）
 .TextFileTrailingMinusNumbers = True 
。刷新BackgroundQuery：= False 
以
 
结尾Dim i作为整数
 
对于i = 4到642 
 
 Dim文件名作为字符串
 Dim OutputSheet作为字符串
 Dim ColNumber作为字符串
 
 FileN ame = DFI.Range（ B& i）.Value 
 OutputSheet = DFI.Range（ C& i）.Value 
 ColNumber = DFI.Range（ D& i）.Value 
 
使用DL.QueryTables.Add（Connection：= TEXT;& FileName，Destination：= DL.Range（ColNumber& 3））
 .FieldNames = True 
 .RowNumbers = False 
 .FillAdjacentFormulas =假
 .PreserveFormatting =真
 .RefreshOnFileOpen =假
 .RefreshStyle = xlInsertDeleteCells 
。 b .AdjustColumnWidth = True 
 .RefreshPeriod = 0 
 .TextFilePromptOnRefresh = False 
 .TextFilePlatform = 866 
 .TextFileStartRow = 1 
 .TextFileParseType = xlDelimited 
。 TextFileTextQualifier = xlTextQualifierDoubleQuote 
 .TextFileConsecutiveDelimiter =假
 .TextFileTabDelimiter =真
 .TextFileSemicolonDelimiter =假
 .TextFileCommaDelimiter =真
 .TextFileSpaceDelimiter =假
 .TextFileColumnDataTypes = Array（9，9，9 ，9，9，9，9，9，9，9，9，9，9，9，9）
 .TextFileTrailingMinusNumbers = True 
 .Refresh BackgroundQuery：= True 
以$结尾b 
 $ b接下来的i 
 
 DL.Cells.EntireColumn.AutoFit 
  

此方法的问题是，如果未从外部源下载.CSV文件，则会收到错误消息，指出该文件丢失。

另一个问题是这种方法需要数十年才能完成任务。

我正在寻找一种不依赖于文件路径表，遍历子文件夹并提取内容的解决方案仅来自.C的第6列SV文件。

在每个这些文件夹中我有一个.CSV文件：

我需要遍历它们中的每一个并创建与Excel工作表的连接，同时仅从.CSV导入第6列。

编辑1：

这是子文件夹的文件路径：

C：\Users＼ ＼Betty\AppData\Roaming\MetaQuotes\Terminal\B4D9BCD10BE9B5248AFCB2BE2411BA10\MQL4\Files\Export_History

编辑2：

到目前为止，我在@Jeeped的帮助下了解到，我可以使用 FileSystemObject ，可能去进入每个文件夹，然后从.CSV导入第6列。

对我来说，要深入了解如何通过文件夹和。 CSV导入。如果您可以帮助我进行概述的过程，我想我可以将其放在一起，并根据需要将其添加为该问题的编辑。

编辑3：

我认为我可以使用类似的方法来完成任务：

来自@Tim Williams对这个问题的回答的代码->

我从代码中得到的输出是：

@QHarr：请告诉我，如果您发现有什么可以改进的地方，尤其是在 QueryTables.Add 部分中。

I am trying to loop through a specific directory's sub folders and import specified columns from .CSV files.

I have a coding solution that does not loop through the sub folders.

Instead, it includes a Worksheet with File Path, File Destination and Column Number in three separate columns, but the sub folders are dynamic. They are changing in name and quantity.

File Path sheet:

Code:

Dim DL As Worksheet
Dim DFI As Worksheet

Set DL = ThisWorkbook.Sheets("DataList")
Set DFI = ThisWorkbook.Sheets("DataFeedInput")

    DL.Rows("$3:$202").ClearContents

        With DL.QueryTables.Add(Connection:="TEXT;C:\Users\ ... \MQL4\Files\Hist_#Corn_1440.csv", Destination:=Range("$A$3"))
            .Name = "Hist_#Corn_1441"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 866
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(9, 1, 9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

    Dim i As Integer

    For i = 4 To 642

    Dim FileName As String
    Dim OutputSheet As String
    Dim ColNumber As String

        FileName = DFI.Range("B" & i).Value
        OutputSheet = DFI.Range("C" & i).Value
        ColNumber = DFI.Range("D" & i).Value

            With DL.QueryTables.Add(Connection:="TEXT;" & FileName, Destination:=DL.Range(ColNumber & "3"))
                 .FieldNames = True
                 .RowNumbers = False
                 .FillAdjacentFormulas = False
                 .PreserveFormatting = True
                 .RefreshOnFileOpen = False
                 .RefreshStyle = xlInsertDeleteCells
                 .SavePassword = False
                 .SaveData = True
                 .AdjustColumnWidth = True
                 .RefreshPeriod = 0
                 .TextFilePromptOnRefresh = False
                 .TextFilePlatform = 866
                 .TextFileStartRow = 1
                 .TextFileParseType = xlDelimited
                 .TextFileTextQualifier = xlTextQualifierDoubleQuote
                 .TextFileConsecutiveDelimiter = False
                 .TextFileTabDelimiter = True
                 .TextFileSemicolonDelimiter = False
                 .TextFileCommaDelimiter = True
                 .TextFileSpaceDelimiter = False
                 .TextFileColumnDataTypes = Array(9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9)
                 .TextFileTrailingMinusNumbers = True
                 .Refresh BackgroundQuery:=True
             End With

    Next i

        DL.Cells.EntireColumn.AutoFit

The problem with this approach is that if a .CSV file is not downloaded from the external source, I get an error stating that the file is missing.

Another issue is that this approach takes decades to finish the task.

I am looking for a solution that is not dependent on the File Path sheet, loops through the sub folders and extracts solely column 6 from the .CSV file.

In each of these folders I have one .CSV file:

I need to loop through each of them and create connection to Excel sheet, while importing solely column 6 from the .CSV.

Edit 1:

This is the File Path to the Sub Folders:

C:\Users\Betty\AppData\Roaming\MetaQuotes\Terminal\B4D9BCD10BE9B5248AFCB2BE2411BA10\MQL4\Files\Export_History

Edit 2:

What I learned so far, with the help of @Jeeped, is that I can loop through the folders with FileSystemObject, probably, go in to each of the folders and import column 6 from the .CSV.

It is quite difficult for me to get into how to merge the loop trough the folders and the .CSV import. If you can give me a hand with an outline procedure, I think I will be able to put it together and add it as edit to this question, if needed.

Edit 3:

I reckon I can use something of such for completing the task:

Code from @Tim Williams' answer to this question -> VBA macro that search for file in multiple subfolders

Sub GetSubFolders()

    Dim fso As New FileSystemObject
    Dim f As Folder, sf As Folder

    Set f = fso.GetFolder("file path")
    For Each sf In f.SubFolders

        'Use a loop to import only column 6 from every .CSV file in sub folders 

    Next

End Sub

解决方案

@QHarr: Special thanks for the guidance!

After looking in to the FileSystemObject method for the purpose of looping trough Sub Folders and importing column 6 from a .CSV file in each Sub Folder in the next blank column in Worksheet HDaER, I managed to put together this code:

    Dim fso As Object
    Dim folder As Object
    Dim subfolders As Object
    Dim CurrFile As Object
    Dim HDaER As Worksheet

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("C:\Users\Betty\AppData\Roaming\MetaQuotes\Terminal\B4D9BCD10BE9B5248AFCB2BE2411BA10\MQL4\Files\Export_History\")
    Set subfolders = folder.subfolders
    Set HDaER = Sheets("HDaER")

'   IMPORT Col 6 FROM EACH .CSV FILE IN EACH SubFolder    
    LastCol = HDaER.Cells(2, HDaER.Columns.Count).End(xlToLeft).Column

    For Each subfolders In subfolders

    Set CurrFile = subfolders.Files
        For Each CurrFile In CurrFile
            With HDaER.QueryTables.Add(Connection:="TEXT;" & CurrFile, Destination:=HDaER.Cells(2, LastCol + 1))
                 .TextFileStartRow = 1
                 .TextFileParseType = xlDelimited
                 .TextFileConsecutiveDelimiter = False
                 .TextFileTabDelimiter = False
                 .TextFileSemicolonDelimiter = False
                 .TextFileCommaDelimiter = True
                 .TextFileSpaceDelimiter = True
                 .TextFileColumnDataTypes = Array(9, 9, 9, 9, 9, 1, 9)
                 .Refresh BackgroundQuery:=False
                 LastCol = LastCol + 1
            End With
        Next
    Next

'   REMOVE SOURCE CONNECTIONS
    For Each Connection In HDaER.QueryTables
        Connection.Delete
    Next Connection

'   FREE MEMORY 
    Set fso = Nothing
    Set folder = Nothing
    Set subfolders = Nothing

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

The Sub Folders that I currently have in the general Folder (Export_History) are:

The output that I get from the code is:

@QHarr: Please, let me know if you see anything that can be improved, especially in the QueryTables.Add part.

这篇关于循环浏览目录中的子文件夹，并从.CSV文件导入指定的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！