I have a csv that I want to have PowerShell extract 10 rows for each unique value in a column named "art". After the unique art values have been identified, I need sperate csv files for those values. The code I have written has not been working for me.
If (Test-Path D:\WORK\JetLetter\TPL\TPL_SNAP\data\TPL_Mailer.csv){
$source=Get-ChildItem "D:\WORK\JetLetter\TPL\TPL_SNAP\data\TPL_Mailer.csv" | select -Last 1
$csvFile = 'D:\WORK\JetLetter\TPL\TPL_SNAP\data\TPL_Mailer.csv'
$arttag = Import-Csv $csvFile | Group-Object | Select-Object "art" -Force
Export-csv $arttag
}
The error I get is:
Select-Object : A parameter cannot be found that matches parameter name 'Force'.
At line:5 char:62
+ $arttag = Import-Csv $csvFile | Group-Object | Select-Object -Force
+ ~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Select-Object], ParameterBindingException
+ FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.SelectObjec
tCommand
Export-Csv : Cannot validate argument on parameter 'Path'. The argument is null or empty.
Provide an argument that is not null or empty, and then try the command again.
At line:6 char:12
+ Export-csv $arttag
+ ~~~~~~~
+ CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationExcepti
on
+ FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.E
xportCsvCommand
The imported csv will have multiple rows with names, addresses, art tags, and sequence numbers.
sequence art last first address city st zip
1 3S Doe John 123 Any Street AnyTown CA 12345
11 OG Dow Jane 124 Any Street AnyTown CA 12346
21 OGF Cool Joe 125 Any Street AnyTown CA 12347
31 SLV Cool Carol 126 Any Street AnyTown CA 12348
41 SMP Bravo Johnny 127 Any Street AnyTown CA 12349
Whatever existing columns are in the data, I need a csv exported with 10 rows of each art value.
Any help would be greatly appreciated.
I modified the script to make it simpler, however I'm not seeing the result I need.
$source=Get-ChildItem 'D:\WORK\JetLetter\TPL\TPL_SNAP\data\TPL_Mailer.csv' | select -Last 1
$csvFile = 'D:\WORK\JetLetter\TPL\TPL_SNAP\data\TPL_Mailer.csv'
import-csv $csvFile | Select-Object -Property 'art' -Unique | export-csv 'D:\WORK\JetLetter\TPL\TPL_SNAP\data\art_proofs.csv' -NoTypeInformation
What I get is:
art
APS
DWS
I need every column with headers for each art row. What I am looking for is a way to filter by the art tag and export that filtered result. The imported csv can have 1000 to 400,000 records. For Example:
sequence art last first address city st zip
1 3S Doe John 123 Any Street AnyTown CA 12345
2 OG Dow Jane 124 Any Street AnyTown CA 12346
3 OGF Cool Joe 125 Any Street AnyTown CA 12347
4 SLV Cool Carol 126 Any Street AnyTown CA 12348
5 SMP Bravo Johnny 127 Any Street AnyTown CA 12349
6 3S Doe John 128 Any Street AnyTown CA 12350
7 OG Dow Jane 129 Any Street AnyTown CA 12351
8 OGF Cool Joe 130 Any Street AnyTown CA 12352
9 SLV Cool Carol 131 Any Street AnyTown CA 12353
10 SMP Bravo Johnny 132 Any Street AnyTown CA 12354
11 3S Doe John 133 Any Street AnyTown CA 12355
12 OG Dow Jane 134 Any Street AnyTown CA 12356
13 OGF Cool Joe 135 Any Street AnyTown CA 12357
14 SLV Cool Carol 136 Any Street AnyTown CA 12358
15 SMP Bravo Johnny 137 Any Street AnyTown CA 12359
16 3S Doe John 138 Any Street AnyTown CA 12360
17 OG Dow Jane 139 Any Street AnyTown CA 12361
18 OGF Cool Joe 140 Any Street AnyTown CA 12362
19 SLV Cool Carol 141 Any Street AnyTown CA 12363
20 SMP Bravo Johnny 142 Any Street AnyTown CA 12364
The output file should look like this:
sequence art last first address city st zip
1 3S Doe John 123 Any Street AnyTown CA 12345
6 3S Doe John 128 Any Street AnyTown CA 12350
2 OG Dow Jane 124 Any Street AnyTown CA 12346
7 OG Dow Jane 129 Any Street AnyTown CA 12351
3 OGF Cool Joe 125 Any Street AnyTown CA 12347
8 OGF Cool Joe 130 Any Street AnyTown CA 12352
4 SLV Cool Carol 126 Any Street AnyTown CA 12348
9 SLV Cool Carol 131 Any Street AnyTown CA 12353
5 SMP Bravo Johnny 127 Any Street AnyTown CA 12349
10 SMP Bravo Johnny 132 Any Street AnyTown CA 12354
As you can see the file has been filtered down to 2 rows per the value in the art column. First the import csv would need to be sorted by the art column and then filtered down to 2 rows per art value.
By looking at your output, seems like you're looking to group by the art column then pick the first 2 objects from each group and output them. For this you can use Group-Object combined with Select-Object.
For example, given the example in your question, with the following code:
Import-Csv path\to\csv.csv | Group-Object art |
ForEach-Object { $_.Group | Select-Object -First 2 } |
Export-Csv path\to\export.csv -NoTypeInformation
The output becomes:
sequence art last first address city st zip
-------- --- ---- ----- ------- ---- -- ---
1 3S Doe John 123 Any Street AnyTown
6 3S Doe John 128 Any Street AnyTown
2 OG Dow Jane 124 Any Street AnyTown
7 OG Dow Jane 129 Any Street AnyTown
3 OGF Cool Joe 125 Any Street AnyTown
8 OGF Cool Joe 130 Any Street AnyTown
4 SLV Cool Carol 126 Any Street AnyTown
9 SLV Cool Carol 131 Any Street AnyTown
5 SMP Bravo Johnny 127 Any Street AnyTown
10 SMP Bravo Johnny 132 Any Street AnyTown