Vista - Getting a combined string based upon similar reference values

Hi,

I'm new to Powershell and am trying to get the following to work.

I have a csv file with content similar to what follows:

RefNr;Status;Info
Nr-1;Active;Info A1
Nr-2;Active;Info A2
Nr-2;Active;Info B2
Nr-3;Active;Info A3
Nr-2;Active;Info C2
Nr-4;Active;Info A4
Nr-3;Active;Info B3
Nr-2;Active;Info D2

I'm trying to loop through these lines and get an output file with the
following results of a combined string of the Info values for similar RefNr.
values:

RefNr.;Status;Info
Nr-1;Active;Info A1
Nr-2;Active;Info A2 Info B2 Info C2 Info D2
Nr-3;Active;Info A3 Info B3
Nr-4;Active;Info A4

My many attempts have not resulted in the expected output.

I will be sincerely thankful for any suggestions and help.

TIA

Art

#change the delimeter to a comma
get-content sourcefile.csv | %{$_ -replace ";",","} | set-content
newsourcefile.csv

#import, group, export (assumes status will be same for a given refnr)
Import-CSV newsourcefile.csv |
Group-Object RefNr |
Select @{n="RefNr";e={$_.name}},
@{n="Status";e={$_.Group[0].Status}},
@{n="Info";e={$_.Group | %{"$($_.Info) "}}} |
export-csv file.csv -notype



"Art-De" wrote:

Thanks Paul! Thanks for the fast response and for the precise solution. At
last I'm able to generate the output file with the content as expected.

"PaulChavez" wrote:

Mmm data parsing!

Perhaps within the automation tool,
Windows PowerShell, let's do some
data parsing using .NET!

$null =
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.VisualBasic")

$fileToUse = "$pwd\atest1file.txt"

for ($runTimes = 1; $runTimes -lt 5; $runTimes++)
{
$myParser = new-object
Microsoft.VisualBasic.FileIO.TextFieldParser("$fileToUse")

$headers = $myParser.ReadLine()

$sepChars = "Nr-" + $runTimes + ";Active;"

$myParser.SetDelimiters("$sepChars")

$currentLine = $null

while ( $myParser.EndOfData -ne $true )
{
$fields = $myParser.ReadFields()

if( $fields.count -eq 2)
{
$currentLine += $fields[1] + " "
}

}

$myParser.Close()
$line += $sepChars + $currentLine + "`n"

}

" "
$headers
$line
" "
"Done!"
" "

Returns

RefNr;Status;Info
Nr-1;Active;Info A1
Nr-2;Active;Info A2 Info B2 Info C2 Info D2
Nr-3;Active;Info A3 Info B3
Nr-4;Active;Info A4

Done!

Mmm just like using Log Parser's COM
or .NET methods (of using each fields
data while obtaining it)!

Just another Windows automation tool
(PowerShell) data parsing usage way!

Thanks for this alternative approach to generate the expected csv file.

"Flowering Weeds" wrote:

Mmmm data parsing!

Perhaps automate Log Parser within
the automation tool Windows PowerShell.

$fileName = "atest1file.txt"

# Get the file header and field names.
$header = get-content $fileName -totalcount 1
$sepChar = ";"
$fields = $header.Split("$sepChar")
$nFields = $fields.count

$useThisField = $fields[1]

$secondFieldData = LogParser.exe "SELECT
DISTINCT $useThisField
FROM $filename " `
-i tsv -iSeparator "$sepChar" `
-nfields $nFields -q on

$useThisField = $fields[0]

$list = LogParser.exe "SELECT
DISTINCT $useThisField
FROM $fileName " `
-i tsv -iSeparator "$sepChar" `
-nFields $nFields -q on

$a,$b,$c,$d = $null

$list | foreach { $a =
LogParser.exe "SELECT Info
FROM $fileName
WHERE $useThisField LIKE '$_' " `
-i tsv -iSeparator "$sepChar" `
-nfields $nFields -q on ;
$a += " ";
$b += $a;
$c += "$_;" + $secondFieldData + ";$b`n";
$a = $null; $b = $null }

$d = "$header`n"
$d += $c
" "
$d

"Done!"
" "

Returns

RefNr;Status;Info
Nr-1;Active;Info A1
Nr-2;Active;Info A2 Info B2 Info C2 Info D2
Nr-3;Active;Info A3 Info B3
Nr-4;Active;Info A4

Done!

Have some data parsing fun,
use Log Parser within almost
any Windows process!