0

while migrating an Excel-VBA project to Visual Basic 2010, I came across a problem when populating arrays.

In Excel-VBA I would do something like

Function mtxCorrel() As Variant
    mtxCorrel = wsCorr.UsedRange
End Function

to read an m*n-matrix (in this case n*n), that is conveniently stored in a worksheet, into an array for further use.

In VB2010 I obviously won't use an Excel-Worksheet as storage. csv-Files (see below) seem like a decent alternative.

I want to populate an 2d-array with the csv-contents without looping n*n-times. Let's assume I already know n=4 for demonstration purposes.

This suggests that what I want to do cant be done.

Nevertheless I still hope something like the following could work:

Function mtxCorrel() As Object

    Dim array1(4, 4) As String

    Using ioReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\cm_KoMa.csv")

        With ioReader
            .TextFieldType = FileIO.FieldType.Delimited
            .SetDelimiters(";")

          ' Here I want to...
          ' A) ...either populate the whole 2d-array with something like
            array1 = .ReadToEnd()

          ' B) ... or populate the array by looping its 1d-"rows"
            While Not .EndOfData
                array1(.LineNumber, 0)= .ReadFields()
            End While

        End With
    End Using

    return array1
End Function

Notes:

  • I'm mainly interested in populating the array.
  • I'm less interested in potential errors with determining which csv-line belongs into which 1d-"row", and also not interested in checking n.

Appendix: sample csv-File:

1;0.5;0.9;0.3
0.5;1;0.6;0.2
0.9;0.6;1;0.1
0.3;0.2;0.1;1
Community
  • 1
  • 1
Martin Dreher
  • 1,514
  • 2
  • 12
  • 22
  • Did you see the `ReadAllLines` method? See here: https://msdn.microsoft.com/en-us/library/s2tte0y1(v=vs.110).aspx for an example. – Ryan Wildry Sep 07 '16 at 12:30
  • [Here](http://stackoverflow.com/questions/9821013/handle-large-delimited-text-files-in-vba) is something that might be of interest – Zac Sep 07 '16 at 12:42
  • @RyanWildry Afaik `ReadAllLines` does basically what `TextFieldParser.ReadToEnd` does (returns the file contents in a single string), but without e.g. the option to skip comments with `.CommentTokens`. Either way, I am still stuck with a String from which I want to populate an array. – Martin Dreher Sep 07 '16 at 13:00
  • @MartinDreher, it returns a `String Array`. From the docs `Return Value Type: System.String() A string array containing all lines of the file.` – Ryan Wildry Sep 07 '16 at 13:06
  • 1
    What exactly is wrong with looping over the rows and columns? You were going to do data validation, right? Also, why use an array at all? Make an object `Foo` for what the row represents, give it a constructor that parses a single line, and get a `List` with `string.Split` and build the list by passing rows to the `Foo` ctor. – Comintern Sep 07 '16 at 13:09
  • @RyanWildry you are perfectly right, I misread that. Still, the need to iterate `n*n` times when populating the array remains – Martin Dreher Sep 07 '16 at 13:09
  • I'm not sure how that method works, but it's a single call. There isn't 2 or more levels of iteration needed to achieve this. `Dim readText() As String = File.ReadAllLines(path)` Assigns to an array without iteration, which I think addresses your question: `I want to populate an 2d-array with the csv-contents without looping n*n-times`. It populates an array in one line. – Ryan Wildry Sep 07 '16 at 13:17
  • You could create an ADO connection to fill a Recordset and use the GetRows method of the Recordset get the array of data. `array1 = rs.GetRows` –  Sep 07 '16 at 13:22
  • @Comintern Thanks. Sadly, using an object to store several 1d-arrays isn't an option - the results will be passed to functions with `n*m`-array-parameters (think of the Matrix-`WorksheetFunctions`). Part of this will be the data validation, which would be ill-placed at this point. I'm reluctant to loop rows and columns since a) it seems like a step backwards from the existing solution in excel-vba and b) I was hoping that there is still a way to render an array 1-based in VB.net (as `mtxCorrel = wsCorr.UsedRange` does in VBA) – Martin Dreher Sep 07 '16 at 13:35
  • I'm not sure what you think Excel does when it reads a csv file (or xlsm for that matter), but I'd bet good money that part of it is looping through rows and columns. You can also easily perform matrix operations on Lists using Linq. Just because the VBA code isn't object oriented doesn't mean that the VB.NET code can't be. Porting code isn't simple transciption - write code that takes advantage of the target language's features. If you don't, you not only pass on all the tech debt - you pay interest on it. – Comintern Sep 07 '16 at 13:43
  • @RyanWildry Thank you for the further explanation. Still, `.ReadAllLines` returns a 1d-array, not a 2d one. I guess I will resort to looping 'rows' and 'columns' separately. – Martin Dreher Sep 07 '16 at 13:51
  • @Comintern I'm pretty certain excel loops - still, IF there was a pre-defined method in vb.net similar to the one in VBA (which, I begin to assume, there isn't) why not consider using it? I will, however look into LINQ - thanks for hinting in that direction. – Martin Dreher Sep 07 '16 at 14:12

0 Answers0