Read DAT file and add records between two Dates in C# and VB.Net

Hi kana250688,

First read the data from Dat file and select distinct Ids.

Then loop through the selected date and add the records for each id by checking the dat file records.

Refer below sample and modify as per your requirement and data present in Dat file.

The Dat File

Form Design

The Form consists of:

DatePicker - For selecting Date.

OpenFileDialogBox - For for filtering the record.

DataGridView - For displaying the records.

Button - For Reading and filtering the Dat file records.

Namespaces

You need to inherit the following namespaces.

C#

using System.Data;
using System.IO;
using System.Linq;

VB.Net

Imports System.Data
Imports System.IO
Imports System.Linq

Property Class 

C#

public class Attendance
{
    public string ID { get; set; }
    public string DATE { get; set; }
    public string TIME { get; set; }
    public string FP { get; set; }
    public string IN_OUT { get; set; }
    public string OTHERS1 { get; set; }
    public string OTHERS2 { get; set; }
}

VB.Net

Public Class Attendance
    Public Property ID As String
    Public Property [DATE] As String
    Public Property TIME As String
    Public Property FP As String
    Public Property IN_OUT As String
    Public Property OTHERS1 As String
    Public Property OTHERS2 As String
End Class

Code

C#

private void btnFilter_Click(object sender, EventArgs e)
{
    DateTime dtStart = Convert.ToDateTime(startDate.Value).Date;
    DateTime dtEnd = Convert.ToDateTime(endDate.Value).Date;
    // Opening FileDialog to select Dat File.
    openFileDialog1.InitialDirectory = @"D:\Files";
    openFileDialog1.Filter = "Dat files(*.Dat)|*.Dat";
    openFileDialog1.RestoreDirectory = true;
    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        List<Attendance> attendances = new List<Attendance>();
        // Reading all the Data from Dat File.
        string[] lines = File.ReadAllLines(openFileDialog1.FileName);
        for (int i = 0; i <= lines.Length - 1; i++)
        {
            if (!string.IsNullOrEmpty(lines[i]))
            {
                string[] data = lines[i].Split('\t');
                attendances.Add(new Attendance
                {
                    ID = data[0],
                    DATE = data[1],
                    TIME = data[2],
                    FP = data[3],
                    IN_OUT = data[4].ToString() == "0" ? "IN" : "OUT",
                    OTHERS1 = data[5],
                    OTHERS2 = data[6]
                });
            }
        }
        List<Attendance> attendances_New = new List<Attendance>();
        // Selecting distinct Ids.
        string[] ids = attendances.Select(x => x.ID).Distinct().ToArray();
        for (DateTime dt = dtStart; dt <= dtEnd; dt = dt.AddDays(1))
        {
            foreach (string id in ids)
            {
                Attendance inAttendance = attendances
                    .Where(x => Convert.ToDateTime(x.DATE) == dt && x.IN_OUT.ToUpper() == "IN" && x.ID == id).FirstOrDefault();
                Attendance outAttendance = attendances
                    .Where(x => Convert.ToDateTime(x.DATE) == dt && x.IN_OUT.ToUpper() == "OUT" && x.ID == id).FirstOrDefault();
                if (inAttendance != null)
                {
                    attendances_New.Add(inAttendance);
                }
                if (outAttendance != null)
                {
                    attendances_New.Add(outAttendance);
                }
                if (inAttendance == null)
                {
                    attendances_New.Add(new Attendance
                    {
                        ID = id,
                        DATE = dt.ToString("yyyy-MM-dd"),
                        IN_OUT = "IN"
                    });
                }
                if (outAttendance == null)
                {
                    attendances_New.Add(new Attendance
                    {
                        ID = id,
                        DATE = dt.ToString("yyyy-MM-dd"),
                        IN_OUT = "OUT"
                    });
                }
            }
        }

        dataGridView1.DataSource = attendances_New;
    }
}

VB.Net

Private Sub btnFilter_Click(sender As Object, e As EventArgs) Handles btnFilter.Click
    Dim dtStart As DateTime = Convert.ToDateTime(startDate.Value).Date
    Dim dtEnd As DateTime = Convert.ToDateTime(endDate.Value).Date
    ' Opening FileDialog to select Dat File.
    openFileDialog1.InitialDirectory = "D:\Files"
    openFileDialog1.Filter = "Dat files(*.Dat)|*.Dat"
    openFileDialog1.RestoreDirectory = True
    If openFileDialog1.ShowDialog() = DialogResult.OK Then
        Dim attendances As List(Of Attendance) = New List(Of Attendance)()
        ' Reading all the Data from Dat File.
        Dim lines As String() = File.ReadAllLines(openFileDialog1.FileName)
        For i As Integer = 0 To lines.Length - 1
            If Not String.IsNullOrEmpty(lines(i)) Then
                Dim data As String() = lines(i).Split(vbTab)
                attendances.Add(New Attendance With {
                    .ID = data(0),
                    .DATE = data(1),
                    .TIME = data(2),
                    .FP = data(3),
                    .IN_OUT = If(data(4).ToString() = "0", "IN", "OUT"),
                    .OTHERS1 = data(5),
                    .OTHERS2 = data(6)
                })
            End If
        Next
        Dim attendances_New As List(Of Attendance) = New List(Of Attendance)()
        ' Selecting distinct Ids.
        Dim ids As String() = attendances.[Select](Function(x) x.ID).Distinct().ToArray()
        Dim dt As DateTime = dtStart
        While dt <= dtEnd
            For Each id As String In ids
                Dim inAttendance As Attendance = attendances.
                    Where(Function(x) Convert.ToDateTime(x.DATE) = dt AndAlso x.IN_OUT.ToUpper() = "IN" AndAlso x.ID = id).FirstOrDefault()
                Dim outAttendance As Attendance = attendances.
                    Where(Function(x) Convert.ToDateTime(x.DATE) = dt AndAlso x.IN_OUT.ToUpper() = "OUT" AndAlso x.ID = id).FirstOrDefault()

                If inAttendance IsNot Nothing Then
                    attendances_New.Add(inAttendance)
                End If

                If outAttendance IsNot Nothing Then
                    attendances_New.Add(outAttendance)
                End If

                If inAttendance Is Nothing Then
                    attendances_New.Add(New Attendance With {
                    .ID = id,
                    .DATE = dt.ToString("yyyy-MM-dd"),
                    .IN_OUT = "IN"
                })
                End If

                If outAttendance Is Nothing Then
                    attendances_New.Add(New Attendance With {
                    .ID = id,
                    .DATE = dt.ToString("yyyy-MM-dd"),
                    .IN_OUT = "OUT"
                })
                End If
            Next

            dt = dt.AddDays(1)
        End While

        dataGridView1.DataSource = attendances_New
    End If
End Sub

Screenshot