VBA - 有效地访问和查找 CSV 文件中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11145832/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA - Accessing and looking up values in CSV files efficiently
提问by brentf
I am working on a complex model that needs to lookup values in a series of distinct tables. If I embed all of the information in the model itself, then the file quickly becomes unwieldy. I am hoping to find a solution where I can have a series of CSV files that contain all of the lookup tables, and then have my VBA code just quickly read each CSV file as necessary and return the appropriate value. My initial thought is to read each CSV file in working memory as needed, lookup the necessary values, then discard the information once the lookups are complete. Is that most efficient way to do it?
我正在研究一个需要在一系列不同表中查找值的复杂模型。如果我将所有信息嵌入模型本身，那么文件很快就会变得笨拙。我希望找到一个解决方案，在其中我可以拥有一系列包含所有查找表的 CSV 文件，然后让我的 VBA 代码根据需要快速读取每个 CSV 文件并返回适当的值。我最初的想法是根据需要读取工作内存中的每个 CSV 文件，查找必要的值，然后在查找完成后丢弃这些信息。这是最有效的方法吗？
回答by Tim Williams
Here's an idea which might work for you: load your csv file into a variant array the first time it's required, then subsequent calls will use the cached data. You can lookup values in any column and return the corresponding value from any other column.
这是一个可能对您有用的想法：第一次需要时将您的 csv 文件加载到一个变体数组中，然后后续调用将使用缓存的数据。您可以在任何列中查找值并从任何其他列返回相应的值。
EDIT: updated to show how to populate lookup arrays from CSV files
编辑：更新以显示如何从 CSV 文件填充查找数组
Sub Tester()
Dim arr1, arr2
arr1 = CsvToArray("D:\Analysis\tmp\Data1.csv")
arr2 = CsvToArray("D:\Analysis\tmp\Data2.csv")
Debug.Print TestLookup(arr1, "lookup1", 2, 1)
Debug.Print TestLookup(arr2, "lookup2", 3, 1)
'bunch more lookups...
End Sub
Function TestLookup(arr, val, lookincol As Integer, returnfromcol As Integer)
Dim r
r = Application.Match(val, Application.Index(arr, 0, lookincol), 0)
If Not IsError(r) Then
TestLookup = arr(r, returnfromcol)
Else
TestLookup = "Not found" 'or some other "error" value
End If
End Function
Function CsvToArray(filepath As String) As Variant
Dim wb As Workbook
Application.ScreenUpdating = False
Set wb = Workbooks.Open(filepath)
CsvToArray = wb.Sheets(1).Range("A1").CurrentRegion.Value
wb.Close False
End Function
回答by SeanC
if you really have to do it in excel, then this is a method:
如果你真的必须在excel中做到这一点，那么这是一种方法：
Function GetData(This As String, ResultCol As Integer)
Dim LastRow As Long
Application.ScreenUpdating = False 'Turn off screen refreshing
Workbooks.Open Filename:="E:\my_files\tables.csv" 'Open the CSV file
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'used for range in vlookup
GetData = Application.WorksheetFunction.VLookup(This, Range(Cells(1, 1), Cells(LastRow, ResultCol)), ResultCol, False)
Workbooks("Tables.csv").Close 'Close the CSV file
Application.ScreenUpdating = True 'Turn on screen refreshing
End Function
