CODEWITHSUNDEEP
powershellcsvsplit
Johnathan
Johnathan

Reputation: 909

How to split a line into multiple lines?

I have a CSV file which looks something like this:

Column1,Column2,Column3
John,Smith,"AA, AH, CA, NI, PB"
Reginald,Higginsworth,"AA, AH, CA, NI, PB, SN, ZS"

You'll notice that there are multiple values within Column3 (enclosed in the quotes). What I need to do is produce a line of data in the CSV for each of these values in Column3, i.e. the file would need to look something like:

Column1,Column2,Column3
John,Smith,AA
John,Smith,AH
John,Smith,CA
John,Smith,NI
John,Smith,PB
Reginald,Higginsworth,AA
Reginald,Higginsworth,AH
Reginald,Higginsworth,CA
Reginald,Higginsworth,NI
Reginald,Higginsworth,PB
Reginald,Higginsworth,SN
Reginald,Higginsworth,ZS

I have had more than several attempts at this and can't seem to get it right.

Upvotes: 0

Views: 1880

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200283

Use Import-Csv to read the input file, then split the third column and create new output lines for each resulting element:

Import-Csv 'C:\path\to\input.csv' | ForEach-Object {
  foreach ($value in ($_.Column3 -split ', ')) {
    $_ | Select-Object -Exclude Column3 -Property *,@{n='Column3';e={$value}}
  }
} | Export-Csv 'C:\path\to\output.csv' -NoType

The Select-Object construct replaces the existing property Column3 with a new property Column3 that contains only a single value.

Upvotes: 5

Related Questions