How to parse csv file, look for trigger and split into new files with powershell How to parse csv file, look for trigger and split into new files with powershell powershell powershell

How to parse csv file, look for trigger and split into new files with powershell


The Import-CSV cmdlet will work here, if you don't already know about it. I would use that, as it returns all the rows as different objects in an array, with the properties being the column values. And you don't have to manually remove the quotes and such. Assuming the second column is a date time value, and should be unique for each group of 4 consecutive rows, then this will work:

$src = "C:\temp\ORD001.txt"$dstDir = "C:\temp\files\"Remove-Item -Path "$dstDir\*"$csv = Import-CSV $src -Delimiter ';'$DateTimeGroups = $csv | Group-Object -Property 'ColumnTwoHeader'foreach ($group in $DateTimeGroups) {    $filename = $group.Group.'ColumnFiveHeader' | select -Unique    $group.Group | Export-CSV "$dstDir\$filename.txt" -Append -NoTypeInformation}

However, this will break if two of those "groups of 4 consecutive rows" have the same value for the second column and the fifth column. There isn't a way to fix this unless you are certain that there will always be 4 consecutive rows in each time group. In which case:

$src = "C:\temp\ORD001.txt"$dstDir = "C:\temp\files\"Remove-Item -Path "$dstDir\*"$csv = Import-CSV $src -Delimiter ';'if ($csv.count % 4 -ne 0) {    Write-Error "CSV does not have a proper number of rows. Attempting to continue will be bad :)"    return}for ($i = 0 ; $i -lt $csv.Count ; $i=$i+4) {    $group = $csv[$i..($i+4)]    $group | Export-Csv "$dstDir\$($group[3].'ColumnFiveHeader').txt" -Append -NoTypeInformation}

Just be sure to replace Column2Header and Column5Header with the appropriate values.


If performance is not a concern, combining Import-Csv / Export-Csv with Group-Object allows the most concise, direct expression of your intent, using PowerShell's ability to convert CSV to objects and back:

$src =    "C:\temp\ORD001.txt"  # Input CSV file$dstDir = "C:\temp\files"       # Output directory# Delete previous output files, if necessary.Remove-Item -Path "$dstDir\*" -WhatIf# Import the source CSV into custom objects with properties named for the columns.# Note: The assumption is that your CSV header line defines columns  "Col1", "Col2", ...Import-Csv $src -Delimiter ';' |   # Group the resulting objects by column 2  Group-Object -Property Col2 |     ForEach-Object {  # Process each resulting group.      # Determine the output filename via the group's last row's column 5 value.      $outFile = '{0}\{1}.txt' -f $dstDir, $_.Group[-1].Col5      # Append the group at hand to the target file.      $_.Group | Export-Csv -Append -Encoding Ascii $outFile -Delimiter ';' -NoTypeInformation    }

Note:

  • The assumption - in line with your sample data - is that it is always the last row in a group of lines sharing the same column-2 value whose column 5 contains the root of the output filename (e.g., 140000001)


Sorry but I don't have a Header Column. It's a semikolon seperated txt file for an interface