Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

import csv to array

Hi,

We have a csv file that contains the following: (comma seperated)
0,33,22,6,5,4,3,
1,13,62,7,3,5,3,
2,23,52,8,2,6,9,
3,33,42,9,1,7,8,
....

Presently I can import the csv to an array sadly it only works using Y axis(it loads everything to the first element).
i.e.
element 1 -0,33,22,6,5,4,3,
element 2 1,13,62,7,3,5,3,
....
Please assist how can we include X axis and seperate each value using comma?

sample output:
Debug.Print arr_Tempate(0,1) = 33


Thanks

Dim arr_Tempate() As String
    i = FreeFile
    Open "c:\Template.csv" For Input As #i
    arr_Tempate = Split(Input(LOF(i), #i), vbCrLf)
    Close #i



0
RyanBank
Asked:
RyanBank
  • 2
2 Solutions
 
bruintjeCommented:
Hello RyanBank,

you could try something like
----------
Dim sFileName As String
Dim sRows As Variant
Dim sTemplate() As Variant
Dim i As Long

i = FreeFile
Open "c:\test.csv" For Input As #i
sRows = Split(Input(LOF(i), #i), vbCrLf)
Close #i

ReDim sTemplate(UBound(sRows))

For i = LBound(sRows) To UBound(sRows)
  sTemplate(i) = Split(sRows(i), ",")
Next i

MsgBox sTemplate(0)(1)
----------

hope this helps a bit
bruintje
0
 
RyanBankAuthor Commented:
bruintje,

how can we have it as sTemplate(x,y) ?

I use alot of debug.print sTemplate(x,y)   :-)

Thanks.
0
 
bruintjeCommented:
:) try

Dim sFileName As String
Dim sRows As Variant
Dim sTemplate() As Variant
Dim sTemplate2() As Variant
Dim i As Long, j As Long, rowCount As Long, colCount As Long

i = FreeFile
Open "c:\test.csv" For Input As #i
sRows = Split(Input(LOF(i), #i), vbCrLf)
Close #i

ReDim sTemplate(UBound(sRows))

For i = LBound(sRows) To UBound(sRows)
  sTemplate(i) = Split(sRows(i), ",")
Next i

rowCount = i

For i = LBound(sTemplate(0)) To UBound(sTemplate(0))
  colCount = colCount + 1
Next i

ReDim sTemplate2(rowCount - 1, colCount - 1)

For i = 0 To rowCount - 1
  For j = 0 To colCount - 1
    sTemplate2(i, j) = sTemplate(i)(j)
  Next j
Next i

MsgBox sTemplate2(0, 1)
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Another implementation...  

This produces sTemplate(row, col).  It is a zero based array so the first row and col are zero, not one.

Option Explicit

Private sTemplate() As String

Private Sub Form_Load()
    Dim fileName As String
    fileName = "c:\someFile.txt"
   
    Dim contents As String
    contents = GetFileContents(fileName)
    While Right(contents, 2) = vbCrLf
        contents = Left(contents, Len(contents) - 2)
    Wend
   
    Dim lines() As String
    lines = Split(contents, vbCrLf)
   
    Dim columns As Integer
    columns = UBound(Split(lines(0), ","))
   
    ReDim sTemplate(UBound(lines), columns)
   
    Dim values() As String
    Dim row As Integer
    Dim col As Integer
   
    For row = 0 To UBound(sTemplate, 1)
        values = Split(lines(row), ",")
        For col = 0 To UBound(sTemplate, 2)
            sTemplate(row, col) = values(col)
        Next col
    Next row
End Sub

Private Function GetFileContents(ByVal fileName As String) As String
    Dim ff As Integer
    Dim entireFile As String
   
    If Dir(fileName) <> "" Then
        ff = FreeFile
        Open fileName For Binary Access Read As #ff
        GetFileContents = Input(LOF(ff), ff)
        Close #ff
    Else
        MsgBox fileName, vbExclamation, "File Not Found"
    End If
End Function
0

Featured Post

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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now