Split column in CSV

PowerShell Help
1

Hi All
Im trying to spit a column in a csv file.
The column named “l” i need to split the state eg NSW, VIC, QLD, out to a column named “St”

physicalDeliveryOfficeName	employeeid	givenName	sn	mail	sAMAccountName	Accountstart	accountExpires	streetAddress	l	postalCode	mobile	telephoneNumber
221	2458467	Adam	blogs	Exampleemail1@gmail.com	2458467	11/10/2017		1 sample street	SAWTELL NSW	2452	411111111	398798798
222	2375467	ROBERT	white	Exampleemail2@gmail.com	2375467	11/09/2017		2 sample street	BALLINA NSW	2478	411111111	398798798
218	2434800	KELLIE	smith	Exampleemail3@gmail.com	2434800	11/02/2017		3 sample street	KYOGLE NSW	2474	411111111	398798798
238	2448698	REBECCA	jones	Exampleemail1@gmail.com	2448698	11/10/2017		4 sample street	COOPERNOOK NSW	2426	411111111	398798798
218	2336947	TRAVIC	demo	Exampleemail2@gmail.com	2336947	11/02/2017		5 sample street	KYOGLE NSW	2474	411111111	398798798
218	2434799	ALYSIA	blogs	Exampleemail3@gmail.com	2434799	11/02/2017		6 sample street	KYOGLE NSW	2474	411111111	398798798
231	2434994	NATHAN	white	Exampleemail1@gmail.com	2434994	11/15/2017		7 sample street	TOWNSEND NSW	2463	411111111	398798798
212	2444776	THOMAS	smith	Exampleemail2@gmail.com	2444776	11/09/2017		8 sample street	GREEN POINT NSW	2251	411111111	398798798
247	2444755	KAITLIN	jones	Exampleemail3@gmail.com	2444755	11/15/2017		9 sample street	CARINGBAH NSW	2229	411111111	398798798
233	2448141	GYPSY	demo	Exampleemail1@gmail.com	2448141	11/15/2017		10 sample street	EVANS HEAD NSW	2473	411111111	398798798
296	2301836	HANNAH	blogs	Exampleemail2@gmail.com	2301836	10/26/2017		11 sample street	MERBEIN VIC	3505	411111111	398798798
218	2352520	LILLIAN	white	Exampleemail3@gmail.com	2352520	11/02/2017		12 sample street	KYOGLE NSW	2474	411111111	398798798
245	2401769	THOMAS	smith	Exampleemail1@gmail.com	2401769	11/15/2017		13 sample street	SINGLETON HEIGHTS NSW	2330	411111111	398798798 </pre

Any help would be great. Thanks
2

What have you tried so far? Show your code please and we can try to suggest …

3

to be honest im not even sure where to start with it. Sorry a im a bit of a newbie to powershell

4

Hmmm … actually you’re not that new … I’ve seen some question from you already … :wink: :smiley:
OK, so I assume you know how to import a csv file, right? Then you can use a “calculated property” to create what you need from what you have … like this:

Import-Csv C:\Files\Work\test\csv.csv -Delimiter “`t” -OutVariable CSV |

Select-Object -Property ,@{Name=“State”;Expression={$_.l -match '.\s(.*)$'|Out-Null;$Matches[1] }} -OutVariable NewCSV

Now you have your origninal data in the variable $CSV and the “pimped” data in the variable $NewCSV.