CODEWITHSUNDEEP
csvpowershell
Johnathan
Johnathan

Reputation: 909

Splitting one line of a Csv into multiple lines in PowerShell

I have a Csv which looks something like this:

No,BundleNo,Grossweight,Pieces,Tareweight,Netweight
1,Q4021317/09,193700,1614,646,193054
2,Q4021386/07,206400,1720,688,205712

What I first need to do is do some maths with the Netweight column to get two values, $x and $y. Then I need to split each line in the Csv, into $x lines of data, where each line of data will look like "AL,$y"

In the below example, I get the values of $x and $y for each row successfully. The trouble comes when trying to then split each row into $x rows...:

$fileContent = Import-Csv $File

$x = ( $fileContent | ForEach-Object { ( [System.Math]::Round( $_.Netweight /25000 ) ) } )
$y = ( $fileContent | ForEach-Object { $_.Netweight / ( [System.Math]::Round( $_.Netweight /25000 ) ) } )

$NumRows = ( $fileContent | ForEach-Object { (1..$x) } )
$rows = ( $NumRows | ForEach-Object { "Al,$y" } )

This code works as I would hope when there is one row of data in the Csv. I.e. if $x = 8 and $y = 20, it would return 8 rows of data which look like "AL,20". However, I get an error message when there is more than row in the Csv:

Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Int32".

I hope I've explained that OK and any help is appreciated. Thanks, John

Upvotes: 3

Views: 1251

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174900

Instead of using ForEach-Object over and over again, just iterate over the csv once and generate your $x and $y results one at a time:

$fileContent = @'
No,BundleNo,Grossweight,Pieces,Tareweight,Netweight
1,Q4021317/09,193700,1614,646,193054
2,Q4021386/07,206400,1720,688,205712
'@ | ConvertFrom-Csv

foreach($line in $fileContent){
    $x = [System.Math]::Round( $line.Netweight / 25000 )
    if($x -ne 0){
        $y = $line.Netweight / $x
        1..$x |ForEach-Object {"AL,$y"}
    }
}

Resulting in $x number of "AL,$y" strings per row:

AL,24131.75
AL,24131.75
AL,24131.75
AL,24131.75
AL,24131.75
AL,24131.75
AL,24131.75
AL,24131.75
AL,25714
AL,25714
AL,25714
AL,25714
AL,25714
AL,25714
AL,25714
AL,25714

Upvotes: 3

Related Questions