Convert .csv file into 2-dimensional array in Excel VBA memory

Question

I have a .csv file that looks like this:

Unit,,PU,,,,,Start Date & Time,,,5/20/2020 17:32,,

Name,,PNR15.5,,,,,,,,,,

ID,,TEST52020,,,,,End Date & Time,,,,,

No.,,77,,,,,,,,,,

,,,,,,,,,,,,

Phase name,,Start Time,,End Time,,,,,,,,

C,,,,,,,,,,,,

S,,,,,,,,,,,,

I am trying to convert it to a 2-dimensional array, in Excel VBA memory, that can be referenced by Excel cells.

There are hundreds of CSV files.

I tried line by line, but element2(k,l) line gives me an error.

Dim ArrayOfElements As Variant
Dim line, element, element2() As Variant
Dim k, l as Integer
k=0
Do While filename <> vbNullString
    Open path & filename For Input As #1
            
    Do While Not EOF(1)
        Line Input #1, line
        
        ArrayOfElements = Split(line, ",")
        k = k + 1
        l = 1
        For Each element In ArrayOfElements
            element2(k, l) = element
        l = l + 1
        Next
    Loop
    Close #1
Loop

Eventually, I want: Cells(1,1).Value = element2(1,1).

You haven't sized element2() before you begin populating it. The easier way is to open the CSV in Excel, and read the data from the worksheet. Then you don't need to worry about how to correctly parse the CSV format (such as how to handle field values with embedded spaces for example) — Tim Williams
– Tim Williams, Commented Jun 18, 2020 at 6:41
I agree with @TimWilliams. Might need to test it, but conceptually, it might be faster and simpler to read the entire CSV file into a temporary worksheet usingWorkbooks.OpenText or a Query and then read it into a vba array in a single step (e.g myArr = UsedRange) and then delete the sheet. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jun 18, 2020 at 10:34
I agree. If we still want to use this code, how do I size element2() as it goes through each element? I could potentially size the array in the beginning, but I would like to do it as we go through each element. — ricky88
– ricky88, Commented Jun 18, 2020 at 15:21
I think I got it: Do While filename <> vbNullString Open path & filename For Input As #1 Do While Not EOF(1) Line Input #1, line ArrayOfElements = Split(line, ",") k = k + 1 l = 1 For Each element In ArrayOfElements ReDim element2(k, l) element2(k, l) = element MsgBox element2(k, l) l = l + 1 Next Loop Close #1 Loop — ricky88
– ricky88, Commented Jun 18, 2020 at 15:32
If you want to resize an array but not lose its contents you need to use Redim Preserve , but the issue is you can only resize the last dimension (so you can't add more "rows") — Tim Williams
– Tim Williams, Commented Jun 18, 2020 at 15:36

Ron Rosenfeld · Accepted Answer · 2020-06-19 01:16:16Z

1

I'd still recommend reading the file onto a hidden, temporary worksheet, then directly into the array (eg ArrayOfElements = myRange) but using a variation of your existing code, here is an example using a Collection object for intermediate storage:

    Open Path & Filename For Input As #1
    Set col = New Collection

    Do While Not EOF(1)
        Line Input #1, line
        col.Add Split(line, ",") 'Note that Split results in a 0-based array
    Loop
    Close #1


ReDim ArrayOfElements(1 To col.Count, 1 To UBound(col(1)) + 1)
l = 0

For Each element In col
    l = l + 1
    For k = 0 To UBound(element)
        ArrayOfElements(l, k + 1) = element(k)
    Next k
Next element

answered Jun 19, 2020 at 1:16

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ricky88 Over a year ago

Ah thank you! Do you know if it runs faster using collection vs. what I wrote?

Ron Rosenfeld Over a year ago

@ricky88 I have no idea. You'd have to time it. My sense is that using the collection object would slow things down compared with creating a fixed size array. The advantage of this method is that you don't need to know the size of the array in advance.

Collectives™ on Stack Overflow

Convert .csv file into 2-dimensional array in Excel VBA memory

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related