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
            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...

 End Try
Who is Participating?
Fernando SotoRetiredCommented:
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
        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()

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
IvanHowarthAuthor Commented:
Fernando SotoRetiredCommented:
Glad I could help. :=)
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.