How can i get total hours and miniuts form txt files

Feroz Khan

5y876C#

Dear All,

I have one txt where have data user punch In and Punch Out but problem is both in one columns and there is not seprate defination like this entry for out and this entry for in there is only two columsn one for Emp_ID and second for datetime.

How can read that and calculate the time of each employee date wise like how many hourse and minuts and there can be possible someone punched only once so i need to defined no out time punched and there can be possible mutliple in and out so i need to check date wise each employees timeing and want to calculate how many hours and minuts.

43814   2021-09-01 06:02:58
800000000   2021-09-01 07:14:15
20140426   2021-09-01 07:27:44
43814   2021-09-01 07:29:40
20140426   2021-09-01 07:45:08
20140426   2021-09-01 08:05:51
20159977   2021-09-01 08:23:18
20140215   2021-09-01 08:27:45
103585732   2021-09-01 08:29:55
20140163   2021-09-01 08:37:21
20140319   2021-09-01 08:50:27
2014034   2021-09-01 08:59:13
2014086   2021-09-01 09:02:45
20140321   2021-09-01 09:18:15

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
first, the number of entries should always be even, which means if an employee first punch-in then goes for lunch (punch out) then comes from lunch (punch in) then finally, the shift ends and he goes home (punch out).
so total time= Sum(punchout-punchIn) on particular day
for now, based on max and min, the below code will give you the time spent by employee on a particular day in minutes
string[] lines = System.IO.File.ReadAllLines(@"D:\CheckPDF\Testtxt.txt");

           // Display the file contents by using a foreach loop.
           System.Console.WriteLine("Contents of file = ");
           List emps = new List();
           foreach (string line in lines)
           {
               string[] fields = line.Split(new char[] { ' ', '\t' }, StringSplitOptions.RemoveEmptyEntries);

               Employee emp = new Employee()
               {
                   Id=Convert.ToInt32(fields[0].ToString()),
                   Date = Convert.ToDateTime(fields[1]).ToString("MMMM dd"),
                   TimeSpent = Convert.ToDateTime(fields[2]).TimeOfDay,
               };
               emps.Add(emp);
           }

           var res = emps.GroupBy(c => new { Date = c.Date, Type = c.Id})
           .Select(g => new
                  {
                      Date = g.Key.Date,
                      Type = g.Key.Type,
                     // totalSpan = new TimeSpan(g.Sum(r => r.TimeSpent.Ticks)),
                      totalSpan =g.Max(x=>x.TimeSpent).Subtract(g.Min(x=>x.TimeSpent)).Minutes

                  }
           );
Sachin Singh5y ago
This is how you could display in gridview
string[] lines = System.IO.File.ReadAllLines(@"D:\CheckPDF\Testtxt.txt");

           // Display the file contents by using a foreach loop.
           System.Console.WriteLine("Contents of file = ");
           List emps = new List();
           foreach (string line in lines)
           {
               string[] fields = line.Split(new char[] { ' ', '\t' }, StringSplitOptions.RemoveEmptyEntries);

               Employee emp = new Employee()
               {
                   Id=Convert.ToInt32(fields[0].ToString()),
                   Date = Convert.ToDateTime(fields[1]).ToString("MMMM dd"),
                   TimeSpent = Convert.ToDateTime(fields[2]).TimeOfDay,
               };
               emps.Add(emp);
           }

           var res = emps.GroupBy(c => new { Date = c.Date, Type = c.Id})
           .Select(g => new  EmployeeVM
                  {
                      Date = g.Key.Date,
                      ID= g.Key.Type,
punchInFirst =g.Min(x=>x.TimeSpent),
punchOutLast =g.Max(x=>x.TimeSpent),

                     // totalSpan = new TimeSpan(g.Sum(r => r.TimeSpent.Ticks)),
                      timeSpent =g.Max(x=>x.TimeSpent).Subtract(g.Min(x=>x.TimeSpent)).Minutes

                  }
           );

GridView1.DataSource=res;
GridView1.DataBind();


public class EmployeeVM
        {
            public int Id { get; set; }
            public string Date { get; set; }
            public TimeSpan punchInFirst { get; set; }
            public TimeSpan punchOutLast { get; set; }
            public int timeSpent{get;set;}
        }

public class Employee
        {
            public int Id { get; set; }
            public string Date { get; set; }
            public TimeSpan TimeSpent { get; set; }
        }
Feroz Khan5y ago
Yes now i can see total miniuts and hours this is Really just one thing now how to display girdview with sperate column like firstin and last out then total hours?
Feroz Khan5y ago
Dear Sachin,

I am getting data like that how can i get total hours and minuts like how many hours employee spend during between times or if possible i want to get first row and last row employee wise and date wise then we will calculate totat hours?
Sachin Singh5y ago
To view in grid view, you can simply bind the list to gridview
public void BindGrid()
{

string[] lines = System.IO.File.ReadAllLines(@"D:\CheckPDF\Testtxt.txt");

            // Display the file contents by using a foreach loop.
            System.Console.WriteLine("Contents of file = ");
            List emps = new List();
            foreach (string line in lines)
            {
                string[] fields = line.Split(new char[] { ' ', '\t' }, StringSplitOptions.RemoveEmptyEntries);

                Employee emp = new Employee()
                {
                    Id=Convert.ToInt32(fields[0].ToString()),
                    Date = Convert.ToDateTime(fields[1]).ToString("MMMM dd"),
                    TimeSpent = Convert.ToDateTime(fields[2]).TimeOfDay,
                };
                emps.Add(emp);
            }

GridView1.DataSource= emps;
GridView1.DataBind();

}

public class Employee
        {
            public int Id { get; set; }
            public string Date { get; set; }
            public TimeSpan TimeSpent { get; set; }
        }
To insert rows into database,
using(SqlConnection con= new SqlConnection(cs))
{
SqlCommand cmd= new SqlCommand("insert into EmployeeTable values @Date, @TimeSpent",cs);
foreach(var emp in emps)
{
cmd.Parameters.AddWithValue("@Date",emp.Date);
cmd.Parameters.AddWithValue("@EntryTime",emp.TimeSpent);
con.Open();
cmd.ExecuteNonQuery();
}

}
Feroz Khan5y ago
Dear Sachin,

Thank you for your reply and below code yes its working but how can i get that data in datatable to insert database table or view gridview?
Sachin Singh5y ago
this is impossible to calculate until you don't know which is punchin time and which is punchout time. for example
20140426 2021-09-01 07:27:44 (punchIn)
20140426 2021-09-01 07:45:08 (punchout)
20140426 2021-09-01 08:05:51 (punchin) then what happened he never left the office? also, you should mention AM and PM.

for, getting 3 columns date, time and id you can use this
string[] lines = System.IO.File.ReadAllLines(@"D:\CheckPDF\Testtxt.txt");

            // Display the file contents by using a foreach loop.
            System.Console.WriteLine("Contents of file = ");
            List emps = new List();
            foreach (string line in lines)
            {
                string[] fields = line.Split(new char[] { ' ', '\t' }, StringSplitOptions.RemoveEmptyEntries);

                Employee emp = new Employee()
                {
                    Id=Convert.ToInt32(fields[0].ToString()),
                    Date = Convert.ToDateTime(fields[1]).ToString("MMMM dd"),
                    TimeSpent = Convert.ToDateTime(fields[2]).TimeOfDay,
                };
                emps.Add(emp);
            }
public class Employee
        {
            public int Id { get; set; }
            public string Date { get; set; }
            public TimeSpan TimeSpent { get; set; }
        }