Member Avatar for Needhelp2

Hi all,

I have a large txt file (3 Million lines). Ouside excel capacity.
Thanks for your support...

input file format:

..
...
...
2014 Jul 23 08:15:16.675
...
..

Name = x
Lastname = y
.
.

Age = 5
height = 1
..

..
...
...
2014 Jul 24 08:15:16.675
...
..

Name = HHH
Lastname = BBSB
.
.

Age = 10
height = 2
..

2014 Jul 25 08:15:16.675
...
..

Name = SKSK
Lastname = SKSK
.
.

Age = 9
height = 3
..

..
...
...
2014 Jul 26 08:15:16.675
...
..

Name = x
Lastname = y
.
.

Age = 8
height = 1.5

=============================================================

Wanted output format :

Date/time| Name |Last name|Age|Height
2014 Jul 23 08:15:16.675|x|Y|5|1
.
.
.
.

  • 2 Contributors
  • 3 Replies
  • 256 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by e.pol

Dani AI

Generated

posted a repeating block layout where each record starts with a date line like 2014 Jul 23 08:15:16.675 followed by key = value lines. For a 3‑million‑line file, Excel or loading the whole file into memory is not viable. As hinted, a line‑by‑line (streaming) reader is the correct pattern: detect the date lines, accumulate the following key/value pairs into a tiny in‑memory map, then write one pipe‑separated output row when the next date appears.

Algorithm (concise)

  • Open input as a streamed reader and open output as a streamed writer.
  • Write a header (Date/time|Name|Last name|Age|Height).
  • For each input line: if it matches a date regex, flush the previous record to output; otherwise, if it contains =, split on the first = and store the normalized key (remove spaces, lower case) => value.
  • At EOF flush the final record.
  • Keep only one record in memory at a time so RAM stays small.

VB.NET (memory‑efficient example)

Imports System.IO
Imports System.Text.RegularExpressions

Sub ConvertLargeFile(inputPath As String, outputPath As String)
    Dim dateRegex As New Regex("^\s*\d{4}\s+[A-Za-z]{3,9}\s+\d{1,2}\s+\d{2}:\d{2}:\d{2}\.\d{1,3}\s*$", RegexOptions.Compiled)
    Using sr As New StreamReader(inputPath)
        Using sw As New StreamWriter(outputPath, False)
            sw.WriteLine("Date/time|Name|Last name|Age|Height")
            Dim currentDate As String = Nothing
            Dim fields As New Dictionary(Of String, String)(StringComparer.OrdinalIgnoreCase)
            While Not sr.EndOfStream
                Dim line As String = sr.ReadLine()
                If String.IsNullOrWhiteSpace(line) Then Continue While
                If dateRegex.IsMatch(line) Then
                    If currentDate IsNot Nothing Then
                        sw.WriteLine(currentDate & "|" & GetField(fields, "name") & "|" & GetField(fields, "lastname") & "|" & GetField(fields, "age") & "|" & GetField(fields, "height"))
                        fields.Clear()
                    End If
                    currentDate = line.Trim()
                Else
                    Dim idx As Integer = line.IndexOf("="c)
                    If idx >= 0 Then
                        Dim key As String = line.Substring(0, idx).Trim().Replace(" ", "").ToLowerInvariant()
                        fields(key) = line.Substring(idx + 1).Trim()
                    End If
                End If
            End While
            If currentDate IsNot Nothing Then
                sw.WriteLine(currentDate & "|" & GetField(fields, "name") & "|" & GetField(fields, "lastname") & "|" & GetField(fields, "age") & "|" & GetField(fields, "height"))
            End If
        End Using
    End Using
End Sub

Private Function GetField(dict As Dictionary(Of String, String), key As String) As String
    Dim k = key.Replace(" ", "").ToLowerInvariant()
    Dim v As String = Nothing
    If dict.TryGetValue(k, v) Then Return v
    Return String.Empty
End Function

Classic VB / VBA approach

  • Same logic applies. Use Line Input # or FileSystemObject.OpenTextFile and a VBScript.RegExp to spot the date line, collect keys, and Print out a pipe row when a new date is found. The same streaming idea avoids large memory use.

Notes and cautions

  • Test the date regex on a few real sample lines first; adjust if month text or spacing changes.
  • Confirm file encoding (UTF‑8 vs ANSI) when opening StreamReader/TextStream.
  • If the output must be opened in Excel, remember Excel limits (split output if it exceeds Excel row limits).
  • Running time will be proportional to file size; streaming keeps RAM low but allow the job to run until completion.
Member Avatar for Needhelp2

the file is too large to be open in excel.. :-(

Member Avatar for e.pol

Are you want to open text file with VB ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.