问题描述
我正在尝试优化一些代码,这些代码需要一些存储在CSV文件中的测试数据,并进行一些分析,并将其数据复制到excel工作表中.该代码通常一次运行数百个测试,每次测试大约需要4.5秒,因此有时可能要花费数小时才能完成.
I'm trying to optimize some code that takes some test data stored in CSV files does some analysis and copies their data into an excel sheet. This code is often run on hundreds of tests at a time, and its taking about 4.5 seconds per test so it can take hours to complete at times.
我查找了一些优化技术,每次测试将其减少了约.25秒,但我认为excel不得不占用大部分时间,因此必须打开"单个文件,然后才能对其进行任何处理.有办法更有效地做到这一点吗?
I looked up some optimization techniques and cut it down by about .25 seconds per test but I think the bulk of the time is being taken up by excel having to "open" the individual files before it can do anything with them. Is there a way to do this more efficiently?
我很乐意回答涉及使用另一种语言将文件编译成一个大文件的问题,如果这样做会使事情变得更快.
I am open to answers that involve using another language to compile the files into one big file if that would make things quicker.
推荐答案
我将其作为文本而不是工作簿打开:
I would open them as text rather than workbooks:
Sub ReadCSV()
Dim MyString As String
Open "C:\path\text.csv" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, MyString ' Read a line into variable
Debug.Print MyString ' Print data to the Immediate window.
Loop
Close #1 ' Close file.
End Sub
这比打开工作簿快很多
This will be much faster than opening as a workbook
这篇关于操作/复制.CSV数据,而无需打开文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!