Powershell Split 1 column into 2 columns-CodePudding

Im familar with the -split operator and how to split a string, but i cant figure out how to split a string in one column and turn it into 2 seperate columns

Data:

"MPN","Item_Description","Customer_Order"
"000501","BLU 60MM Slate Shale Grey (116.82 sqft/pallet - 10.62 sqft/layer)","12055"
"000501","BLU 60MM Slate Shale Grey (116.82 sqft/pallet - 10.62 sqft/layer)","12421"
"000501","BLU 60MM Slate Shale Grey (116.82 sqft/pallet - 10.62 sqft/layer)","12424"
"002243","Mini-Creta Architectural 6'' Champlain Grey (30 sqft/pallet - 6 sqft/layer)","12014"
"002243","Mini-Creta Architectural 6'' Champlain Grey (30 sqft/pallet - 6 sqft/layer)","12425"
"002243","Mini-Creta Architectural 6'' Champlain Grey (30 sqft/pallet - 6 sqft/layer)","12817"

I am trying to figure out how to split or copy the data in between '(' and 'sqft/pallet' in the "Item_Description" column to its own column.

my biggest obstacle is splitting it and then adding it back in as a new column with a new header to look like this:

"MPN","Item_Description","Sqft_Pallet","Customer_Order"
"000501","BLU 60MM Slate Shale Grey (116.82 sqft/pallet - 10.62 sqft/layer)","116.82","12055"
"000501","BLU 60MM Slate Shale Grey (116.82 sqft/pallet - 10.62 sqft/layer)","116.82","12421"
"000501","BLU 60MM Slate Shale Grey (116.82 sqft/pallet - 10.62 sqft/layer)","116.82","12424"
"002243","Mini-Creta Architectural 6'' Champlain Grey (30 sqft/pallet - 6 sqft/layer)","30","12014"
"002243","Mini-Creta Architectural 6'' Champlain Grey (30 sqft/pallet - 6 sqft/layer)","30","12425"
"002243","Mini-Creta Architectural 6'' Champlain Grey (30 sqft/pallet - 6 sqft/layer)","30","12817"

CodePudding user response：

You could use calculated properties with Select-Object. This example assumes Sqft_Pallet is always fully contained in parentheses:

$CsvData | select MPN,
  Item_Description,
  @{l='Sqft_Pallet';e={
    # Regex is to select anything inside parentheses from Item_Description
    if ($_.Item_Description -match '\(.*\)') {$Matches.Values} 
    else {'N/A'}  ## if no full parentheses present, use N/A
  }},
  Customer_Order

Outputs like so:

MPN    Item_Description                                                           Sqft_Pallet                             Customer_Order
---    ----------------                                                           -----------                             --------------
000501 BLU 60MM Slate Shale Grey (116.82 sqft/pallet - 10.62 sqft/layer)          (116.82 sqft/pallet - 10.62 sqft/layer) 12055         
000501 BLU 60MM Slate Shale Grey (116.82 sqft/pallet - 10.62 sqft/layer)          (116.82 sqft/pallet - 10.62 sqft/layer) 12421         
000501 BLU 60MM Slate Shale Grey (116.82 sqft/pallet - 10.62 sqft/layer)          (116.82 sqft/pallet - 10.62 sqft/layer) 12424         
002243 Mini-Creta Architectural 6' Champlain Grey (30 sqft/pallet - 6 sqft/layer) (30 sqft/pallet - 6 sqft/layer)         12014         
002243 Mini-Creta Architectural 6' Champlain Grey (30 sqft/pallet - 6 sqft/layer) (30 sqft/pallet - 6 sqft/layer)         12425         
002243 Mini-Creta Architectural 6' Champlain Grey (30 sqft/pallet - 6 sqft/layer) (30 sqft/pallet - 6 sqft/layer)         12817         
002243 Mini-Creta Architectural 6' Champlain Grey 30 sqft/pal                     N/A                                     12817