Solved

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

Posted on 2006-07-06
4
258 Views
Last Modified: 2012-06-27
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
Comment
Question by:IvanHowarth
  • 2
4 Comments
 
LVL 8

Expert Comment

by:plq
ID: 17050243
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
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 17050900
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
 

Author Comment

by:IvanHowarth
ID: 17064431
Thanks!
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 17064772
Glad I could help. :=)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

910 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now