Store some data of one Array into another Array in VBS

Question

I have a CSV file in the following format:

abc, def
ghi, xyz

I am getting the data inside the file into an Array with this Function:

Public Function readeEachColumnDataExcel()
    Dim filename
    Dim fso
    Dim f
    Dim arrData
    Dim arrDataParameter()
    Dim fData
    
    filename = "test.csv"
    
    Set fso = createobject("scripting.filesystemobject")
    Set f  = fso.OpenTextFile("C:\Users\U5UI5TU\Documents\PDRM_Project\Excel\"& Filename)
    
    While not f.AtEndOfStream
        fData = f.ReadLine    ' Read CSV File Line
        arrData = split(fData,",")    'Split the line
        'arrDataParameter = arrData(1)
    Wend

    f.Close
    Set fso=Nothing
End Function
 
Call readeEachColumnDataExcel

Now I want to save the second value of arrData for every iteration into the array arrDataParameter (see the line commented). How can I do that? Any help on this will be much appreciated.

Answer 1

CSV Column To Array

test.csv

• The empty line is just used to illustrate the behavior of the code.

abc,def

ghi,jkl
mno,pqr

Result

Code

• Usually, the column separator has no spaces. If it does, replace the "," with ", ".
• If the file is empty i.e. its length is 0, the function will return an empty Variant (Empty).

Option Explicit

' Main

Const FOLDER_PATH = "C:\Users\U5UI5TU\Documents\PDRM_Project\Excel\"
Const FILE_NAME = "test.csv"
Const COLUMN_INDEX = 1 ' zero-based
Const COLUMN_SEPARATOR = ","

Dim Arr: Arr = CsvColumnToArray( _
    FOLDER_PATH, FILE_NAME, COLUMN_INDEX, COLUMN_SEPARATOR)

If Not IsEmpty(Arr) Then
    MsgBox "Result" & vbLf & vbLf & "Strings Found: ... " & UBound(Arr) + 1 _
        & vbLf & Join(Arr, vbLf), vbInformation, "Test"
End If

' Procedures

Private Function CsvColumnToArray( _
        FolderPath, _
        Filename, _
        ColumnIndex, _
        ColumnSeparator)
    Const PROC_TITLE = "CSV Column To Array"
    Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")

    If Not fso.FolderExists(FolderPath) Then
        MsgBox "The path """ & FolderPath & """ doesn't exist!", _
            vbCritical, PROC_TITLE
        Exit Function
    End If
    
    Dim FilePath: FilePath = FolderPath & Filename

    If Not fso.FileExists(FilePath) Then
        MsgBox "The file """ & FILE_NAME & """ doesn't exist in """ _
            & FolderPath & """!", vbCritical, PROC_TITLE
        Exit Function
    End If

    Dim n: n = 0
    Dim WasLineFound: WasLineFound = False

    Dim fsoFile: Set fsoFile = fso.OpenTextFile(FilePath)

    Dim ArrSplit, ArrResult(), CurrentLine, CurrentString, HadStringLength

    Do While Not fsoFile.AtEndOfStream
        CurrentLine = fsoFile.ReadLine
        HadStringLength = False
        If Len(CurrentLine) > 0 Then
            ArrSplit = Split(CurrentLine, ColumnSeparator)
            If UBound(ArrSplit) >= ColumnIndex Then
                CurrentString = ArrSplit(ColumnIndex)
                If Len(CurrentString) > 0 Then
                    ReDim Preserve ArrResult(n): ArrResult(n) = CurrentString
                    HadStringLength = True
                End If
            End If
        End If
        If Not WasLineFound Then
            If n > 0 Or Len(CurrentLine) > 0 Then WasLineFound = True
        End If
        n = n + 1
    Loop

    fsoFile.Close

    If Not WasLineFound Then
        MsgBox "No data found in """ & FilePath & """!", _
        vbCritical, PROC_TITLE
        Exit Function
    End If

    If Not HadStringLength Then ReDim Preserve ArrResult(n - 1)
    
    CsvColumnToArray = ArrResult

End Function