Split column in CSV

This topic contains 3 replies, has 2 voices, and was last updated by  Olaf Soyk 3 weeks, 5 days ago.

  • Author
    Posts
  • #84673

    Lethal
    Participant

    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 
  • #84683

    Olaf Soyk
    Participant

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

    • #84689

      Lethal
      Participant

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

  • #84692

    Olaf Soyk
    Participant

    Hmmm … actually you're not that new … I've seen some question from you already … 😉 😀
    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.

You must be logged in to reply to this topic.