Splitting on column into multiple columns from a CSV file in PowerShell

Question

I am new to using PowerShell and I am in need of some assistance.

I have a .csv file that looks like this:

DisplayName,AllJSSUSers,ALLMobileDevices,LimitToUsers,Exclusions,DepartmentEx,IconURL,ID 
Aurasma,TRUE,TRUE,"G_Year 4,G_Year 7,G_Year 11,G_Year 6,G_Year 10,G_Year 5,G_Year 9,G_Teaching Staff,G_Year 8,G_Supply Teachers,G_Year 3,G_Year 12",,,,5

What I would like to do is split the column LimitToUsers where the commas are into multiple column and then output that to a new csv file.

I have no idea where to start with this. Can anyone help?

Thank you

Gavin

Please paste the header line and a sample data row directly into your question, with correct formatting. (Screen shots are next to useless.) — Bill_Stewart
– Bill_Stewart, Commented Mar 7, 2018 at 16:01
Apologies I hope the below is of better use. DisplayName,AllJSSUSers,ALLMobileDevices,LimitToUsers,Exclusions,DepartmentEx,IconURL,ID Aurasma,TRUE,TRUE,"G_Year 4,G_Year 7,G_Year 11,G_Year 6,G_Year 10,G_Year 5,G_Year 9,G_Teaching Staff,G_Year 8,G_Supply Teachers,G_Year 3,G_Year 12",,,,5 — gavin
– gavin, Commented Mar 7, 2018 at 16:17
Please use proper formatting for the CSV file sample you pasted. (To format as code, indent each line 4 spaces.) — Bill_Stewart
– Bill_Stewart, Commented Mar 7, 2018 at 17:40
Start outlining your expected results. This will also show there are more questions behind your question, e.g.: how should the new column be named? — iRon
– iRon, Commented Mar 9, 2018 at 13:29

Joey · Accepted Answer · 2018-03-07 16:20:18Z

1

You can read CSV data with Import-Csv.
You can access that column from each data object by accessing the LimitToUsers property.
You can split a string with the -split operator.
You can add new properties to object with Add-Member.
You can write CSV with Export-Csv.

Since you somehow have to split a single column into multiple ones, how you do that is up to you and I can't help you there

answered Mar 7, 2018 at 16:20

Joey

357k88 gold badges704 silver badges699 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gavin Over a year ago

Thank you. As a complete novice I'm not sure how to write it. I assume I start here... Import-Csv "iPadScopedApps.csv" Select-Object -Property LimitToUsers how then will i write the split line. Sorry I know this is the basics but I'm only just getting into it.

Joey Over a year ago

Split your problem into tractable parts, figure out how to solve these parts and ask questions if you're stuck. Since you cannot even describe your approach to solve the problem, I'd suggest you start there. See for example Eric Lippert's great article on that subject: ericlippert.com/2014/03/21/find-a-simpler-problem.

gavin Over a year ago

I have tried doing some research on this but I have no idea how I get the LimitToUsers property to be usable by the split operator. This is basically the first time using powershell so its all a bit over my head.

Collectives™ on Stack Overflow

Splitting on column into multiple columns from a CSV file in PowerShell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related