save .csv rows in different files

Question

Warning: PowerShell-newb at work.

I got a .csv-file that I am trying to filter and split in different parts. The data looks s.th. like this (much more columns in real)

column1; column2; column3; column4; column5; column6; column7
DATA;012594;50;400;1;123456789;87986531;
DATA;012594;50;401;1;456321564;53464554;
DATA;012594;50;402;1;321567894;54634858;
DATA;012594;51;400;1;312354684;38768449;
DATA;012594;51;410;1;123153167;54648648;

I now need do 2 steps:

1st: Filter the data for rows that have only column4 = '400'. Also picking just some columns as not all are of interest to me. And adding some custom columns at this point aswell.

2nd: Split and save the rows in 2 different files depending whether column3 is '50' or '51'.

$files = Get-ChildItem .\test\*.csv
foreach ($file in $files) {
    $fname = $file.Name
    $data = (Get-Content -path $file) | Select-Object -skip 1 | Foreach-Object {
        $_ -replace '\|',';'
    } | Set-Content -Path ".\test-out\${fname}"

    foreach ($rec in $data){
        $status = $rec.Substring(16,3)
        if ($status -eq "400"){
            Write-Warning "400 found"
            $csv400q = [PSCustomObject]@{
                'column 1' = $rec.'column 1'
                'column 2' = $rec.'column 2'
                'column 3' = $rec.'column 3'
                'column 4' = $rec.'column 4'
                'column 5' = $rec.'column 5'
                'column 6' = $rec.'column 6'
                'column 7' = $rec.'column 7'
                'new column1' = 'static text'
                'new column2' = 'static text'
                'new column3' = 'static text'
            }
            $csv400o += $csv400q
        }
    }
    $csv400o | Export-Csv -Path ".\test-out\${fname}" -Delimiter ";" -NoTypeInformation

    #Step #2 should be here
    foreach ($rec in $data) {
        $lunk = $rec.Substring(13,2)
        if ($lunk -like "50") {
            
        } elseif ($lunk -like "51") {
            
        }
    }
}

THe files are much larger than this example. But for this sample data the desired outcome would be

file50.csv

column1; column2; column3; column4; column5; column6; column7
DATA;012594;50;400;1;123456789;87986531;

file51.csv

column1; column2; column3; column4; column5; column6; column7
DATA;012594;51;400;1;312354684;38768449;

I just used some parts of previously used code. If that's the completly wrong direction - don't hesitate to say so.

Thank you in advance!

I'm just curious. Is there a special reason why you're not using `Import-Csv` to read your CSV file? — Olaf, May 20 '21 at 22:36
$Data would be empty as it’s been written to file instead. Plus CSV cmdlets would definitely be preferred. — Doug Maurer, May 21 '21 at 00:54
Yes, a very simple one: lack of knowledge/experience in Powershell. Thanks for the hints/pushes in the right direction. — Nerevar.de, May 21 '21 at 17:47

score 2 · Accepted Answer · answered May 21 '21 at 04:13

Your code sample handles multiple input files. You seem to understand the file I/O but as the commenters have already noted, you are not making use of the built in CSV and Object processing within PowerShell.

First we'll read your sample data from a here-string for simplicity and to make the code below easy to test

$YourData = @'
column1; column2; column3; column4; column5; column6; column7
DATA;012594;50;400;1;123456789;87986531;
DATA;012594;50;401;1;456321564;53464554;
DATA;012594;50;402;1;321567894;54634858;
DATA;012594;51;400;1;312354684;38768449;
DATA;012594;51;410;1;123153167;54648648;
'@
# Treat the here-string as CSV data
$YourCSV = $YourData | ConvertFrom-Csv -Delimiter ";"

Your question referred to two steps. Use Where-Object for filtering.
Use Select-Object to pick out the columns you want.
Use a backtick ` at the end of a line for line continuation

# 1st: Filter the data for rows that have only column4 = '400'. 
# Also picking just some columns as not all are of interest to me. 
# And adding some custom columns at this point as well. 
$Step1 = $YourCSV | Where-Object column4 -EQ '400' | Select-Object column1,column3,column4, `
   @{Name="Col10";Expression={"your text"}},@{Name="Col11";Expression={"other text"}}

# Above line with shortened syntax.  Same result
$Step1 = $YourCSV | ? column4 -EQ '400' | Select column1,column3,column4,@{N="Col10";E={"your text"}},@{N="Col11";E={"other text"}}

Now we can pipe the output of the $Step1 variable to CSV. Filter it first using Where-Object again. Since you gave two specific cases (50 and 51) that is what is demonstrated below.

#2nd: Split and save the rows in 2 different files depending whether column3 is '50' or '51'.
$Step1 | Where-Object column3 -EQ '50' | Export-Csv -Delimiter ";" -Path file50.csv -NoTypeInformation
$Step1 | Where-Object column3 -EQ '51' | Export-Csv -Delimiter ";" -Path file51.csv -NoTypeInformation

Note that the output data will be wrapped in quotes like this.

"column1";"column3";"column4";"Col10";"Col11"
"DATA";"50";"400";"your text";"other text"

If this is problematic, there are several methods to remove the quotes.

Perfect! Had to tweak a lot to fit my script ofc but that's exactly what I was looking for. Thank you very much! — Nerevar.de, May 21 '21 at 17:46

save .csv rows in different files

1 Answers1