none
c# RRS feed

  • Question

  • How to get .csv file data by start date, start time and end date, end time

    i am getting the .csv file to the Windows Form Application through a link label the code is below

     public static Task<DataTable> CopyCsvToDataTable(string filePath)
            {
                return Task.Run<DataTable>(() => {
    
                    if (!File.Exists(filePath))
                        throw new ArgumentException("File does not exist in the selected path.");
                    StreamReader sr = new StreamReader(filePath);
                    string[] headers = sr.ReadLine().Split(',');
                    var dt = new DataTable();
                    foreach (string header in headers)
                    {
                        dt.Columns.Add(header);
                    }
                    while (!sr.EndOfStream)
                    {
                        string[] rows = Regex.Split(sr.ReadLine(), ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < headers.Length; i++)
                        {
                            if (rows.Length <= i)
                                continue;
                            dr[i] = rows[i];
                        }
                        dt.Rows.Add(dr);
                    }
                    return dt;
                });
    
            }
            public string filepath { get; set; }
            private void LinkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
            {
                
                OpenFileDialog op = new OpenFileDialog();
                op.Filter = "CSV File (*.csv)|*.csv";
                op.InitialDirectory = @"D:\";
                op.Title = "Please select the path of the CSV file";
                linkLabel1.Enabled = false;
                //btnSearch.Enabled = false;
                //btnPrint.Enabled = false;
                Cursor.Current = Cursors.WaitCursor;
                if (op.ShowDialog() == DialogResult.OK)
                {
                    filepath = op.FileName;
                    var m = CopyCsvToDataTable(filepath);
                    DataTable dt = m.Result;
                    dataGridView1.DataSource = dt;
                }
            

    this is one of my where i select data by start date, start time and end date, end time,

    thisi used to get data from MSSQL, how to do it for .csv files

     DateTime starttime = dtpstartdate.Value.Add(dtpstarttime.Value.TimeOfDay);
                        DateTime endtime = dtpenddate.Value.Add(dtpendtime.Value.TimeOfDay);
                        {
                            String cmdString = "select Date, Time, " + cmbMachines.Text + "" +
                                                " from " + cmbTables.Text + "" +
                                                $" where (CONVERT(DATETIME, DATE) + CONVERT(DATETIME, TIME)) between '{ starttime }' and '{ endtime }'";
                            SqlCommand cmd = new SqlCommand(cmdString, con);
                            DataTable dt = new DataTable();
                            SqlDataAdapter da = new SqlDataAdapter(cmd);
                            da.Fill(dt);
                            dataGridView.DataSource = dt;
    
                            con.Open();
                            cmd.ExecuteNonQuery();
                            con.Close();


    Friday, August 16, 2019 4:29 AM

All replies

  • Hi tharminianandasivanesan,

    Thank you for posting here.

    Based on your description, you want get data in datagridview by starttime and endtime.

    You could try the following code.

     public string filepath { get; set; }
            private void LinkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
            {
                OpenFileDialog op = new OpenFileDialog();
                op.Filter = "CSV File (*.csv)|*.csv";
                op.InitialDirectory = @"D:\";
                op.Title = "Please select the path of the CSV file";
                linkLabel1.Enabled = false;
                Cursor.Current = Cursors.WaitCursor;
                if (op.ShowDialog() == DialogResult.OK)
                {
                    filepath = op.FileName;
                    var m = CopyCsvToDataTable(filepath);
                    DataTable dt = m.Result;
                    DateTime starttime = dateTimePicker1.Value.Add(dateTimePicker1.Value.TimeOfDay);
                    DateTime endtime = dateTimePicker2.Value.Add(dateTimePicker2.Value.TimeOfDay);
                    DataTable table = dt.Clone();
                    foreach (DataRow row in dt.Rows)
                    {
                        DateTime time = Convert.ToDateTime(row["date"]);
                        if(time<endtime&&time>starttime)
                        {
                            var newRow = table.NewRow();
                            newRow.ItemArray = row.ItemArray.Clone() as object[];
                            table.Rows.Add(newRow);
                        }
                    }
                     dataGridView1.DataSource = table;
                }
            }
            public static Task<DataTable> CopyCsvToDataTable(string filePath)
            {
                return Task.Run<DataTable>(() => {
    
                    if (!File.Exists(filePath))
                        throw new ArgumentException("File does not exist in the selected path.");
                    StreamReader sr = new StreamReader(filePath);
                    string[] headers = sr.ReadLine().Split(',');
                    var dt = new DataTable();
                    foreach (string header in headers)
                    {
                        dt.Columns.Add(header);
                    }
                    while (!sr.EndOfStream)
                    {
                        string[] rows = Regex.Split(sr.ReadLine(), ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < headers.Length; i++)
                        {
                            if (rows.Length <= i)
                                continue;
                            dr[i] = rows[i];
                        }
                        dt.Rows.Add(dr);
                    }
                    return dt;
                });
    
            }

    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, August 16, 2019 6:25 AM
    Moderator
  • i want to fetch data like this 24/6/2019 10:00:00 AM to 7/8/2019 23:00:00 PM

    between these gap data should appear in datagridview

    data is not coming for the above one sir



    Friday, August 16, 2019 6:27 AM
  • Hi 

    Thanks for the feedback.

    If you want to fetch data like this 24/6/2019 10:00:00 AM to 7/8/2019 23:00:00 PM, you need to add another two datetimepicker controls.

    You could refer to the following code.

      public string filepath { get; set; }
            private void LinkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
            {
                OpenFileDialog op = new OpenFileDialog();
                op.Filter = "CSV File (*.csv)|*.csv";
                op.InitialDirectory = @"D:\";
                op.Title = "Please select the path of the CSV file";
                linkLabel1.Enabled = false;
                Cursor.Current = Cursors.WaitCursor;
                if (op.ShowDialog() == DialogResult.OK)
                {
                    filepath = op.FileName;
                    var m = CopyCsvToDataTable(filepath);
                    DataTable dt = m.Result;
                    DateTime starttime = dateTimePicker1.Value.Date + dateTimePicker3.Value.TimeOfDay;
                    DateTime endtime = dateTimePicker2.Value.Date + dateTimePicker4.Value.TimeOfDay;
                    DataTable table = dt.Clone();
                    foreach (DataRow row in dt.Rows)
                    {
                        DateTime time = Convert.ToDateTime(row["date"]);
                        if(time<endtime&&time>starttime)
                        {
                            var newRow = table.NewRow();
                            newRow.ItemArray = row.ItemArray.Clone() as object[];
                            table.Rows.Add(newRow);
                        }
                    }
                     dataGridView1.DataSource = table;
                }
            }
            public static Task<DataTable> CopyCsvToDataTable(string filePath)
            {
                return Task.Run<DataTable>(() => {
    
                    if (!File.Exists(filePath))
                        throw new ArgumentException("File does not exist in the selected path.");
                    StreamReader sr = new StreamReader(filePath);
                    string[] headers = sr.ReadLine().Split(',');
                    var dt = new DataTable();
                    foreach (string header in headers)
                    {
                        dt.Columns.Add(header);
                    }
                    while (!sr.EndOfStream)
                    {
                        string[] rows = Regex.Split(sr.ReadLine(), ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < headers.Length; i++)
                        {
                            if (rows.Length <= i)
                                continue;
                            dr[i] = rows[i];
                        }
                        dt.Rows.Add(dr);
                    }
                    return dt;
                });
    
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                dateTimePicker3.Format = DateTimePickerFormat.Time;
                dateTimePicker4.Format = DateTimePickerFormat.Time;
            }

    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, August 16, 2019 6:48 AM
    Moderator
  • But data is not coming


    Friday, August 16, 2019 6:54 AM
  • Hi 

    Thanks for the feedback.

    It works for me, so you could download my code to check if it works.


    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, August 16, 2019 7:05 AM
    Moderator
  • sorry sir what procedure i did like first select the data through link label and then select the date and time but data didn't come
    Friday, August 16, 2019 7:39 AM
  • Hi 

    Thanks for the feedback.

    I have uploaded it, could you check it?

    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, August 16, 2019 8:01 AM
    Moderator
  •  foreach (DataRow row in dt.Rows)
                    {
                        DateTime time = Convert.ToDateTime(row["date"]);
                        if (time < endtime && time > starttime)
                        {
                            var newRow = table.NewRow();
                            newRow.ItemArray = row.ItemArray.Clone() as object[];
                            table.Rows.Add(newRow);
                        }
                    }
                    dataGridView1.DataSource = table;
    DateTime time = Convert.ToDateTime(row["date"]);
    In above line this error is occurring "String was not recognized as a valid DateTime"
    Friday, August 16, 2019 8:17 AM
  • Hi 

    I don't get the error when I run this code.

    Pic

    CSV file:

    I think it may be related to csv file. I suggest that you could create the same csv file to test it.

    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, August 16, 2019 8:29 AM
    Moderator
  • i am having like this type of data sir, could please check with this data sir

    Date  Time  L1  L2  L3  L12  L23  L31  I1  I2  I3  Frq1  Frq2  Frq3  PF1  PF2  PF3  kW  kVA  kVAr  kWh  kVArh  kVAh  thdV1%  thdV2%  thdV3%
    6/24/2019 0:00:00 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 6.66 0 0
    6/24/2019 0:00:02 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 4.44 0 0
    6/24/2019 0:00:06 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 6.69 0 0
    6/24/2019 0:00:10 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 6.69 0 0
    6/24/2019 0:00:15 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 5.78 0 0
    6/24/2019 0:00:19 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 5.78 0 0
    6/24/2019 0:00:23 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 5.42 0 0
    6/24/2019 0:00:27 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 5.58 0 0
    6/24/2019 0:00:31 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 5.58 0 0
    6/24/2019 0:00:35 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 5.82 0 0
    6/24/2019 0:00:40 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 5.82 0 0
    6/24/2019 0:00:44 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 5.86 0 0
    6/24/2019 0:00:48 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 5.52 0 0
    6/24/2019 0:00:52 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 5.52 0 0
    6/24/2019 0:00:56 235.21 0 0 235.13 0 235.15 0 0 0 50.25 0 0 1 1 1 0 0 0 0 0 0 4.73 0 0
    Friday, August 16, 2019 8:42 AM
  • Hi tharminianandasivanesan,

    Thanks for the feedback.

    I still give the correct result with the same code I provided. I think your csv file has some problems.

    Please download the csv file and use my code to test it again.

    https://1drv.ms/u/s!Ast0x04bo32Wgy9vW3e7N-pFDBwm?e=GXGcCG

    Result:

    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, August 16, 2019 9:07 AM
    Moderator
  • Thank you sir, its working i think so my csv files are having problem how can i sort it out sir
    Friday, August 16, 2019 9:32 AM
  • Hi tharminianandasivanesan,

    Thanks for the feedback.

    >>Thank you sir, its working i think so my csv files are having problem how can i sort it out sir

    For your question, I have two suggestions.

    First, you could recreate a new csv file.

    Second, you could modify my uploaded csv file.

    By the way, your current question has been solved, so you could post "Mark as answer" to the appropriate answer.

    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, August 16, 2019 9:38 AM
    Moderator