excel vba Load csv file into an array rather than the worksheet
'VBA function to open a CSV file in memory and parse it to a 2D
'array without ever touching a worksheet:
Function ArrayFromCSV(sFile$)
Dim c&, i&, j&, p&, d$, s$, rows&, cols&, a, r, v
Const Q = """", QQ = Q & Q
Const ENQ = "" 'Chr(5)
Const ESC = "" 'Chr(27)
Const COM = ","
d = OpenTextFile$(sFile)
If LenB(d) Then
r = Split(Trim(d), vbCrLf)
rows = UBound(r) + 1
cols = UBound(Split(r(0), ",")) + 1
ReDim v(1 To rows, 1 To cols)
For i = 1 To rows
s = r(i - 1)
If LenB(s) Then
If InStrB(s, QQ) Then s = Replace(s, QQ, ENQ)
For p = 1 To Len(s)
Select Case Mid(s, p, 1)
Case Q: c = c + 1
Case COM: If c Mod 2 Then Mid(s, p, 1) = ESC
End Select
Next
If InStrB(s, Q) Then s = Replace(s, Q, "")
a = Split(s, COM)
For j = 1 To cols
s = a(j - 1)
If InStrB(s, ESC) Then s = Replace(s, ESC, COM)
If InStrB(s, ENQ) Then s = Replace(s, ENQ, Q)
v(i, j) = s
Next
End If
Next
ArrayFromCSV = v
End If
End Function
Function OpenTextFile$(f)
With CreateObject("ADODB.Stream")
.Charset = "utf-8"
.Open
.LoadFromFile f
OpenTextFile = .ReadText
.Close
End With
End Function