.CSV to DataGridView Rows coming up blank in VB.NET

Question

Here is the CSV data I am trying to import into my VB.NET app:

Raw Data

But when I run it through the app it only populates the last row:

Output

Here's the code for the import:

Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
    DataGridView1.ClearSelection()
    ofd.Filter = "(*csv)|*.csv"
    If ComboBox1.Text = "zVBImportTEST" Then
        If (ofd.ShowDialog() = DialogResult.OK) Then
            txtbxFilePath.Text = ofd.FileName
        End If
        Dim colsexpected As Integer = 6
        Dim thereader As New StreamReader(txtbxFilePath.Text, Encoding.ASCII)
        Dim sline As String = ""
        thereader.ReadLine()
        Do
            sline = thereader.ReadLine
            If sline Is Nothing Then Exit Do
            Dim words() As String = sline.Split(",")
            DataGridView1.Rows.Add("")
            For ix As Integer = 0 To 5
                DataGridView1.Rows(DataGridView1.Rows.Count - 1).Cells(ix).Value = words(ix)
            Next

        Loop
        thereader.Close()
    Else
        MessageBox.Show("Please select a project.", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
    End If
End Sub

I can't seem to figure out why the other rows are coming up blank. If anyone could help me it would be greatly appreciated.

Answer 1

I would not suggest trying to read the data via a StreamReader, instead use the OleDb class while passing the connection string for a CSV file.

Here is a quick example of a function that returns a DataTable based on the contents of the CSV file:

Private Function ConvertCSVToDataTable(ByVal path As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        Try
            con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Text;HDR=YES;FMT=Delimited""", "Microsoft.Jet.OLEDB.4.0", IO.Path.GetDirectoryName(path))
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM " & IO.Path.GetFileName(path), con)
                Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                    con.Open()
                    da.Fill(dt)
                    con.Close()
                End Using
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using

    Return dt
End Function

Then here is how you'd bind your DataGridView:

DataGridView1.DataSource = Me.ConvertCSVToDataTable(ofd.FileName)

Update

Since you want to specify the data type of the DataColumn, declare a DataTable and assign it to the custom function, but then go in after the fact and change the data type of the specific columns. Here is a quick (free-typed and untested) example:

Dim csv As DataTable = Me.ConvertCSVToDataTable(ofd.FileName)
With csv.Columns
    .Items(0).DataType = GetType(Int32)
    .Items(1).DataType = GetType(Int32)
    .Items(4).DataType = GetType(Int32)
    .Items(5).DataType = GetType(Int32)
End With

DataGridView1.DataSource = csv

Answer 2

This is C# but the snippet shows what you are currently doing, and how to do it so that it works.

using System.Collections.Generic;
using System.Windows.Forms;

namespace DataGridNoBinding_47308996
{
    public partial class Form1 : Form
    {

        DataGridView dgv = new DataGridView();
        public Form1()
        {
            InitializeComponent();
            dgv.Dock = DockStyle.Fill;
            dgv.AutoGenerateColumns = false;
            dgv.Columns.Add("Key","Key");
            dgv.Columns.Add("Value", "Value");
            this.Controls.Add(dgv);

            Dictionary<string, string> dgvdata = new Dictionary<string, string>();
            for (int i = 0; i < 10; i++)
            {
                dgvdata.Add($"key{i}", $"value{i}");
            }

            //AddToGridNoWork(dgvdata);
            AddToGridDoesWork(dgvdata);

        }

        /// <summary>
        /// This method does not work. This emulates what you are currently doing.
        /// </summary>
        /// <param name="dgvdata"></param>
        private void AddToGridNoWork(Dictionary<string, string> dgvdata)
        {
            foreach (KeyValuePair<string, string> item in dgvdata)
            {
                dgv.Rows.Add();
                dgv.Rows[dgv.Rows.Count - 1].Cells[0].Value = item.Key;
                dgv.Rows[dgv.Rows.Count - 1].Cells[1].Value = item.Value;
                dgv.Refresh();
            }
        }


        /// <summary>
        /// This method does work.
        /// Add a new row to the Grid and store the new row index in rowindex
        /// Then use the variable rowindex to update the correct row
        /// </summary>
        /// <param name="dgvdata"></param>
        private void AddToGridDoesWork(Dictionary<string, string> dgvdata)
        {
            foreach (KeyValuePair<string, string> item in dgvdata)
            {
                int rowindex = dgv.Rows.Add();
                dgv.Rows[rowindex].Cells[0].Value = item.Key;
                dgv.Rows[rowindex].Cells[1].Value = item.Value;
            }
        }
    }
}

In your VB.net application, it's probably just a matter of doing somehting like this

dim newRowIndex as Integer
newRowIndex = DataGridView1.Rows.Add("")
For ix As Integer = 0 To 5
    DataGridView1.Rows(newRowIndex).Cells(ix).Value = words(ix)
Next

With DataDriver instead Here's my code, re spun using a data driver instead of the StreamReader

using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data;

namespace DataGridNoBinding_47308996
{
    public partial class Form1 : Form
    {

        DataGridView dgv = new DataGridView();
        public Form1()
        {
            InitializeComponent();
            dgv.Dock = DockStyle.Fill;
            this.Controls.Add(dgv);

            Dictionary<string, string> dgvdata = new Dictionary<string, string>();
            for (int i = 0; i < 10; i++)
            {
                dgvdata.Add($"key{i}", $"value{i}");
            }

            //AddToGridNoWork(dgvdata);
            //AddToGridDoesWork(dgvdata);
            //AddToGridUsingDataDriver(@"M:\StackOverflowQuestionsAndAnswers\DataGridNoBinding_47308996\SampleData.csv");
            AddToGridByBindingTheWholeTable(@"M:\StackOverflowQuestionsAndAnswers\DataGridNoBinding_47308996\SampleData.csv");

        }

        /// <summary>
        /// This method will do what you want using a Data Driver instead of a StreamReader
        /// Though, this method binds the whole DataTable to the DataGridView instead of manually creating 1 row per data row
        /// </summary>
        /// <param name="dataFilePath"></param>
        private void AddToGridByBindingTheWholeTable(string dataFilePath)
        {
            dgv.AutoGenerateColumns = true;

            string connstring = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={System.IO.Path.GetDirectoryName(dataFilePath)};Extended Properties=\"Text;HDR=NO;FMT=Delimited\"";
            OleDbConnection conn = new OleDbConnection(connstring);
            OleDbCommand command = new OleDbCommand($"select * from {System.IO.Path.GetFileName(dataFilePath)}", conn);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
            DataTable dt = new DataTable();
            conn.Open();
            dataAdapter.Fill(dt);
            conn.Close();

            dgv.DataSource = dt;
        }



        /// <summary>
        /// This method will do what you want using a Data Driver instead of a StreamReader
        /// In this method, we are actively creating 1 DataGridRow for each DataRow in the DataTable
        /// </summary>
        /// <param name="dataFilePath"></param>
        private void AddToGridUsingDataDriver(string dataFilePath)
        {
            dgv.AutoGenerateColumns = false;
            dgv.Columns.Add("Key", "Key");
            dgv.Columns.Add("Value", "Value");

            string connstring = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={System.IO.Path.GetDirectoryName(dataFilePath)};Extended Properties=\"Text;HDR=NO;FMT=Delimited\"";
            OleDbConnection conn = new OleDbConnection(connstring);
            OleDbCommand command = new OleDbCommand($"select * from {System.IO.Path.GetFileName(dataFilePath)}", conn);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
            DataTable dt = new DataTable();
            conn.Open();
            dataAdapter.Fill(dt);
            conn.Close();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    int newrowid = dgv.Rows.Add();
                    dgv.Rows[newrowid].Cells[0].Value = item.Field<string>(0);
                    dgv.Rows[newrowid].Cells[1].Value = item.Field<string>(1);
                }
            }
            dgv.Refresh();
        }

        /// <summary>
        /// This method does not work. This emulates what you are currently doing.
        /// </summary>
        /// <param name="dgvdata"></param>
        private void AddToGridNoWork(Dictionary<string, string> dgvdata)
        {
            dgv.AutoGenerateColumns = false;
            dgv.Columns.Add("Key","Key");
            dgv.Columns.Add("Value", "Value");

            foreach (KeyValuePair<string, string> item in dgvdata)
            {
                dgv.Rows.Add();
                dgv.Rows[dgv.Rows.Count - 1].Cells[0].Value = item.Key;
                dgv.Rows[dgv.Rows.Count - 1].Cells[1].Value = item.Value;
                dgv.Refresh();
            }
        }


        /// <summary>
        /// This method does work.
        /// Add a new row to the Grid and store the new row index in rowindex
        /// Then use the variable rowindex to update the correct row
        /// </summary>
        /// <param name="dgvdata"></param>
        private void AddToGridDoesWork(Dictionary<string, string> dgvdata)
        {
            dgv.AutoGenerateColumns = false;
            dgv.Columns.Add("Key", "Key");
            dgv.Columns.Add("Value", "Value");

            foreach (KeyValuePair<string, string> item in dgvdata)
            {
                int rowindex = dgv.Rows.Add();
                dgv.Rows[rowindex].Cells[0].Value = item.Key;
                dgv.Rows[rowindex].Cells[1].Value = item.Value;
            }
        }
    }
}