excelvba 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
