I need to pull out two lines of text from multiple text files and put them into one CSV file.
=============================
Sample data:
File1.txt
User=asfd
UserID=12
Computer=12343
Name=Smith, Will
File2.txt
User=qwerty
UserID = 14
Computer=4567
Name=Smith, John
File3.txt
User=asfd
UserID=12
Computer=123434
Name=Smith, William
=============================
I want to pull out the two lines that are UserID & Name and put those into a CSV that is structured like this:
UserID,Name
12;Smith, William
12;Smith, Will
14; Smith, John
I am currently using:
Get-Content $TextFilesInDirectory | Select-String -Pattern "^Name=" | Out-File C:\TMP\Names.csv
but I can’t put this into the same file as
Get-Content $TextFilesInDirectory | Select-String -Pattern "^UserID=" | Out-File C:\TMP\Names.csv
Any suggestions?
It’s certainly not a one-liner, but it uses Regex and parses the files into their own PSObjects (which can then exported to CSV).
$allTxtFiles = Get-ChildItem -Path $TextFilesInDirectory
$allInfo = foreach ($file in $allTxtFiles) {
$raw = Get-Content -Path $file.FullName -Raw
$matchGrps = [regex]::Matches($raw, '^(.+)?\=(.+)$', "Multiline")
$props = @{}
foreach ($grp in $matchGrps) {
$name = $grp.Groups[1].Value.Trim()
$value = $grp.Groups[2].Value.Trim()
$props.Add($name, $value)
}
New-Object PSObject -Property $props
}
$allInfo | Export-Csv "C:\TMP\Names.csv" -NoTypeInformation
# The output looks like:
# User UserID Computer Name
# ---- ------ -------- ----
# asfd 12 12343 Smith, Will
# qwerty 14 4567 Smith, John
# asfd 12 123434 Smith, William
Not counting string splitting/manipulation, I see 3 possible solutions: using Select-String with regex like you are, ConvertFrom-String, and ConvertFrom-StringData.
Select-String
get-content $textfiles -raw | Select-String "(?s)UserID[?=\ ]*([\d]*).*Name[?=\ ](.*)" | foreach {
[PSCustomObject]@{
UserID = $_.matches.groups[1]
Name = $_.matches.groups[2]
}
} -OutVariable results | Export-CSV C:\TMP\Names.csv -NoType
Output
UserID Name
------ ----
12 Smith, Will
14 Smith, John
12 Smith, William
ConvertFrom-String
Now this one assumes you have more than one entry per file, otherwise why are you automating it? 
Need to give enough training data without going overboard. For this I have combined the 3 users into one file. Closer to real sample data may be better to train with.
@'
User=asfd
UserID=12
Computer=12343
Name=Smith, Will
User=qwerty
UserID = 14
Computer=4567
Name=Smith, John
User=asfd
UserID=12
Computer=123434
Name=Smith, William
'@ | Out-File c:\TMP\alltext.txt
$template = @'
User=asfd
UserID={User*:{UserID:12}
{!UserID:Computer=12343}
Name={[string]Name: Smith, Will}}
User = qwerty
UserID ={User*:{UserID:14}
{!UserID:Computer =4567}
Name ={[string]Name:Smith, John}}
'@
Get-Content c:\temp\alltext.txt -Raw |
ConvertFrom-String -TemplateContent $template |
select -ExpandProperty user | Export-Csv C:\TMP\Names.csv -NoTypeInformation
The contents of the csv
import-csv C:\TMP\Names.csv
UserID Name
------ ----
12 Smith, Will
14 Smith, John
12 Smith, William
I haven’t quite figured ConvertFrom-StringData out all the way. I say it seems like a good fit because the data is already in the “name=value” format. Maybe someone else can add an example using that.
ConvertFrom-StringData will convert a string into a hash table, but the key has to be unique. In the “alltext.txt” example that Doug gives, ConvertFrom-StringData will fail as the keys (ie User in User=qwery) has already been defined (User=asfd).
If they are all seperate files, something like this should work;
$file1 = @'
User=asfd
UserID=12
Computer=12343
Name=Smith, Will
'@
$File2 = @'
User=qwerty
UserID = 14
Computer=4567
Name=Smith, John
'@
$File3 = @'
User=asfd
UserID=12
Computer=123434
Name=Smith, William
'@
$files = $file1, $file2, $File3
$output = @()
foreach ($file in $files)
{
$info = $null
$info = ConvertFrom-StringData $file -Delimiter '='
$i = [pscustomobject]@{
UserID = $info.UserID
Name = $info.name
}
$output += $i
$i = $null
}
$output
Thank you all so much, you got me going in the right direction!
Good deal. Just to be thorough for myself and future readers, here is another way using plain ol’ string manipulation.
$TextFilesInDirectory = 'C:\some\path\file*.txt'
Get-ChildItem $TextFilesInDirectory | foreach{
$userid,$name = foreach($line in Get-Content $_){
'UserID','Name' | foreach{
if($line -match $_){$line.split('=').trim()[1]}
}
}
[PSCustomObject]@{
UserID = $userid
Name = $name
}
} | Export-Csv C:\TMP\Names.csv -NoTypeInformation
It just linked to this thread, so not sure which one you used. I also wanted to share my ConvertFrom-StringData solution.
$TextFilesInDirectory | foreach {
$ht = @{}
Get-Content $_ | ConvertFrom-StringData | foreach {$ht += $_}
[PSCustomObject]$ht
}
Output
User UserID Computer Name
---- ------ -------- ----
asfd 12 12343 Smith, Will
qwerty 14 4567 Smith, John
asfd 12 123434 Smith, William
To limit to just the 2 desired properties
$TextFilesInDirectory | foreach {
$ht = @{}
Get-Content $_ | ConvertFrom-StringData | foreach {$ht += $_}
[PSCustomObject]$ht
} | select UserID,Name
As desired
UserID Name
------ ----
12 Smith, Will
14 Smith, John
12 Smith, William
That worked fine because there is only one set per file. However it’s likely to have many per file. If the files hav each set separated by a blank line, you can use this approach.
(get-content $combinedfiles -raw) -split '(?m)^[^\S]' | foreach {
$ht = @{}
$_ | convertfrom-StringData | foreach{$ht += $_}
[PSCustomObject]$ht
}
This will split each group at the blank line and then process each line of each group the same way as before.