?
Solved

power array

Posted on 2006-06-02
19
Medium Priority
?
351 Views
Last Modified: 2010-04-07
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
0
Comment
Question by:RyanBank
  • 7
  • 6
  • 4
  • +1
19 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 16816484
Hi RyanBank,
----------

what do you need in vb array?

----------
bruintje
0
 

Author Comment

by:RyanBank
ID: 16816513
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
 
LVL 13

Expert Comment

by:jmundsack
ID: 16816522
Questions:

* Will the CSV file always contain 4 comma-delimited columns?
* Will the CSV file always contain 5 rows?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Assisted Solution

by:bruintje
bruintje earned 100 total points
ID: 16816557
ok i seem to miss something but your code is already giving you the result your asking for or not?
0
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 16816636
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
 
LVL 13

Expert Comment

by:jmundsack
ID: 16816677
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
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 16816704
Good question jmundsack!   =)
0
 

Author Comment

by:RyanBank
ID: 16816708
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
 

Author Comment

by:RyanBank
ID: 16816726
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
 
LVL 13

Expert Comment

by:jmundsack
ID: 16816996
Is the number of columns constant at 4?
0
 

Author Comment

by:RyanBank
ID: 16817012
jmundsack

yes that is correct, it is constant at 4 columns and 5 rows.
0
 
LVL 13

Assisted Solution

by:jmundsack
jmundsack earned 100 total points
ID: 16817111
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
 
LVL 86

Accepted Solution

by:
Mike Tomlinson earned 1800 total points
ID: 16817204
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
 

Author Comment

by:RyanBank
ID: 16817213
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
 

Author Comment

by:RyanBank
ID: 16817221
Idle_Mind,

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

thanks.
0
 
LVL 13

Expert Comment

by:jmundsack
ID: 16817224
Crud, I forgot his original spec called for a 1D array.  My bad.
0
 
LVL 13

Expert Comment

by:jmundsack
ID: 16817242
My example is just all messed up.  I'm braindead today.  Forget everything I wrote.  How embarrassing!  :(
0
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 16817256
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
 

Author Comment

by:RyanBank
ID: 16817316
works like a charm!!

Idle_Mind for president!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question