VB.NET: Reading a CSV file into a 2D array

I'm working on a project that requires me to take values from a CSV file. I've to do further processing with these values and it'd be great if I can have these values in a 2D array. The number of rows and columns of the CSV files changes at regular intervals.

I'm unable to take these values into a 2D array in VB.NET/C#. Can I please have some help on that?

Here the code that I used:

Imports System.IO

Public Class Form1
    Private Sub ReadCSVFileToArray()
        Dim strfilename As String
        Dim num_rows As Long
        Dim num_cols As Long
        Dim x As Integer
        Dim y As Integer
        Dim strarray(1, 1) As String

        ' Load the file.
        strfilename = "test.csv"

        'Check if file exist
        If File.Exists(strfilename) Then
            Dim tmpstream As StreamReader = File.OpenText(strfilename)
            Dim strlines() As S开发者_C百科tring
            Dim strline() As String

            strlines = tmpstream.ReadToEnd().Split(Environment.NewLine)

            ' Redimension the array.
            num_rows = UBound(strlines)
            strline = strlines(0).Split(",")
            num_cols = UBound(strline)
            ReDim strarray(num_rows, num_cols)

            ' Copy the data into the array.
            For x = 0 To num_rows
                strline = strlines(x).Split(",")
                For y = 0 To num_cols
                    strarray(x, y) = strline(y)
                Next
            Next

            ' Display the data in textbox

            For x = 0 To num_rows
                For y = 0 To num_cols
                    TextBox1.Text = TextBox1.Text & strarray(x, y) & ","
                Next
                TextBox1.Text = TextBox1.Text & Environment.NewLine
            Next

        End If
    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ReadCSVFileToArray()
    End Sub
End Class

You use the CsvReader from here to easily and conveniently read a csv (or other similar data) into a DataTable (or an IDataReader). It is always my first choice for this scenario - fast and pretty robust.

I'm not disagreeing with Marc Gravell, because you shouldn't try to re-invent the wheel. His solution would perform the best, and there can be many CSV format nuances that can screw up a simple parser such as the one demonstrated below.

With that said, you asked for a CSV parser that returns a 2D array. The code below does just that (jagged array), and should work for a very simple CSV file. This skips a header row in the file.

(sorry it's not in VB, but you can put it in a helper class in your project)

private string[][] GetData(string fileName)
{
  List<string[]> data = new List<string[]>();

  using (StreamReader sr = new StreamReader(fileName))
  {
    bool headerRow = true;
    string line;

    while ((line = sr.ReadLine()) != null)
    {
      if (headerRow)
      {
        headerRow = false;
      }
      else
      {
        string[] split = line.Split(new char[] { ',' });

        data.Add(split);
      }
    }
  }

  return data.ToArray();
}

Parsing a CSV file can be pretty hard due to all the possible variations of CSV. See: http://en.wikipedia.org/wiki/Comma-separated_values

E.g., think about embedded quotes, commas etc. So it will not be a simple matter of reading lines/strings and splitting them.

Perhaps you are better of by using a third party library

Since the number of columns and rows change frequently, you could use dynamic lists rather than a fixed 2D array.

List<List<string>> data = new List<List<string>>();

Then as you parse the CSV file you can build up the data dynamically in the above structure, adding a new List<string> to data for each new row in the CSV file.

Update: The VB.NET equivalent is something like

data As New List(Of List(Of String))

That being said, @Mark Gravell's suggestion is a far better solution than doing this yourself.