[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Loading the contents of a csv file into a 2D array - The array x value is being assigned to each array y value

Lets say I have a csv file containing:

Jim                Jims Address              Jims Tel
John              JohnsAddress              JohnsTel

Load this into an array and I get this:

Jim0                Jims Address0              Jims Tel0
John1              JohnsAddress1              JohnsTel1      etc

Two questions: 1) How can I avoid the appended values? 2) see code below at '?????'

The code used:

Dim DB(,) As String
Try
            FileOpen(1, SourceFilePathAndName, OpenMode.Input)
            TotalNumberOfLines = 4 ' ????????????? How can I find this if I don't know it in advance?
            TotalNumberOfColumns = 3
            DB = New String(TotalNumberOfLines - 1, TotalNumberOfColumns - 1) {}
            For i As Integer = 0 To TotalNumberOfLines - 1
                For y As Integer = 0 To TotalNumberOfColumns - 1
                    Input(1, DB(i, y))
                Next y
            Next i

        Catch etc...

        Finally
            FileClose(1)
 End Try
0
IvanHowarth
Asked:
IvanHowarth
  • 2
1 Solution
 
plqCommented:
Using Input seems wrong. Read the whole file into a buffer and then use the split function to split it into records. As long as the file is <10MB you should be fine.

    sRecords = sBuffer.Split(vbCrLf)
    for i = 0 to sRecords.Length
        if sRecord(i) <> "" then
              ...

Then replace every comma which is outside of quotes with a chr(2) or some other character that wont occur in data

Then split the record into fields

       sfield = split(sRecord(i), chr(2))

and process each field in a loop

If you really need it in a 2D loop you can parse it into a new array
0
 
Fernando SotoCommented:
Hi IvanHowarth;

I re-wrote your code to do it in a VB .Net way. Also this code can expand columns and rows without any code re-write. From your sample data it looked as the file is Tab delimited if it delimited by a different character then change in one line in the code, it is commented.

To your two questions
1) How can I avoid the appended values?

I do not see where in your code it is appending a value to the field. You read a line from the file and write it into the array.

2) see code below at '?????'

Answer is in the sample I have given below.

        Dim SourceFilePathAndName As String = "C:\Temp\cvs.txt"
        ' VB .Net way to open and read a file
        Dim sr As New System.IO.StreamReader(SourceFilePathAndName)
        ' Read the whole file into memory and split on the CrLf into an array
        Dim cvsFile() As String = sr.ReadToEnd().Split(CChar(ControlChars.CrLf))
        ' Close the input file
        sr.Close()
        Dim TotalNumberOfLines As Integer = cvsFile.Length - 1
        Dim TotalNumberOfColumns As Integer = 3
        ' Create a multi dim array
        Dim DB(TotalNumberOfLines, TotalNumberOfColumns - 1) As String

        ' Loop through the array of input lines
        For i As Integer = 0 To TotalNumberOfLines
            ' Split on tab deliniter or change to meet the correct delimiter being used
            ' Each field is in its own element of the array
            Dim fields() As String = cvsFile(i).Split(CChar(ControlChars.Tab))
            ' Place elements into the final array
            For y As Integer = 0 To fields.Length - 1
                DB(i, y) = fields(y).Trim()
            Next
        Next


Fernando
0
 
IvanHowarthAuthor Commented:
Thanks!
0
 
Fernando SotoCommented:
Glad I could help. :=)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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