vba 在不打开文件的情况下将 CSV 文件的内容加载到数组

Load contents of CSV file to array without opening file

提问by Benny Muller

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

我需要将 6000 多个 csv 文件整理成一个 csv 文档。当前的 VBA 流程是： 1. 打开单个 CSV 数据文件 2. 根据行数将文件内容加载到数组 3. 关闭单个 CSV 文件 4. 处理数组

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.

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

I am using Excel 2011 for Mac.

我使用的是 Excel 2011 for Mac。

回答by Siddharth Rout

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

好的，我假设所有 6000 个文件都具有相同的格式。

My Test Conditions

我的测试条件

1. I have a folder called C:\Temp\ which has 6000 CSV Files
2. All csv files have 40 Rows and 16 Columns
3. Tested it in Excel 2010. Don't have access to 2011. Will test it in 2011 in approx 30 mins.

1. 我有一个名为 C:\Temp\ 的文件夹，其中包含 6000 个 CSV 文件
2. 所有 csv 文件都有 40 行和 16 列
3. 在 Excel 2010 中对其进行了测试。无法访问 2011。将在 2011 年进行大约 30 分钟的测试。

I ran the below code and the code took just 4 seconds.

我运行了下面的代码，代码只用了 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

Screenshot of the folder

文件夹截图

Screenshot of the Code Output

代码输出的屏幕截图

---

---

UPDATE

更新

Ok I tested it in MAC

好的，我在 MAC 上测试过了

My Test Conditions

我的测试条件

1. I have a folder called Sample on the desktop which has 1024 CSV Files
2. All csv files have 40 Rows and 16 Columns
3. Tested it in Excel 2011.

1. 我在桌面上有一个名为 Sample 的文件夹，其中有 1024 个 CSV 文件
2. 所有 csv 文件都有 40 行和 16 列
3. 在 Excel 2011 中对其进行了测试。

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

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

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

Screenshot of the folder

文件夹截图

Screenshot of the Code Output

代码输出的屏幕截图

回答by Gary Restall

You don't needto use Excel to do this, you can merge using windows copy from the command prompt by entering:

您不需要使用 Excel 来执行此操作，您可以通过从命令提示符输入以下命令使用 windows copy 进行合并：

copy *.csv mergedfilename.csv

回答by Joe

There's not an Excel answer for your problem, in my opinion - certainly not within the normal definition thereof, anyhow.

在我看来，没有针对您的问题的 Excel 答案 - 无论如何，当然不在其正常定义范围内。

The correct method to solve it is to use a programming language that is appropriate for the task; perl, for example, or even command shell, to combine the files. Excel isn't made for constant file i/o, but perl is quite good at handling lots of files. I performed a project similar to this (combining millions of files) in a few minutes on a relatively small unix server.

解决它的正确方法是使用适合该任务的编程语言；例如，perl 甚至命令 shell 来组合文件。Excel 不是为常量文件 i/o 设计的，但 perl 非常擅长处理大量文件。我在一个相对较小的 unix 服务器上在几分钟内执行了一个与此类似的项目（组合了数百万个文件）。

You can also use command shell to cat the files together (cat=concatenate), as nneonneo suggests in comments; I couldn't say which is faster. Perl certainly would take longer to code, especially if you have to learn perl first (though there are lots of examples on the 'net).

您还可以使用命令 shell 将文件放在一起 (cat=concatenate)，正如 nneonneo 在评论中所建议的那样；我不能说哪个更快。Perl 肯定需要更长的时间来编写代码，尤其是如果您必须先学习 perl（尽管网上有很多示例）。