CODEWITHSUNDEEP
filepowershellcsvsplit
Fab
Fab

Reputation: 133

How to split a CSV file depending of row values

Below is only an example, I have seen a lot of script to breakdown a .CSV file in smaller files but struggling with this.

How can we with PowerShell, find the header indicated by ALPH take each subsequent line, stop when it reaches ALPT (inclusive) and put this text into another file. The operation will need to run through the whole file and the number of ALPD or ALPC lines will vary.

ALPH can be considered as a header while the information contained is needed as some field value can be different. The only constant are ALPH and ALPT.

ALPH;8102014
ALPC;PK
ALPD;50
ALPD;40
ALPT;5
ALPH;15102014
ALPC;PK
ALPD;50
ALPD;50
ALPD;70
ALPD;70
ALPD;71
ALPD;72
ALPD;40
ALPT;6
ALPH;15102014
ALPC;PK
ALPD;50
ALPD;50
ALPD;40
ALPT;6

Upvotes: 2

Views: 209

Answers (2)

JPBlanc
JPBlanc

Reputation: 72680

Here is a way using switch. Your original file is in C:\temp\ALPH.CSV here is the way I imagine to find the begin an the end. 

$n = 1
switch -File 'C:\temp\ALPH.CSV' -Regex
{
  '^ALPH.*' {
    Write-Host "Begin $n"
  }
  '^ALPT.*' {
    Write-Host "End $n"
    $n++
  }
}

Now saving lines to a var and exporting files :

$n = 1
$csvTmp = @()
switch -File 'C:\temp\ALPH.CSV' -Regex
{
  '^ALPH.*' {
    Write-Host "Begin $n"
    $csvTmp += $_
  }
  '^ALPT.*' {
    Write-Host "End $n"
    $csvTmp += $_
    $csvTmp | Set-Content "c:\temp\file$n.csv"
    $csvTmp = @()
    $n++
  }
  default {
    $csvTmp += $_
  }
}

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200573

If I understood your question correctly, something like this should work:

$csv     = 'C:\path\to\your.csv'
$pattern = 'ALPH[\s\S]*?ALPT.*'
$cnt     = 0

[IO.File]::ReadAllText($csv) | Select-String $pattern -AllMatches |
  select -Expand Matches | select -Expand Groups | 
  % {
    $cnt++
    $outfile = Join-Path (Split-Path $csv -Parent) "split${cnt}.csv"
    [IO.File]::WriteAllText($outfile, $_.Value)
  }

Upvotes: 1

Related Questions