How to import CSV data into multiple arrays and to return the multiple arrays via a function or sub in VBA?

Question

I want to create a VBA add-in that imports a CSV file, split the data columns into separate arrays and returns these multiple arrays to the sub that calls this add-in. Not sure what is the best way to go about this.

For example, my data folder contains many CSV files where each has 5 columns of data (1st column is dates, and the remaining columns are numeric data. There is one line of header).

I also have several different workbooks that perform different types of analyses on these CSV files. Therefore, instead of reusing the same import csv data function or sub in each workbook, I want to create an add-in to perform this import function instead.

Ideally, this add-in will return 5 separate arrays for each CSV file. If not, I would be OK if it returns a 5-column array.

The code will look something like (where the SomeAnalysis Sub is in one of the Workbooks, ImportCSV is the add-in function or Sub and ncsv is the number of CSV files to be evaluated):

Sub SomeAnalysis()

  For n = 1 to ncsv
    [arr1,arr2,arr3,arr4,arr5]=ImportCSV(filename(n))
    'Perform the analysis
  next n

End Sub

LYang · Accepted Answer · 2019-07-24 05:08:07Z

Finally solved it with some adaption of codes I found on the net. Hope this is useful for some of you.

Option Explicit
Option Base 1

'Import CSV file function: Returns an array of data or multiple arrays
Public Function ImportCSV(filename As String, Optional splitarray As Boolean = True, Optional incheaders As Boolean = True) As Variant()
Dim fnum As Integer                         'file number
Dim datafile As String                      'raw datafile
Dim lines As Variant                        'breaking the file into lines
Dim one_line As Variant                     'temp data rows to split in loop
Dim nrows As Long, ncols As Integer         '# data rows and columns
Dim arr() As Variant                        'jagged data array (i.e. array of arrays)
Dim colarr() As Variant                     'component arrays in arr()
Dim h As Integer                            'flag for include header option
Dim r As Long, c As Integer                 'counters

'Load file
fnum = FreeFile
Open filename For Input As fnum
datafile = Input$(LOF(fnum), #fnum)
Close fnum

'Break file into lines
lines = Split(datafile, vbCrLf)

'Dimension the array
h = IIf(incheaders, 0, 1)
nrows = UBound(lines) - h
one_line = Split(lines(0), ",")
ncols = UBound(one_line)

'Choice to use jagged arrays to split data columns
If splitarray = True Then
    ReDim arr(1 To ncols + 1)
    For c = 0 To ncols
        ReDim colarr(1 To nrows)
        arr(c + 1) = colarr
    Next c
    'Copy data into array
    For r = 0 To nrows - 1
        If Len(lines(r)) > 0 Then
            one_line = Split(lines(r + h), ",")
            For c = 0 To ncols
                arr(c + 1)(r + 1) = one_line(c)
            Next c
        End If
    Next r
Else
    ReDim arr(nrows, ncols + 1)
    'Copy data into array
    For r = 0 To nrows - 1
        If Len(lines(r)) > 0 Then
            one_line = Split(lines(r + h), ",")
            For c = 0 To ncols
                arr(r + 1, c + 1) = one_line(c)
            Next c
        End If
    Next r
End If
ImportCSV = arr
End Function

Collectives™ on Stack Overflow

How to import CSV data into multiple arrays and to return the multiple arrays via a function or sub in VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related