VB.NET Custom import excel to Datagridview

[Andrei_11]

Hello all!
I have an excel file that looks like this:

Time of Day Lap Lap Tm  Speed
14 - Stoicescu Dan - Regularitate Coupe         
9:40:50.835 1   2:47.462    88.763
9:43:37.584 2   2:46.749    89.142
9:46:33.319 3   2:55.735    84.584
9:49:58.901 4   3:25.582    72.304
9:53:13.988 5   3:15.087    76.194
9:55:50.639 6   2:36.651    94.889
9:58:49.884 7   2:59.245    82.928
10:02:33.328    8   3:43.444    66.524
20 - Dima Viorel - Regularitate Coupe           
9:40:56.287 1   2:46.284    89.392
9:43:39.673 2   2:43.386    90.977
9:46:18.075 3   2:38.402    93.84
9:48:57.580 4   2:39.505    93.191
9:51:38.104 5   2:40.524    92.599
9:54:18.371 6   2:40.267    92.748
9:57:01.825 7   2:43.454    90.939
9:59:51.986 8   2:50.161    87.355
10:02:33.040    9   2:41.054    92.295
10:05:35.524    10  3:02.484    81.456
10:08:55.828    11  3:20.304    74.209
12 - Cobori Stefan - Regularitate Sedan         
9:41:18.824 1   3:07.493    79.28
9:44:10.809 2   2:51.985    86.428
9:46:55.658 3   2:44.849    90.17

what i need is to import this in datagridview like this:

 NO NAME LAP TIME CLASS
    20  Dima Viorel 1   00:02:46.328    Coupe
    20  Dima Viorel 2   00:02:38.169    Coupe
    20  Dima Viorel 3   00:02:40.080    Coupe
    20  Dima Viorel 4   00:02:44.595    Coupe
    43  Caraban Bogdan  1   00:03:25.701    Cabrio
    43  Caraban Bogdan  2   00:02:45.295    Cabrio
    43  Caraban Bogdan  3   00:02:45.642    Cabrio
    14  Stoicescu Dan   1   00:03:06.488    Coupe
    14  Stoicescu Dan   2   00:02:49.431    Coupe

where no name and class must be extracted from single cell in excell see in first codeblock

 14 - Stoicescu Dan - Regularitate Coupe

the times and laps are in next rows after the row with the cell that contains the no name and class until next competitor

i know how to import in datagridview but i do not know how to parse
please help
Code for populate datagridview with excell sheet as it is:

Private Sub Button4_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Retro Racing Et. 1 - Regularitate - laptimes.xls';Extended Properties='Excel 12.0;HDR=YES'")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select `Time of Day`,Lap,`Lap Tm` from [Sheet1$] ", MyConnection)

        MyCommand.TableMappings.Add("Table", "RAW")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridView4.DataSource = DtSet.Tables(0)
        MyConnection.Close()

    End Sub

[bluenunn]

Greetings

Reply to your question is in "how_to_create_race_report_from_raw_input_file_r001.txt":

 - download "how_to_procs_r001.zip" @ GitHub repository: https://github.com/bluenunn/Toolchest/

If you have any questions regarding this solution shoot me an email. Hope this helps :-)

Regards,

Bill Nunn

Email: [e-mail]

[Andrei_11]

Solution

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DataGridView1.SuspendLayout()
DataGridView1.SuspendLayout()
        ListBox1.SuspendLayout()
        Dim fso As Object
        fso = CreateObject("Scripting.FileSystemObject")
        Dim sourceFile As Object
        Dim myFilePath As String
        myFilePath = "xxxxxx\Desktop\new 1.txt"
        sourceFile = fso.OpenTextFile(myFilePath, ForReading)
        Dim line As String
        Dim lineNume() As String

        sourceFile = fso.OpenTextFile(myFilePath, ForReading)
        Dim sFileName As String
        Dim srFileReader As System.IO.StreamReader

        sFileName = "xxxxxxx\Desktop\new 1.txt"
        srFileReader = System.IO.File.OpenText(sFileName)
        line = srFileReader.ReadLine()
        While Not sourceFile.AtEndOfStream ' while we are not finished reading through the file
            line = sourceFile.ReadLine
            If line.Contains("-") = True Then
                line = Trim(line)
                lineNume = line.Split(New String() {" - "}, StringSplitOptions.RemoveEmptyEntries)
                ListBox1.Items.Add(lineNume(0))
            ElseIf line.Contains("-") = False Then 'And String.Compare(line, lineanterior, StringComparison.InvariantCultureIgnoreCase) = 1
                Dim lineSplit() As String
                lineSplit = line.Split(vbTab)
                'TextBox1.Text = TextBox1.Text & NUMELE & "=>>" & lineSplit(1) & "=>>" & lineSplit(2) & vbNewLine
                lineNume(2) = lineNume(2).Replace(vbTab, "")
                '.Replace("Regularitate ", "")
                If lineSplit(2).Length = 7 Then
                    DataGridView1.Rows.Add(lineNume(0), lineNume(1), lineNume(2).Replace("Regularitate ", ""), lineSplit(1).Replace("Regularitate ", ""), "00:00:" & lineSplit(2))
                ElseIf lineSplit(2).Length = 8 Then
                    DataGridView1.Rows.Add(lineNume(0), lineNume(1), lineNume(2).Replace("Regularitate ", ""), lineSplit(1).Replace("Regularitate ", ""), "00:0" & lineSplit(2))
                ElseIf lineSplit(2).Length = 9 Then
                    DataGridView1.Rows.Add(lineNume(0), lineNume(1), lineNume(2).Replace("Regularitate ", ""), lineSplit(1), "00:" & lineSplit(2))
                End If
            End If
        End While
        sourceFile.Close()
        DataGridView1.ResumeLayout()
        ListBox1.ResumeLayout()
        Label1.Text = ("Concurenti = " & ListBox1.Items.Count & " Timpi = " & DataGridView1.Rows.Count & vbNewLine & "Total inregistrari fisier = " & ((ListBox1.Items.Count) + (DataGridView1.Rows.Count)))
        End With
    End Sub