none
How can i fetch csv to datagridview between only those dates we select (from and to) RRS feed

  • Question

  • Hi,

    I have a from and to date pickers(in a group box) and datagridview in form1.

    I am reading multiple csv file in form3 and getting the values of test columns in datagridview in form1.

    I want to display only those tests between specific dates.

    my CSV file format


    Date Test_name Test_value
    01-05-19     Test6 6.99
    02-05-19     Test6 7.34
    03-05-19     Test7 8.36
    04-05-19     Test8 6.38
    05-05-19     Test9 7
    06-05-19     Test10 7

    My form3 code

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.IO;

    namespace checkedlistboxfetch
    {
        public partial class Form3 : Form
        {
            public Form3()
            {
                InitializeComponent();
            }

            string filename = null;
            DataTable dt = new DataTable();

            private void Bttn_Brwse_Click(object sender, EventArgs e)
            {
                OpenFileDialog openfiledialog1 = new OpenFileDialog();
                openfiledialog1.Multiselect = true;
                openfiledialog1.Filter = "comma seperated value | *.CSV|Excel 97-2003 WorkBook|*.xls|Excel WorkBook|*.xlsx|All Excel Files|*.xls,*.xlsx|All Files|*.*";

                if (openfiledialog1.ShowDialog() == DialogResult.OK)
                {
                    string strfilename = openfiledialog1.FileName;
                    filename = openfiledialog1.FileName;
                    for (int fi = 0; fi < openfiledialog1.FileNames.Length; fi++)
                    {
                        ListBoxFiles.Items.Add(openfiledialog1.FileNames[fi].ToString());
                        txtBoxDrctry.Text = strfilename;
                        Bttn_Brwse.Enabled = false;
                    }
                }
            }

            public static DataTable OpenCSV(string filePath)
            {
                DataTable dt = new DataTable();
                FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

                StreamReader sr = new StreamReader(fs);
                string strLine = "";
                string[] aryLine = null;
                string[] tableHead = null;
                int columnCount = 0;
                bool IsFirst = true;
                while ((strLine = sr.ReadLine()) != null)
                {
                    if (IsFirst == true)
                    {
                        tableHead = strLine.Split(',');
                        IsFirst = false;
                        columnCount = tableHead.Length;
                        for (int i = 0; i < columnCount; i++)
                        {
                            DataColumn dc = new DataColumn(tableHead[i]);
                            dt.Columns.Add(dc);
                        }
                    }
                    else
                    {
                        aryLine = strLine.Split(',');
                        DataRow dr = dt.NewRow();
                        for (int j = 0; j < columnCount; j++)
                        {
                            dr[j] = aryLine[j];
                        }
                        dt.Rows.Add(dr);
                    }
                }
                if (aryLine != null && aryLine.Length > 0)
                {
                    dt.DefaultView.Sort = tableHead[0] + " " + "asc";
                }
                sr.Close();
                fs.Close();
                return dt;
            }

            Form1 form1 = (Form1)Application.OpenForms["Form1"];
            private void Bttn_OK_Click_1(object sender, EventArgs e)
            {
                foreach (string item in ListBoxFiles.Items)
                {

                    dt = OpenCSV(item);
                    foreach (DataRow dr in dt.Rows)
                    {
                        form1.CheckedList.Items.Add(dr["Test_name"]);

                    }
                }
                List<string> list = new List<string>();
                foreach (var item in form1.CheckedList.Items)
                {
                    list.Add(item.ToString());
                }
                for (int i = 0; i < list.Count; i++)
                {
                    for (int j = list.Count - 1; j > i; j--)
                    {
                        if ((list[i].ToUpper() == list[j].ToUpper()) && (i != j))
                        {
                            form1.CheckedList.Items.Remove(list[j]);
                        }
                    }
                }

                // Displays Test Columns from the file //
                foreach (var item in form1.CheckedList.Items)
                {
                    DataGridViewTextBoxColumn testcol = new DataGridViewTextBoxColumn();
                    testcol.Name = item.ToString();
                    testcol.HeaderText = item.ToString();
                    form1.gridView.Columns.Add(testcol);
                }

                form1.gridView.Rows.Add();
                form1.gridView.Rows.Add();

                int count = 0;
                foreach (string item in ListBoxFiles.Items)
                {
                    dt = OpenCSV(item);
                    foreach (DataRow dr in dt.Rows)
                    {

                        string val = dr["Test_name"].ToString();
                    A: if (form1.gridView.Rows[count].Cells[val].Value == null)
                        {
                            form1.gridView.Rows[count].Cells[val].Value = dr["Test_value"].ToString();
                            count = 0;
                            int y = 0;
                            Panel panel = new Panel();
                            panel.Parent = form1.GBox;
                            panel.Dock = DockStyle.Fill;
                            panel.AutoScroll = true;
                            foreach (var radio in form1.CheckedList.Items)
                            {
                                RadioButton radioButton = new RadioButton();
                                radioButton.Parent = panel;
                                radioButton.Name = radio.ToString();
                                radioButton.Text = radio.ToString();
                                radioButton.Location = new Point(5, y);
                                y += radioButton.Height;

                                this.Close();
                            }
                        }

                        else
                        {
                            count++;
                            goto A;

                        }
                    }
                }
            }

            private void Bttn_Reset_Click(object sender, EventArgs e)
            {
                txtBoxDrctry.Text = "";
                ListBoxFiles.Items.Clear();
                Bttn_Brwse.Enabled = true;
            }
        }
    }




         
            
        



    Akshay

    Friday, May 10, 2019 6:05 AM

Answers

  • Hi anonymous_3210,

    Thank you for posting here.

    According to your description, you want to fetch csv to datagridview between two datetimepicker's value.

    You could try to add the following code.

    Form1

    public DateTimePicker StartPicker
            {
    
                get
                {
                    return StartTimePicker;
                }
                set
                {
                    StartTimePicker = value;
                }
            }
    
            public DateTimePicker EndPicker
            {
    
                get
                {
                    return EndTimePicker;
                }
                set
                {
                    EndTimePicker = value;
                }
            }

    In form3

     foreach (DataRow dr in dt.Rows)
                {
                    string val = dr["Test_name"].ToString();
                    DateTime time = Convert.ToDateTime(dr["Date"]);
                    DateTime t1 = Convert.ToDateTime(form1.StartPicker.Value);
                    DateTime t2 = Convert.ToDateTime(form1.EndPicker.Value);
                A: if((form1.gridView.Rows[count].Cells[val].Value==null))
                    {
                        if((time>=t1) && (time<=t2))
                        {
                            form1.gridView.Rows[count].Cells[val].Value = dr["Test_value"].ToString();
                            count = 0;
                        }
                      
                    }
                    else
                    {
                        count++;
                        goto A;
                    }
                    
    
                }

    Result:

    If you have trouble in my code, you could download it from here.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 10, 2019 7:10 AM
    Moderator

All replies

  • Hi anonymous_3210,

    Thank you for posting here.

    According to your description, you want to fetch csv to datagridview between two datetimepicker's value.

    You could try to add the following code.

    Form1

    public DateTimePicker StartPicker
            {
    
                get
                {
                    return StartTimePicker;
                }
                set
                {
                    StartTimePicker = value;
                }
            }
    
            public DateTimePicker EndPicker
            {
    
                get
                {
                    return EndTimePicker;
                }
                set
                {
                    EndTimePicker = value;
                }
            }

    In form3

     foreach (DataRow dr in dt.Rows)
                {
                    string val = dr["Test_name"].ToString();
                    DateTime time = Convert.ToDateTime(dr["Date"]);
                    DateTime t1 = Convert.ToDateTime(form1.StartPicker.Value);
                    DateTime t2 = Convert.ToDateTime(form1.EndPicker.Value);
                A: if((form1.gridView.Rows[count].Cells[val].Value==null))
                    {
                        if((time>=t1) && (time<=t2))
                        {
                            form1.gridView.Rows[count].Cells[val].Value = dr["Test_value"].ToString();
                            count = 0;
                        }
                      
                    }
                    else
                    {
                        count++;
                        goto A;
                    }
                    
    
                }

    Result:

    If you have trouble in my code, you could download it from here.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 10, 2019 7:10 AM
    Moderator
  • Hi,

    That was amazing. Thank you so much!!


    Akshay

    Friday, May 10, 2019 7:24 AM