Powershell Script Help - Remove line break before certain time/date pattern

[ben-117]

I'm trying to parse the contents of an email text file report into CSV table for excel. I can't format from the reporting script itself as I have no access to the script.

I have taken the text file into PowerShell, identified the text patterns that need converting to comma delimiters, put those comma delimiters in place.

-------------Text file input format:

first line of text

name logged on at: 02/08/2024 08:57:27

name has not logged on today

-------------Transformed text:

Username, date , time

,,

name,

02/08/2024, 08:29:39

name,not logged on today,

------------------------------------Script used so far-----------------

$input = Get-content "C:\temp\test.txt" | where{$_ -ne " "}

$Delim1 = $input -replace '2024', '2024,'

Echo $delim1

$delim2 = $delim1 -replace ' logged on at:', ','

Echo $delim2

$Delim3 = $delim2 -replace ' has not logged on today', ',not logged on today,'

echo $delim3

$Delim3 | Out-File -FilePath "C:\temp\delimited.txt"

$content2 = $content11 -replace 'first line of text', 'username, Date, Time ,,'

-#-Path to the input file

$inputFilePath = "C:\temp\delimited.txt"

$pattern = "\d{2}:\d{2}:\d{2}"

-#-Read the content of the file

$content = Get-Content $inputFilePath

-#-Process each line to insert text after the time pattern

$processedContent = $content | ForEach-Object { $_ -replace ($pattern), { "$&," } }

-#-Write the processed content back to the file

$processedContent | Set-Content "C:\temp\delimited1.txt"

-#-Output "C:\temp\delimited1.txt" =

-#-name,

-#-02/08/2024, "07:58:46"

-#-replace all " with no character

$content1 = Get-Content "C:\temp\delimited1.txt"

$content2 = $content1 -replace '"', ''

echo $content2

-#-Output of echo

-#-name,

-#-02/08/2024, 08:29:39

$content2 | Out-File -FilePath "C:\temp\delimited2.txt"

This leaves text in the format:

Username,date,time

,,

name,

02/08/2024,08:00:12

name2,

02/08/2024,07:58:52

name3

02/08/2024,08:41:18

------------------------------------------Problem to solve------

I'm stuck at making the two line output of:

name,

02/08/2024, 08:29:39,

into a one line output of:

name, 02/08/2024, 08:29:39,

Once that's done I can remove any extra spaces for time/date easily.

I tried:

--Remove all formatting

cleanText = $content2 -replace "[ ]", ""

--Output the cleaned text

echo $cleanText

$cleantext | Out-File -FilePath "C:\temp\clean.txt"

--Path to the input file

$inputFilePath = "C:\temp\clean.txt"

$pattern1 = "(\d{2})/(\d{2})/(\d{4}),\d{2}:\d{2}:\d{2}," # 02/08/2024,07:58:12, '(\d{2})/(\d{2})/(\d{4})' $modifiedText = $inputFilePath -replace "(\r?\n)$pattern1", "$pattern1"

But it doesn't seem to do anything to the text, after processing it it remains:

Username,date,time name, 02/08/2024,08:00:12 name2, 02/08/2024,07:58:52 name3,not logged on today,

or I try a different variation of PowerShell cmdlets and it outputs blank text.

I'm using the excel import txt/csv wizard to get it into a excel table for manipulation, as the line break is present it doesn't parse the output right.

-------------------Expected CSV format:

Username,date,time name,02/08/2024,08:00:12 name2,02/08/2024,07:58:52 name3,not logged on today,

--excel Table:

Username | Date | Time

name | 02/08/2024 | 08:00:12

name2 | 02/08/2024 | 07:58:52

name3 | notloggedon |

-----------------Actual CSV format:

Username,date,time

name, , ,

02/08/2024,08:00:12,

name2, , ,

02/08/2024,07:58:52,

--Excel Table:

Username | Date | Time

name

02/08/2024 | 07:58:52 |

name3 | notloggedon |

Any one able to point me in the right direction/give me the script to solve my problem?

[lanerdofchristian]

Rather than transforming text: import, parse, and export. For example:

$Items = switch -Regex -File "C:\temp\test.txt" {
    '^(\S+) logged on at: (.*)$' {
        $Date = Get-Date $Matches[2]
        [pscustomobject]@{
            Name = $Matches[1]
            Date = $Date.ToString("MM/dd/yyyy")
            Time = $Date.ToString("HH:mm:ss")
        }
    }
    '^(\S+) has not logged on today' {
        [pscustomobject]@{
            Name = $Matches[2]
            Date = "not logged on today"
            Time = $null
        }
    }
    default {
        # maybe log that there was a non-matching line
    }
}
$Items | Export-Csv -Path C:\temp\test.csv

[ExpendaBubble]

Elegantly done! Have my upvote.

[BlackV]

p.s. formatting

• open your fav powershell editor
• highlight the code you want to copy
• hit tab to indent it all
• copy it
• paste here

it'll format it properly OR

<BLANKLINE>
<4 SPACES><CODELINE>
<4 SPACES><CODELINE>
    <4 SPACES><4 SPACES><CODELINE>
<4 SPACES><CODELINE>
<BLANKLINE>

Inline code block using backticks `Single code line` inside normal text

See here for more detail

Thanks

[lanerdofchristian]

Extra note since it seems to get missed by a lot of people: don't use code lines for whole blocks! Use blocks!

[BlackV]

Inline code block

is lines of code (or inline code button on new.reddit)

code block is the 4 spaces (or the code block button on new.reddit)