Powershell Searching within a text file and exporting horizontally to CSV

Question

I have a big file which content somme blocks of text data like this.

[SERVER1]
LIBELLE=DATA, SOMME DATA
VARIABLES=A,B,C,D,E
PHYSICAL NAME=E:\SOMME\PATH\FILE.INI
ARTICLE SIZE=50
MACHINE=SOME SERVER 
PARAMETER =
OPTION = 

[SERVER2]
LIBELLE=DATA2, SOMME DATA2
VARIABLES=A,B,C,D,E
PHYSICAL NAME=Z:\SOMME\PATH\FILE2.INI
ARTICLE SIZE=150
MACHINE=SOME SERVER XY
PARAMETER =
OPTION 1 = VOID 
OPTION 2 = 
OPTION 3 = 
OPTION 4 = 
OPTION 5 =

What i would like to do is retrieving every block [SERVERX] and put it into a CSV file in this format (horizontally)

ColumnA__    |  ColumnB (LIBELLE)___  |  ColumnC (VARIABLES) | ColumnD ect...

[SERVER1]  |  DATA1, SOMME DATA1 |  A,B,C,D,E___________           | Ect...

[SERVER2]  |  DATA2, SOMME DATA2 |  A,B,C,D,E___________           | Ect...

I've tried this, the output work as i want but it need to be automated and exported to scv, which doesn't work for me.

$mydata = Get-Content my_file.txt
write-Host $mydata[0] $mydata[1] $mydata[2] $mydata[3] $mydata[4] $mydata[5] | Export-Csv -Path $rep\results.csv -Force -UseCulture -NoTypeInformation

Tried also somthing with select-string but i dont know if this is the right way to do my job..

select-String -path $my_file -Pattern '\[*\]', 'IDENTIFIANTS=','LIBELLE=','VARIABLES=' | Select-Object -Property LineNumber, Line | Export-Csv -Path $rep\results.csv -Force -UseCulture -NoTypeInformation

Thanks for your advices.

Export-CSV requires more complex objects beyond lines to functions. You need to make custom object with this data. Also to account that the data sets are not the same. Options is different in both. Are you keeping all the data for the expot? Are you set one those column names? Can LIBELLE just be LIBELLE? Programmatically this makes more sense. — Matt
– Matt, Commented Mar 16, 2016 at 13:38
Your source data looks mostly, but not completely, to have the same columns for each [OBJECT]. Do you need new columns created dynamically if you encounter a key-value pair with a key that hasn't been seen before? — MisterSeajay
– MisterSeajay, Commented Mar 16, 2016 at 16:36
@Matt : Are you keeping all the data for the expot? --> Yes Are you set one those column names? Can LIBELLE just be LIBELLE? --> Yes the columns are the same, i may have more new columns depending on data block but i'm still keeping the existing ones. — RabzorGT
– RabzorGT, Commented Mar 16, 2016 at 19:35

TheMadTechnician · Accepted Answer · 2016-03-16 20:39:23Z

1

So, I would read the whole file in as a multi-line string using the -Raw switch for Get-Content. Then split the file up based on the [ character to denote records. The get the properties from the ConvertFrom-StringData cmdlet (have to prepend "SERVER=" to each record), and make an object from it. Then we find out what all properties any given record can have, make sure to add them all to the first record if it doesn't have it (this is done because when you export to CSV it bases the columns off of the first entries' property list). Then you can export a CSV.

$Data = (Get-Content my_file.txt -Raw) -split "(\[[^[]+)" | ?{![string]::IsNullOrWhiteSpace($_)}
$Records = $Data -replace '\\','\\'|%{$Record="SERVER="+$_.trim()|ConvertFrom-StringData;New-Object PSObject -Prop $Record}
$Props = $Records|%{$_.psobject.properties.name}|select -Unique
$Props | Where{$_ -notin $Records[0].PSObject.Properties.Name}|%{Add-Member -InputObject $Records[0] -NotepropertyName $_ -NotepropertyValue $Null}
$Records|Export-CSV .\my_file.csv -notype

Edit: For those of you out there running PowerShell 2.0 (3 versions out of date at this point in time), you can't use the -Raw parameter. Here's the alternative:

$Data = (Get-Content my_file.txt) -Join "`r`n" -split "(\[[^[]+)" | ?{![string]::IsNullOrWhiteSpace($_)}

Alternative: Thanks @Matt for the suggestion, it is always good to have a different point of view on these things. As Matt suggested, you can use Out-String to combine the array of strings that Get-Content generates, and end up with a single multi-line string. Here's the usage!

$Data = (GC my_file.txt | Out-String) -split "(\[[^[]+)" | ?{![string]::IsNullOrWhiteSpace($_)}

edited Mar 16, 2016 at 20:39

answered Mar 16, 2016 at 19:01

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matt Over a year ago

This is what I was coming back to answer with once the OP answered my questions. This is the right approach for structured data.

RabzorGT Over a year ago

I've just tried your proposition, but it doesn't work, the "-Raw" parameter is not reconized because i'm on Windows 7 pro with Powershell version 2.0. Sorry i'm pretty new to PS.

Matt Over a year ago

I always found | Out-String to be easier to handle for the 2.0ers

RabzorGT Over a year ago

Thanks guys, but i'm still having a problem, "-notin" seems to be unsuported also.. After a little search i may have to use "-notcontains" but i dont know how to adapt it in your script.

TheMadTechnician Over a year ago

Ah, you are using an old version of PowerShell... right. So -notcontains is like a backwards -notin... If($x -notin $y) is the same as If($y -notcontains $x)

|

Collectives™ on Stack Overflow

Powershell Searching within a text file and exporting horizontally to CSV

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related