Dissecting text from CSV using PowerShell

Question

I have scoured the web for the following solution to my problem... I currently have text in a csv format that I need to break apart into several columns as shown below

Task ID,Task Name,Project                   
2,2 - CR04AW212 Warehouse,2 Crown Castle International : 001586 2013 ALU Phase 3 Sprint Crown : 001623 CR04AW212 ALU Sprint                 
10,10 - CR04AW090 Tower Punch,24 Alcatel Lucent : 001713 2013 ALU AAV : 001752 CR04AW090 ALU AAV

And I need to translate\breakdown into several columns pulling apart text as shown below

Task ID,Task Name,Customer ID,Customer Name,Parent Project ID,Parent Project Name,Child Project ID,Child Project Name
2,CR04AW212 Warehouse,2,Crown Castle International,001586,2013 ALU Phase 3 Sprint Crown 001623,CR04AW212 ALU Sprint
10,CR04AW090 Tower Punch,24,Alcatel Lucent,001713,2013 ALU AAV,001752,CR04AW090 ALU AAV

The Task ID number is found repeated again in the Task Name. I would like to strip that number from name as shown in the second image

The Project column essentially contains all the data needed to break into 6 columns. I believe there has to be a way to programmatically select the text. I will go into it one bit at a time. The "Customer ID" column should contain the number before the first space in the "Project column" This could consist of a single digit or double digit number. Irregardless, that number should always exist before the first empty space in "Project". Being the obvious novice i was able to accomplish it with the -split parameter

@{Name="Customer ID";Expression={($_.Project -split ' ')[0]}}.

Now I must select the following text "Customer Name" that will always exists after the first space and before the ":".

This example persists 2 more times. The "Parent Project ID" and "Child Project ID" are fixed 6 digit numbers. The text following each ID is represented as the name of that project.

And here is where my google searches have been exhausted

I apologize for not uploading images, I just registered and StackOverflow won't allow me to until i am reputable enough :)

EDIT: How do I quit after the first match?

Add-Member -PassThru NoteProperty 'Task Name' -Value ($_.'Task Name' -replace '\d+ - ')

At the Moment if I had

1,1 - JA03MR008 - Civil,9 New Tech Construction Corp. : 001583 2013 Panhandle : 001810 JA03MR008"

it results in stripping all matches when I would like to remove the first match in "Task name"

Task ID: 1
Task Name : JA03MRCivil
Customer ID : 9
Customer Name : New Tech Construction Corp.
Parent Project ID : 001583
Parent Project Name : 2013 Panhandle
Child Project ID : 001810
Child Project Name : JA03MR008

I took a crash course in REGEX for this and this is what I believe worked best for my purpose. See the code below. It seems to work great. Is there a caveat to using it this way? Thanks in advance

Add-Member -PassThru NoteProperty 'Task Name' -Value ($_.'Task Name' -replace '^([^-]*) - ')

BartekB · Accepted Answer · 2013-03-07 17:31:58Z

1

There are probably many ways to skin this cat, I would suggest using regex to process Project property of input object:

@"
Task ID,Task Name,Project
2,2 - CR04AW212 Warehouse,2 Crown Castle International : 001586 2013 ALU Phase 3 Sprint Crown : 001623 CR04AW212 ALU Sprint
10,10 - CR04AW090 Tower Punch,24 Alcatel Lucent : 001713 2013 ALU AAV : 001752 CR04AW090 ALU AAV
"@ | ConvertFrom-Csv | ForEach-Object {
    if ($_.Project -match '(\d+) ([^:]+) : (\d+) ([^:]+) (: (\d+) (.*))?') {
        $_ | select 'Task ID' |
            Add-Member -PassThru NoteProperty 'Task Name' -Value ($_.'Task Name' -replace '\d+ - ') |
            Add-Member -PassThru NoteProperty 'Customer ID' -Value $Matches[1]                      |
            Add-Member -PassThru NoteProperty 'Customer Name' -Value $Matches[2]                    |
            Add-Member -PassThru NoteProperty 'Parent Project ID' -Value $Matches[3]                |
            Add-Member -PassThru NoteProperty 'Parent Project Name' -Value $Matches[4]              |
            Add-Member -PassThru NoteProperty 'Child Project ID' -Value $Matches[6]                 |
            Add-Member -PassThru NoteProperty 'Child Project Name' -Value $Matches[7]

    }
}

This would probably require some polishing here and there. E.g. you could use select-object + hashtables instead of Add-Member. It would be sooo much easier in v3, but I assume this has to be v2 compatible, thus no v3 code used.

EDIT: Updated the code with code that is able to handle option when there is no child project, new regex:

 '(\d+) ([^:]+) : (\d+) ([^:]+) (: (\d+) (.*))?'

it's making last two pieces: : (\d+) and (.*) optional: (pattern)? $Matches[5] will be either blank, or will contain optional group, so you need to change indexes for child project properties to 6 and 7.

edited Mar 7, 2013 at 17:31

answered Mar 4, 2013 at 22:20

BartekB

8,71034 silver badges33 bronze badges

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

7 Comments

Simon Catlin Over a year ago

BartekB - nice answer. What are the V3 features you speak of? Haven't looked at Server2012/Win8 yet.

BartekB Over a year ago

[PSCustomObject]@{} that gives nice objects with preserved order of properties, Add-Member -NotePropertyMembers ([ordered]@{}) that would simplify this code and give same benefit of keeping properties in correct order...

Domingo Ugarte Over a year ago

This is excellent BartekB. Now I just need to apply it to 5,600 rows in Sample.csv. I will post back when I can figure out how to do it. Be right back :)

Domingo Ugarte Over a year ago

BartekB... I took a closer look into the results displayed by your code. I was wondering how I can have blank values for "Child Project ID" and "Child Project Name" in the event I have a line such as

Domingo Ugarte Over a year ago

@BartekB... I took a closer look into the results displayed by your code. I was wondering how I can have blank values for "Child Project ID" and "Child Project Name" in the event I have a line such as 2,2 - CR04AW212 Warehouse,2 Crown Castle International : 001586 2013 ALU Phase 3 Sprint Crown In this row for example I don't have a second semicolon and ideally I would need for result output to be blank as shown below 2,CR04AW212 Warehouse,2,Crown Castle International,001586,2013 ALU Phase 3 Sprint Crown,,

|

Collectives™ on Stack Overflow

Dissecting text from CSV using PowerShell

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related