power array

Hi,

Please assist how can we transfer data from csv to an array in VB. Containing:
i.e.
1,6,11,16
2,7,12,17
3,8,13,18
4,9,14,19
5,10,15,20

basically follow the order of the value above

Dim arr_req() As String

output:
debug.print join(arr_req,",")

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20


Thanks.


    i = FreeFile
    Open App.Path & "\import\cast.csv" For Input As #i
    arr_req = Split(Input(LOF(i), #i), vbCrLf)
    Close #i
RyanBankAsked:
Who is Participating?
 
Mike TomlinsonConnect With a Mentor Middle School Assistant TeacherCommented:
Do you want to end up with a one dimensional array or a two dimensional array?

If a ONE dimensional array then...
(this will work for any size "matrix" in your CSV file)

Option Explicit

Private arr_req() As String
   
Private Sub Command1_Click()
    Dim i As Integer
    Dim fileName As String
    Dim contents As String
    Dim arr_tmp() As String
   
    ' read in the whole file
    fileName = "c:\someFile.txt"
    i = FreeFile
    Open fileName For Input As #i
    contents = Input(LOF(i), #i)
    Close #i
   
    ' trim any trailing vbCrLfs
    While Right(contents, 2) = vbCrLf
        contents = Left(contents, Len(contents) - 2)
    Wend
   
    ' convert it to an array of STRING with each line from file
    ' in it's own element of the array
    arr_tmp = Split(contents, vbCrLf)
   
    ' figure out how many rows/cols we are dealing with
    Dim rows As Integer
    Dim cols As Integer
    rows = UBound(arr_tmp) + 1
    cols = UBound(Split(arr_tmp(0), ",")) + 1
    Debug.Print "rows = " & rows
    Debug.Print "cols = " & cols
       
    ' put all the rows together as one long comma seperated string
    arr_tmp = Split(Join(arr_tmp, ","), ",")
    Debug.Print "Before: " & Join(arr_tmp, ",")
   
    ' resize the target array
    ReDim arr_req(UBound(arr_tmp))
   
    Dim counter As Integer
    Dim tmp As String
    Dim index As Integer
    Dim row As Integer
    Dim col As Integer
    For row = 0 To rows - 1
        For col = 0 To cols - 1
            index = (col * rows) + row
            arr_req(index) = arr_tmp(counter)
            counter = counter + 1
        Next col
    Next row
       
    ' output the results
    Debug.Print "After: " & Join(arr_req, ",")
End Sub
0
 
bruintjeCommented:
Hi RyanBank,
----------

what do you need in vb array?

----------
bruintje
0
 
RyanBankAuthor Commented:
Hi bruintje,

How are you? To contain the data from the csv file.

CSV data contains:
1,6,11,16
2,7,12,17
3,8,13,18
4,9,14,19
5,10,15,20


output:
debug.print join(arr_req,",")

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jmundsackCommented:
Questions:

* Will the CSV file always contain 4 comma-delimited columns?
* Will the CSV file always contain 5 rows?
0
 
bruintjeConnect With a Mentor Commented:
ok i seem to miss something but your code is already giving you the result your asking for or not?
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
How about...

Option Explicit

Private arr_req() As String
   
Private Sub Command1_Click()
    Dim i As Integer
    Dim fileName As String
    Dim contents As String
    Dim arr_tmp() As Integer

    ' read in the whole file
    fileName = "c:\someFile.txt"
    i = FreeFile
    Open fileName For Input As #i
    contents = Input(LOF(i), #i)
    Close #i
   
    ' trim any trailing vbCrLfs
    While Right(contents, 2) = vbCrLf
        contents = Left(contents, Len(contents) - 2)
    Wend
   
    ' convert it to an array of STRING
    arr_req = Split(Join(Split(contents, vbCrLf), ","), ",")
   
    ' copy the STRING values to a temp INTEGER array
    ReDim arr_tmp(UBound(arr_req))
    For i = LBound(arr_tmp) To UBound(arr_tmp)
        arr_tmp(i) = CInt(arr_req(i))
    Next i
   
    ' sort the INTEGER array
    Quicksort arr_tmp, LBound(arr_tmp), UBound(arr_tmp)
   
    ' copy the SORTED INTEGERs back to the STRING array
    For i = LBound(arr_tmp) To UBound(arr_tmp)
        arr_req(i) = CStr(arr_tmp(i))
    Next i
   
    ' output the results
    Debug.Print Join(arr_req, ",")
End Sub

Private Sub Quicksort(ByRef list As Variant, ByVal min As Integer, ByVal max As Integer)
    Dim med_value As Variant
    Dim hi As Integer
    Dim lo As Integer
    Dim i As Integer

    ' If the list has no more than 1 element, it's sorted.
    If min >= max Then Exit Sub

    ' Pick a dividing item.
    i = Int((max - min + 1) * Rnd + min)
    med_value = list(i)

    ' Swap it to the front so we can find it easily.
    list(i) = list(min)

    ' Move the items smaller than this into the left
    ' half of the list. Move the others into the right.
    lo = min
    hi = max
    Do
        ' Look down from hi for a value < med_value.
        Do While list(hi) >= med_value
            hi = hi - 1
            If hi <= lo Then Exit Do
        Loop
        If hi <= lo Then
            list(lo) = med_value
            Exit Do
        End If

        ' Swap the lo and hi values.
        list(lo) = list(hi)
       
        ' Look up from lo for a value >= med_value.
        lo = lo + 1
        Do While list(lo) < med_value
            lo = lo + 1
            If lo >= hi Then Exit Do
        Loop
        If lo >= hi Then
            lo = hi
            list(hi) = med_value
            Exit Do
        End If

        ' Swap the lo and hi values.
        list(hi) = list(lo)
    Loop

    ' Sort the two sublists
    Quicksort list, min, lo - 1
    Quicksort list, lo + 1, max
End Sub
0
 
jmundsackCommented:
Another question:

* Is the point that you need the values to be sorted, regardless of their position in the text file, or do you need them to be added to the array in the order: 1st column top-to-bottom, 2nd column top-to-bottom, etc.?

If you just need the values sorted, then Idle_Mind's code looks good.  But what if the values in the CSV file were:

20,6,11,16
2,7,12,17
3,8,13,18
4,9,14,19
5,10,15,1

And you wanted the end array to be:
20,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,1

?
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Good question jmundsack!   =)
0
 
RyanBankAuthor Commented:
Needs to follow the pattern. no sorting involved. basically going column by column

CSV data contains:
11,16,11,16
22,77,12,17
43,88,13,18
44,19,14,19
51,10,15,20

11,22,43,44,51,16,77,88,19,10,11,12,13,14,15,16,17,18,19,20


0
 
RyanBankAuthor Commented:
Yes this way,
>>need them to be added to the array in the order: 1st column top-to-bottom, 2nd column top-to-bottom, etc<<

sorry it was kinda hard to explain. my apology.
0
 
jmundsackCommented:
Is the number of columns constant at 4?
0
 
RyanBankAuthor Commented:
jmundsack

yes that is correct, it is constant at 4 columns and 5 rows.
0
 
jmundsackConnect With a Mentor Commented:
This should work (not tested...):

    Dim i
    Dim r
    Dim c
    Dim arr_1
    Dim arr_2
    Dim arr_req(4, 3)

    i = FreeFile
    Open App.Path & "\import\cast.csv" For Input As #i
    arr_1 = Split(Input(LOF(i), #i), vbCrLf)
    For r = LBound(arr_1) To UBound(arr_1)
        arr_2 = Split(arr_1(r), ",")
        For c = LBound(arr_2) To UBound(arr_2)
            arr_req(r, c) = arr_2(c)
        Next c
    Next r
    Close #i
0
 
RyanBankAuthor Commented:
jmundsack,

I think you got it the  other way around or I missed something, which array contains the values?

CSV data contains:
11,16,11,16
22,77,12,17
43,88,13,18
44,19,14,19
51,10,15,20

output values:  (inserted values in the array)
join(arr_req,",")
11,22,43,44,51,16,77,88,19,10,11,12,13,14,15,16,17,18,19,20
0
 
RyanBankAuthor Commented:
Idle_Mind,

one dimentional, i'm s  osrry if my info is not complete. please forgive me.

thanks.
0
 
jmundsackCommented:
Crud, I forgot his original spec called for a 1D array.  My bad.
0
 
jmundsackCommented:
My example is just all messed up.  I'm braindead today.  Forget everything I wrote.  How embarrassing!  :(
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Don't feel bad jmundsack...it took me waaaaay too long to come up with my algorithm.

I think I'll go make some coffee now...      =)
0
 
RyanBankAuthor Commented:
works like a charm!!

Idle_Mind for president!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.