Faster way of reading csv to grid

Question

I have following in Windows Forms .NET 3.5

It works fine for csv with records less than 10,000 but is slower for records above 30,000. Input csv file can can any records between 1 - 1,00,000 records

Code currently used :

/// <summary>
        /// This will import file to the collection object
        /// </summary>
        private bool ImportFile()
        {
            try
            {

                String fName;
                String textLine = string.Empty;
                String[] splitLine;

                // clear the grid view

                accountsDataGridView.Rows.Clear();

                fName = openFileDialog1.FileName;

                if (System.IO.File.Exists(fName))
                {
                    System.IO.StreamReader objReader = new System.IO.StreamReader(fName);

                    do
                    {
                        textLine = objReader.ReadLine();
                        if (textLine != "")
                        {
                            splitLine = textLine.Split(',');
                            if (splitLine[0] != "" || splitLine[1] != "")
                            {
                                accountsDataGridView.Rows.Add(splitLine);
                            }
                        }
                    } while (objReader.Peek() != -1);
                }
                return true;
            }
            catch (Exception ex)
            {
                if (ex.Message.Contains("The process cannot access the file"))
                {
                    MessageBox.Show("The file you are importing is open.", "Import Account", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    MessageBox.Show(ex.Message);
                }

                return false;
            }

        }

Sample Input file :
18906,Y
18908,Y
18909,Y
18910,Y
18912,N
18913,N

Need some advice on optimizing this code for fast reads & view in grid.

Answer 1

List<string[]> rows = File.ReadAllLines("Path").Select(x => x.Split(',')).ToList();
DataTable dt = new DataTable();
dt.Columns.Add("1");
dt.Columns.Add("2");
rows.ForEach(x => {
  dt.Rows.Add(x);
});
dgv.DataSource = dt;

Try that, I suspected that you have some form of column names in the datagrid for now I just made them 1 and 2.

To filter as per your original code use:

List<string[]> rows = File.ReadAllines("Path").Select(x => x.Split(',')).Where(x => x[0] != "" && x[1] != "").ToList();

To get your columns from the DataGridView

  dt.Columns.AddRange(dgv.Columns.Cast<DataGridViewColumn>().Select(x => new DataColumn(x.Name)).ToArray());

Answer 2

Instead of putting the data directly into the grid you should take a look at the VirtualMode of the DataGridView.

In your code you're doing two things at one time (read the file, fill the grid), which leads to your freezed gui. Instead you should put the grid into the virtual mode and read the file within a BackgroundWorker into a list which holds the data for the grid. The background worker can after each line read update the virtual size of the grid, which allows to already see the data while the grid is loading. By using this approach you'll getting a smooth working grid.

Below you'll find an example which just needs to be filled into a form that uses a DataGridView with two text columns, a BackgroundWorker and a Button:

public partial class FormDemo : Form
{
    private List<Element> _Elements;

    public FormDemo()
    {
        InitializeComponent();
        _Elements = new List<Element>();

        dataGridView.AllowUserToAddRows = false;
        dataGridView.AllowUserToDeleteRows = false;
        dataGridView.ReadOnly = true;
        dataGridView.VirtualMode = true;
        dataGridView.CellValueNeeded += OnDataGridViewCellValueNeeded;

        backgroundWorker.WorkerReportsProgress = true;
        backgroundWorker.DoWork += OnBackgroundWorkerDoWork;
        backgroundWorker.ProgressChanged += OnBackgroundWorkerProgressChanged;
        backgroundWorker.RunWorkerCompleted += OnBackgroundWorkerRunWorkerCompleted;
    }

    private void OnBackgroundWorkerDoWork(object sender, DoWorkEventArgs e)
    {
        var filename = (string)e.Argument;

        using (var reader = new StreamReader(filename))
        {
            string line = null;

            while ((line = reader.ReadLine()) != null)
            {
                var parts = line.Split(',');

                if (parts.Length >= 2)
                {
                    var element = new Element() { Number = parts[0], Available = parts[1] };
                    _Elements.Add(element);
                }

                if (_Elements.Count % 100 == 0)
                {
                    backgroundWorker.ReportProgress(0);
                }
            }
        }
    }

    private void OnBackgroundWorkerProgressChanged(object sender, ProgressChangedEventArgs e)
    {
        dataGridView.RowCount = _Elements.Count;
    }

    private void OnBackgroundWorkerRunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
        dataGridView.RowCount = _Elements.Count;
        button.Enabled = true;
    }

    private void OnButtonLoadClick(object sender, System.EventArgs e)
    {
        if (!backgroundWorker.IsBusy
            && DialogResult.OK == openFileDialog.ShowDialog())
        {
            button.Enabled = false;
            backgroundWorker.RunWorkerAsync(openFileDialog.FileName);
        }
    }

    private void OnDataGridViewCellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
    {
        var element = _Elements[e.RowIndex];

        switch (e.ColumnIndex)
        {
            case 0:
                e.Value = element.Number;
                break;

            case 1:
                e.Value = element.Available;
                break;
        }
    }

    private class Element
    {
        public string Available { get; set; }

        public string Number { get; set; }
    }
}

Answer 3

There isn't much to optimize in regards to speed, but following is much more readable. If it is too slow, it probably isn't the method reading the file, but your WinForm that needs to display >30k records.

    accountsDataGridView.Rows.Clear();
    using (FileStream file = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.Read, 4096))
    using (StreamReader reader = new StreamReader(file))
    {
        while (!reader.EndOfStream)
        {
            var fields = reader.ReadLine().Split(',');
            if (fields.Length == 2 && (fields[0] != "" || fields[1] != ""))
            {
                accountsDataGridView.Rows.Add(fields);
            }
        }
    }

Answer 4

You can try to use SuspendLayout() and ResumeLayout() Methods.

From MSDN Documentation "The SuspendLayout and ResumeLayout methods are used in tandem to suppress multiple Layout events while you adjust multiple attributes of the control. For example, you would typically call the SuspendLayout method, then set the Size, Location, Anchor, or Dock properties of the control, and then call the ResumeLayout method to enable the changes to take effect."

accountsDataGridView.SuspendLayout();
accountsDataGridView.Rows.Clear();

// .....
// in the end after you finished populating your grid call

accountsDataGridView.ResumeLayout();