CODEWITHSUNDEEP
powershellcsvsplit
Domingo Ugarte
Domingo Ugarte

Reputation: 21

Dissecting text from CSV using PowerShell

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 '^([^-]*) - ')

Upvotes: 1

Views: 330

Answers (1)

BartekB
BartekB

Reputation: 8650

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.

Upvotes: 1

Related Questions