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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

705 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

22 Experts available now in Live!

Get 1:1 Help Now