Creating Rows to Columns

PowerShell Help
1

I am not an expert in powershell but would like to get a little knowledge over this.

If someone can help, it will be really helpful.

I want to export the file in a csv format

eg : performance matrix - file generated : it’s bash file which is generated in every 10 minutes and exports these values in text file.

AB1 : 10
AC2 :24
AD1 : 15
AE1 : 25
Streams : 500 30 40 10 15

AB1 : 20
AC2 : 50
AD1 : 60
AE1 : 90
Streams : 100 40 50 9 10

.
.
.

I want to export this in csv file in a proper format like the below

AB1 AC2 AD1 AE1

10 24 15 25

20 50 60 90

 

Am I suppose to use the regex for this ?

Regards and Thanks

2

I believe ConvertFrom-String could help here, but I can’t find a proper template file to use. So I have another weird way here.

$File = Get-Content -Path <filpath.log>
    $custom = [PSCustOmobject]@{}
    $File  -split "`n" | ForEach-Object -Process {
    $tt = $_ -split ' : ' -notmatch 'stream'
    $t | Add-Member -Membertype NoteProperty $tt[0] -Value $tt[1]
}
$t
3

I’m pretty sure there are more sophisticated ways but I couldn’t find any yet … assumed the structure of our input file is not more complex than what you showed above …

Get-Content -Path C:\sample\source.txt -OutVariable FileContent

$FileContent | Select-String -Pattern ‘(AD1)\s*:\s*(\d+)’ -Context 2 | Select-Object -ExpandProperty Context |

ForEach-Object {

[PSCustomObject]@{

AB1 = $($.PreContext[0] -match ‘(\w+)\s*:\s*(\d+)’ | Out-Null ; $Matches[2])

AC2 = $($.PreContext[1] -match ‘(\w+)\s*:\s*(\d+)’ | Out-Null ; $Matches[2])

AD1 = $($.Line -match ‘(\w+)\s*:\s*(\d+)’ | Out-Null ; $Matches[2])

AE1 = $($.PostContext[0] -match ‘(\w+)\s*:\s*(\d+)’ | Out-Null ; $Matches[2])

}

}

4

Another one

$File = Get-Content -Path c:\file.log
$Custom =[pscustomobject]@{}
$File -split "`n" | ForEach-Object -Process { $_ -match '(\w{2}\d) : (\d{2})' ; $Custom | Add-Member -MemberType NoteProperty $matches[1] -value $matches[2]}
$custom
5

Thank you for responding.

but I am getting an error while using this code :

$File = Get-Content -Path
$custom = [PSCustOmobject]@{}
$File -split “`n” | ForEach-Object -Process {
$tt = $_ -split ’ : ’ -notmatch ‘stream’
$t | Add-Member -Membertype NoteProperty $tt[0] -Value $tt[1]
}
$t

 

Add-Member : Cannot bind argument to parameter ‘InputObject’ because it is null.
At line:5 char:10

  • $t | Add-Member -Membertype NoteProperty $tt[0] -Value $tt[1]
  • CategoryInfo : InvalidData: (:slight_smile: [Add-Member], ParameterBindingValidationException
  • FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddMemberCommand
6

Did you try one of the other suggestions? And please, wehn you post code format it as code. Thanks

7

Sure Olaf, I will try to paste the code in the write format, not much sure how to use the code format.
Anyways. I will try to show the sample file how it looks :

timestamp : 201812171505
streams : Total : 579 461 0 0 24 80 0 5 9
ABC : 630
A-1 : 98
B-2 : 87
C-3 : 0
D-4 : 0
E-5 : 238
timestamp : 201812171840

stream : Total : 476 372 0 0 20 74 0 10 0
ABC : 511
A-1 : 80
B-2 : 77
C-3 : 0
D-4 : 0
E-5 : 186

#The output I am looking for is somewhat like this

timestamp ABC A-1 B-2 C-3 D-4 E-5
201812171505 630 98 87 0 0 238
201812171840 511 80 77 0 0 186
I have tried couple of things , but it shows error everytime, not sure how to create the regex for this.

Tried using convertFrom-StringData, but no luck.

 

8

Simply use the “pre” tags as described in the very first and pinned post of the list of this forum: Read Me Before Posting! You’ll be Glad You Did!
You can use the “buttons” provided on the edit bar of the post editor when you create a post.

Now your sample data look different to what you initially posted. If they look different every time it will be hard to find a structure or an algorithm to parse them. Did you try my suggestion with the sample data you posted earlier?

If you need more information about how to create regular expressions you might take look at https://www.regular-expressions.info.

9

I did tried out, but it didn’t worked out the way I expected.

10

That’s not that helpful. :wink: We cannot see your screen. We do not know what you’re doing. So either you will have to tell us or you have figure out by yourself.

Do your performance matrix log files look always the same? Do they have a certain structure with repeating patterns? Can you post a part of the real data here? (if yes - please format as code as well please. Use the “pre” button on the bar right above the post editor for)

11 
$template = @'
{[string]Name*:Phoebe Cat}, {[string]phone:425-123-6789}, {[int]age:6}, {[int]Address:2155}
{[string]Name*:Lucky }, {[string]phone:(206) 987-4321}, {[int]age:12}
'@

$testText = @'
Phoebe Cat, 425-123-6789, 6, 2155
Lucky , (206) 987-4321, 12
Elephant Wise, 425-888-7766, 87
Wild Shrimp, (111) 222-3333, 1 , 2160
'@

$testText |
ConvertFrom-String -TemplateContent $template -OutVariable PersonalData | Out-Null

$PersonalData

The output is not correct :

Name          phone            age              address
Phoebe cat    425-123-6789       6                2155
lucky         (206) 987-4321     12                  12
Elephant Wise  425-888-7766       87                87
Wild Shrimp      (111) 222-3333     1              2160


Not sure , how to use this.
12

Is this reply somehow related to the initial question you asked?

Do you ever answer questions?

13

Try this,

$t=@{}
Get-Content -Path c:\file.log | Select-String 'stream' -NotMatch |
ForEach-Object -Process {$tt = $_ -split ':' ; $t.Add($tt[0],$tt[1])}
[PSCustomObject]$t

[PSCustomObject]$t | Format-Table
14

@olaf,

I just tried to make it simple with the example.

I tried using the cmdlet - convertfrom-string, but the desired output is not right.

Not sure if I am pretty close, yet I am trying , the problem is if there is a null value, still it adds the value of other items.

Desired output I am looking for is :

Name phone age address
Phoebe cat 425-123-6789 6 2155
lucky (206) 987-4321 12 Null
Elephant Wise 425-888-7766 87 Null
Wild Shrimp (111) 222-3333 1 2160

15

Hi Kvprasoon,

seems like the txt file I have received from my tutor is not in a structured format, there are some instances where some columns have no values, and that is the reason I think, the script is throwing an exception.

 

 

16

BIJO DEV,
I doubt that convertfrom-string will help you in your case. You’d need a kind of structured data for that. The example data are almost structured. They just miss some commas. If you change it to this:

$testText = @’

Phoebe Cat, 425-123-6789, 6, 2155

Lucky , (206) 987-4321, 12,

Elephant Wise, 425-888-7766, 87,

Wild Shrimp, (111) 222-3333, 1 , 2160

'@
you’ll get the expected results.
Therefor I asked for the data you have to deal with. :wink: If they have a certain structure you might be able to parse them. If not you would probably be out of luck.

17

It works even if there are no values for the columns, but not if there are few columns itself is not available. Handling unstructured data is no so easy.

This is how I tested

$r = @'
timestamp :     201812171505
streams   : Total    :  579   461   0   0   24     80     0      5 9
ABC : 630
A-1 : 98
B-2 :
C-3 : 0
D-4 : 0
E-5 : 238
'@

$r = $r -split "`n"
$t=@{}
$r | Select-String 'stream' -NotMatch |
ForEach-Object -Process {$tt = $_ -split ':' ; $t.Add($tt[0],$tt[1])}
[PSCustomObject]$t

[PSCustomObject]$t | Format-Table

This works fine if the input is from a file as well.

18

Hi Kvprasoon,

can I use the get-content cmdlet and call the log file in the same script.

 

19

@BIJO DEV,

you are asking beginner questions. I’d recommend for you to make a little step back and take your time to learn the very basics of Powershell. That would even enable you to understand the help you get here or in StackOverflow. It’ beyond the scope of any forum to teach you to write Powershell scripts or to iteratively develop ready to use productive code for you.

20

Hi Olaf,

Even I thought the same after asking that. It been almost after two years I am touching PowerShell.

Anyways did some modifications and it is working fine.

Will share the script once the desired output is ready.

 

Thank you everyone for your tips and help.

Really appreciated.

  • <!--more-->