Powershell: Using import-csv to select rows by column value and export to csv with a number of rows-CodePudding

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.

CodePudding user response：

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