将CSV文件的内容加载到数组，不打开文件 [英] Load contents of CSV file to array without opening file

问题描述

我需要将超过6000个csv文件整理到一个单一的csv文件中。目前的VBA流程是：
1.打开单独的CSV数据文件
2.根据行数将$文件加载到数组
3.关闭单个CSV文件
4。过程数组

I have a requirement to collate over 6000 csv files into a single csv document. The current VBA process is: 1. Open individual CSV data file 2. Load contents of file to array based on number of rows 3. Close individual CSV file 4. Process array

为了提高代码和处理的效率，我希望有一种方法可以将数据从单独的CSV文件加载到数组中，而不会打开关闭每一个文件。

In order to improve efficiency of the code and processing, I was hoping there may be a method to load the data from the individual CSV files into an array without opening and closing every single file.

我正在使用Excel 2011 for Mac。

I am using Excel 2011 for Mac.

推荐答案

确定我假设所有6000个文件格式相同。

Ok I am assuming that All 6000 files have the same format.

我的测试条件

1. 我有一个名为C：\Temp\的文件夹，具有6000个CSV文件


2. 所有csv文件都有40行和16列


3. 在Excel 2010中进行了测试。无法访问2011年。将在约30分钟内在2011年进行测试。

我运行下面的代码，代码只需要4秒。

Option Explicit

Sub Sample()
    Dim strFolder As String, strFile As String
    Dim MyData As String, strData() As String
    Dim FinalArray() As String
    Dim StartTime As String, endTime As String
    Dim n As Long, j As Long, i As Long

    strFolder = "C:\Temp\"

    strFile = Dir(strFolder & "*.csv")

    n = 0

    StartTime = Now

    Do While strFile <> ""
        Open strFolder & strFile For Binary As #1
        MyData = Space$(LOF(1))
        Get #1, , MyData
        Close #1

        strData() = Split(MyData, vbCrLf)
        ReDim Preserve FinalArray(j + UBound(strData) + 1)
        j = UBound(FinalArray)

        For i = LBound(strData) To UBound(strData)
            FinalArray(n) = strData(i)
            n = n + 1
        Next i

        strFile = Dir
    Loop

    endTime = Now

    Debug.Print "Process started at : " & StartTime
    Debug.Print "Process ended at : " & endTime
    Debug.Print UBound(FinalArray)
End Sub

文件夹的屏幕截图

代码输出的屏幕截图

更新

确定我在MAC中测试了

Ok I tested it in MAC

我的测试条件

My Test Conditions

1. 我有一个名为Sample的文件夹，桌面上有1024个CSV文件


2. 所有csv文件都有40行和16列


3. 在Excel 2011中测试。

我运行下面的代码和代码花了不到1秒（因为只有1024个文件）。所以我希望它再次运行4秒，万一有6k文件

I ran the below code and the code took LESS THAN 1 second (since there were only 1024 files). So I am expecting it to again run for 4 secs in case there were 6k files

Sub Sample()
    Dim strFile As String
    Dim MyData As String, strData() As String
    Dim FinalArray() As String
    Dim StartTime As String, endTime As String
    Dim n As Long, j As Long, i As Long

    StartTime = Now

    MyDir = ActiveWorkbook.Path
    strPath = MyDir & ":"

    strFile = Dir(strPath, MacID("TEXT"))

    'Loop through each file in the folder
    Do While Len(strFile) > 0
        If Right(strFile, 3) = "csv" Then
            Open strFile For Binary As #1
            MyData = Space$(LOF(1))
            Get #1, , MyData
            Close #1

            strData() = Split(MyData, vbCrLf)
            ReDim Preserve FinalArray(j + UBound(strData) + 1)
            j = UBound(FinalArray)

            For i = LBound(strData) To UBound(strData)
                FinalArray(n) = strData(i)
                n = n + 1
            Next i

            strFile = Dir
        End If
        strFile = Dir
    Loop

    endTime = Now

    Debug.Print "Process started at : " & StartTime
    Debug.Print "Process ended at : " & endTime
    Debug.Print UBound(FinalArray)
End Sub

文件夹的屏幕截图

代码输出的屏幕截图

这篇关于将CSV文件的内容加载到数组，不打开文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！