Merge 3 text file in single txt file

PowerShell Help
1

I have 3 txt files with sample Content as below:

File 1:

Report Generated Time: 01-02-2021 21:02:10

Total Number of Laptops Reporting Count:10000
Laptops Authorized Count:10001
Laptop(s) Authorization Pending Count:10002
Laptop(s) Duplicate Pending Deactivation:10003
Laptop(s) Newly added since last 24 Hours:10004
Laptop(s) NOT Communicated Since last 45 Days:10005

File 2:

Report Generated Time: 02-02-2021 21:03:14

Total Number of Laptops Reporting Count:20000
Laptops Authorized Count:20001
Laptop(s) Authorization Pending Count:20002
Laptop(s) Duplicate Pending Deactivation:20003
Laptop(s) Newly added since last 24 Hours:20004
Laptop(s) NOT Communicated Since last 45 Days:20005

File 3:

Report Generated Time: 03-02-2021 21:02:34

Total Number of Laptops Reporting Count:30000
Laptops Authorized Count:30001
Laptop(s) Authorization Pending Count:30002
Laptop(s) Duplicate Pending Deactivation:30003
Laptop(s) Newly added since last 24 Hours:30004
Laptop(s) NOT Communicated Since last 45 Days:30005

Objective is to merge above 3 files & create a master txt file with format as below. Can someone please advise.

Description 01-02-2021 02-02-2021 03-02-2021
Total Number of Laptops Reporting Count 10000 20000 30000
Laptops Authorized Count 10001 20001 30001
Laptop(s) Authorization Pending Count 10002 20002 30002
Laptop(s) Duplicate Pending Deactivation 10003 20003 30003
Laptop(s) Newly added since last 24 Hours 10004 20004 30004
Laptop(s) NOT Communicated Since last 45 Days 10005 20005 30005
2

What have you tried so far? Please show your code.

3

Thank you for response. Following is the last code I tried:

Code:

clear-host
$file1 = import-csv “C:\Scripts\ComplianceStat\Laptop-01-02-2021.txt” -Delimiter “:”
$file2 = import-csv “C:\Scripts\ComplianceStat\Laptop-02-02-2021.txt” -Delimiter “:”
$file3 = import-csv “C:\Scripts\ComplianceStat\Laptop-03-02-2021.txt” -Delimiter “:”

$date1 = “01-02-2021”
$date2 = “02-02-2021”
$date3 = “03-02-2021”

$i = 0
while($i -lt $file1.category.count) {
##$val = [ordered]@{}
$val = new-object PSObject -Property @{

Description = $file1.category[$i]
$date1 = $file1.Counter[$i]
$date2 = $file2.Counter[$i]
$date3 = $file3.Counter[$i]
} | select Description, $date1, $date2, $Date3 | out-file ‘C:\Scripts\ComplianceStat\masterout.txt’ -Append
$i++
}

 

However the output I get is as below. The header / Title (Description, and 3 Dates) is getting repeated during every loop, which is not matching to objective I mentioned in the initial description.

Any assistance is appreciated.

 

Output in Text File:

Description 01-02-2021 02-02-2021 03-02-2021

Total Number of Laptops Reporting Count 10000 20000 30000

Description 01-02-2021 02-02-2021 03-02-2021

Laptops Authorized Count 10001 20001 30001

Description 01-02-2021 02-02-2021 03-02-2021

Laptop(s) Authorization Pending Count 10002 20002 30002

Description 01-02-2021 02-02-2021 03-02-2021

Laptop(s) Duplicate Pending Deactivation 10003 20003 30003

Description 01-02-2021 02-02-2021 03-02-2021

Laptop(s) Newly added since last 24 Hours 10004 20004 30004

Description 01-02-2021 02-02-2021 03-02-2021

Laptop(s) NOT Communicated Since last 45 Days 10005 20005 30005

4

Missed to mention, while I write that code, I modified input files (File1, File2, File3) to have header as Category & Counter as below. So that when I import it, I can use properties like $File1.Category / $file1.Counter.

 

File 1:

Category:Counter

Total Number of Laptops Reporting Count:10000
Laptops Authorized Count:10001
Laptop(s) Authorization Pending Count:10002
Laptop(s) Duplicate Pending Deactivation:10003
Laptop(s) Newly added since last 24 Hours:10004
Laptop(s) NOT Communicated Since last 45 Days:10005

5

Import-Csv is expecting a Header row, which in this case is a ‘column’. The data in the text file is more of a hash, key:value. Try doing a bit more parsing and we can generate a PSObject:

$files = Get-ChildItem -Path 'C:\Scripts\Blah'

$results = foreach ($file in $files) {
    $props = @{}
    $content = Get-Content -Path $file.FullName
    
    foreach($line in $content | Where-Object {$_}) {
        $arr = $line -split ':'
        $props.Add($arr[0],$arr[1].Trim())
    }

    [pscustomobject]$props
}
$results

Output:

PS C:\> $files


    Directory: C:\Scripts\Blah

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a---            2/5/2021 10:34 AM            318 file1.txt
-a---            2/5/2021 10:34 AM            318 file2.txt
-a---            2/5/2021 10:35 AM            318 file3.txt

PS C:\> C:\Scripts\test.ps1

Laptop(s) NOT Communicated Since last 45 Days : 10005        
Laptop(s) Newly added since last 24 Hours     : 10004        
Laptops Authorized Count                      : 10001        
Laptop(s) Authorization Pending Count         : 10002        
Total Number of Laptops Reporting Count       : 10000        
Report Generated Time                         : 01-02-2021 21
Laptop(s) Duplicate Pending Deactivation      : 10003        

Laptop(s) NOT Communicated Since last 45 Days : 20005        
Laptop(s) Newly added since last 24 Hours     : 20004        
Laptops Authorized Count                      : 20001        
Laptop(s) Authorization Pending Count         : 20002        
Total Number of Laptops Reporting Count       : 20000        
Report Generated Time                         : 02-02-2021 21
Laptop(s) Duplicate Pending Deactivation      : 20003        

Laptop(s) NOT Communicated Since last 45 Days : 30005
Laptop(s) Newly added since last 24 Hours     : 30004
Laptops Authorized Count                      : 30001
Laptop(s) Authorization Pending Count         : 30002
Total Number of Laptops Reporting Count       : 30000
Report Generated Time                         : 03-02-2021 21
Laptop(s) Duplicate Pending Deactivation      : 30003
6

[quote quote=290635]Import-Csv is expecting a Header row, which in this case is a ‘column’. The data in the text file is more of a hash, key:value. Try doing a bit more parsing and we can generate a PSObject:
[/quote]
In this case I’d rather use Convertfrom-String. It could be a little more robust as parsing strings for colons could be misleading when you’re dealing with time and date strings as well …

Get-ChildItem -Path D:\sample -Filter *.txt |
    ForEach-Object {
        Get-Content -Path $_.FullName |
            Where-Object {-not [string]::IsNullOrEmpty($_)} |
                ConvertFrom-StringData -Delimiter ':'
    }

I just don’t think that’s the output yet the OP is after. :wink:

8

What’s with all the single period posts?..

You should mention that the -Delimiter parameter of ConvertFrom-StringData is not available in Windows Powershell 5.1. It was added in Powershell Core

9

Since a few weeks I get a lot of errors when I submit a reply and this answers will not be shown as new in the forum list. And that’s my way to deal with that because there no one from the admins or moderators responding to my post in the website feedback forum.

You’re totally right. I used version 7.1.1 for this. :wink:

10

I don’t know if that helps in any way but I think you could create actual structured data from your files. I took the files like you posted them in your very first question.

$result = 
Get-ChildItem -Path C:\Scripts\ComplianceStat -Filter *.txt | 
ForEach-Object {
    $object = [PSCustomObject]@{}
    Get-Content -Path $_.FullName |
    Where-Object { -not [string]::IsNullOrEmpty($_) } |
    ConvertFrom-StringData -Delimiter ':' |
    ForEach-Object {
        $object | Add-Member -MemberType NoteProperty -Name $($_.Keys) -Value $($_.Values)
    }
    $object
}
$result | Format-Table

The ouput looks a little bit weird because of the length of property names.

Ah … and you need to use Powershell core for this as mentioned before. I used Powershell version 7.1.1.

11

…

12

Something is really broken in this forum!! It’s really anoying. :-/