I appreciate any help to split a column date into multiples columns

Welcome Forums General PowerShell Q&A I appreciate any help to split a column date into multiples columns

This topic contains 9 replies, has 3 voices, and was last updated by

 
Participant
2 years, 8 months ago.

  • Author
    Posts
  • #34588

    Participant
    Points: 0
    Rank: Member

    I have a file with hundred of records like this:
    datacenter type Finaldate
    center1 s 1/1/2015
    center1 m 1/1/2015
    center1 l 1/1/2015
    center2 s 1/1/2015
    center2 m 1/1/2015
    center2 l 1/1/2015
    center3 s 1/1/2015
    center3 m 1/1/2015
    center3 l 1/1/2015
    center3 s 2/1/2015
    center3 m 2/1/2015
    center3 l 2/1/2015
    center1 s 2/1/2015
    center1 m 2/1/2015
    center1 l 2/1/2015

    I need tot split and group each month and create a new column with the respective month.

    datacenter type January February
    center1 s 1/1/2015 2/1/2015
    center1 m 1/1/2015 2/1/2015
    center1 l 1/1/2015 2/1/2015
    center2 s 1/1/2015
    center2 m 1/1/2015
    center2 l 1/1/2015
    Center3 s 1/1/2015 2/1/2015
    Center3 m 1/1/2015 2/1/2015
    Center3 l 1/1/2015 2/1/2015

    any help is really appreciated.
    Thanks

  • #34590

    Participant
    Points: 0
    Rank: Member

    Something like this?

    $DataCenters = @{}
    
    Get-Content C:\Temp\input.txt |
    ForEach-Object {
        $parts = $_.split("`t")
        $record = [pscustomobject]@{
            datacenter = $parts[0]
            type = $parts[1]
            date = $parts[2]
        }
        If (-NOT $DataCenters["$($record.datacenter)-$($record.type)"]) {
            $DataCenters["$($record.datacenter)-$($record.type)"] = [pscustomobject]@{
                DataCenter = $record.datacenter
                Type = $record.type
                January = ""
                February = ""
                March = ""
                April = ""
                May = ""
                June = ""
                July = ""
                August = ""
                September = ""
                October = ""
                November = ""
                December = ""
            }
        }
        Switch (([datetime]$record.date).Month) {
            1 {$DataCenters["$($record.datacenter)-$($record.type)"].January = $record.date}
            2 {$DataCenters["$($record.datacenter)-$($record.type)"].February = $record.date}
            3 {$DataCenters["$($record.datacenter)-$($record.type)"].March = $record.date}
            4 {$DataCenters["$($record.datacenter)-$($record.type)"].April = $record.date}
            5 {$DataCenters["$($record.datacenter)-$($record.type)"].May = $record.date}
            6 {$DataCenters["$($record.datacenter)-$($record.type)"].June = $record.date}
            7 {$DataCenters["$($record.datacenter)-$($record.type)"].July = $record.date}
            8 {$DataCenters["$($record.datacenter)-$($record.type)"].August = $record.date}
            9 {$DataCenters["$($record.datacenter)-$($record.type)"].September = $record.date}
            10 {$DataCenters["$($record.datacenter)-$($record.type)"].October = $record.date}
            11 {$DataCenters["$($record.datacenter)-$($record.type)"].November = $record.date}
            12 {$DataCenters["$($record.datacenter)-$($record.type)"].December = $record.date}
        }
    }
    $DataCenters.Values | Sort-Object DataCenter, Type
    

    Results:

    DataCenter Type January  February March April May June July August
    ---------- ---- -------  -------- ----- ----- --- ---- ---- ------
    center1    l    1/1/2015 2/1/2015                                 
    center1    m    1/1/2015 2/1/2015                                 
    center1    s    1/1/2015 2/1/2015                                 
    center2    l    1/1/2015                                          
    center2    m    1/1/2015                                          
    center2    s    1/1/2015                                          
    center3    l    1/1/2015 2/1/2015                                 
    center3    m    1/1/2015 2/1/2015                                 
    center3    s    1/1/2015 2/1/2015                                 
    
  • #34596

    Participant
    Points: 0
    Rank: Member

    Hi Curtis ,Thank you for your replay.
    The script is giving me an error:
    Cannot convert null to type "System.DateTime".
    At line:11 char:52
    + If (-NOT $DataCenters["$($record.datacenter)-$($record.type)"]) {
    + ~~~~~~~~~~~~
    + CategoryInfo : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : nullToObjectInvalidCast

    Thanks so much for taking the time to help me with this request.
    Regards,
    Jose

  • #34637

    Participant
    Points: 0
    Rank: Member

    🙂

    gc .\input.txt | % {$_ | select @{l = 'datacenter';e={$_.split(' ')[0]}},@{l = 'randomletter';e={$_.split(' ')[1]}},@{l = 'Jan';e={$_.split(' ')[2]}},@{l = 'Feb';e={([datetime]$_.split(' ')[2]).addmonths(1).toshortdatestring()}}}
  • #34682

    Participant
    Points: 0
    Rank: Member

    @jose-perez: that would indicate that you either have a or multiple blank lines in your input file, or your input file does not match your sample text.

  • #34688

    Participant
    Points: 0
    Rank: Member

    Hi Curtis Smith, I ran a trim to remove any space,etc and the script is running fine ,but the output is saved on only one column.(datacenter)
    DataCenter Type January February March April May June July August
    center2,m,11/1/2015
    center1,l,7/1/2015
    center2,l,2/1/2015
    center2,m,12/1/2015
    center1,s,5/1/2015
    center1,m,10/1/2015
    center1,m,2/1/2015

  • #34690

    Participant
    Points: 0
    Rank: Member

    how are you outputing it?

  • #34691

    Participant
    Points: 0
    Rank: Member

    $DataCenters.Values | Export-Csv C:\temp\output.csv -NoTypeInformation

  • #34693

    Participant
    Points: 0
    Rank: Member

    Not sure, the sample data I have exports perfectly.

    Sample Data:

    center1	s	1/1/2015
    center1	m	1/1/2015
    center1	l	1/1/2015
    center2	s	1/1/2015
    center2	m	1/1/2015
    center2	l	1/1/2015
    center3	s	1/1/2015
    center3	m	1/1/2015
    center3	l	1/1/2015
    center3	s	2/1/2015
    center3	m	2/1/2015
    center3	l	2/1/2015
    center1	s	2/1/2015
    center1	m	2/1/2015
    center1	l	2/1/2015
    

    Code:

    $DataCenters = @{}
    
    Get-Content C:\Temp\input.txt |
    ForEach-Object {
        $parts = $_.split("`t")
        $record = [pscustomobject]@{
            datacenter = $parts[0]
            type = $parts[1]
            date = $parts[2]
        }
        If (-NOT $DataCenters["$($record.datacenter)-$($record.type)"]) {
            $DataCenters["$($record.datacenter)-$($record.type)"] = [pscustomobject]@{
                DataCenter = $record.datacenter
                Type = $record.type
                January = ""
                February = ""
                March = ""
                April = ""
                May = ""
                June = ""
                July = ""
                August = ""
                September = ""
                October = ""
                November = ""
                December = ""
            }
        }
        Switch (([datetime]$record.date).Month) {
            1 {$DataCenters["$($record.datacenter)-$($record.type)"].January = $record.date}
            2 {$DataCenters["$($record.datacenter)-$($record.type)"].February = $record.date}
            3 {$DataCenters["$($record.datacenter)-$($record.type)"].March = $record.date}
            4 {$DataCenters["$($record.datacenter)-$($record.type)"].April = $record.date}
            5 {$DataCenters["$($record.datacenter)-$($record.type)"].May = $record.date}
            6 {$DataCenters["$($record.datacenter)-$($record.type)"].June = $record.date}
            7 {$DataCenters["$($record.datacenter)-$($record.type)"].July = $record.date}
            8 {$DataCenters["$($record.datacenter)-$($record.type)"].August = $record.date}
            9 {$DataCenters["$($record.datacenter)-$($record.type)"].September = $record.date}
            10 {$DataCenters["$($record.datacenter)-$($record.type)"].October = $record.date}
            11 {$DataCenters["$($record.datacenter)-$($record.type)"].November = $record.date}
            12 {$DataCenters["$($record.datacenter)-$($record.type)"].December = $record.date}
        }
    }
    $DataCenters.Values | Export-Csv c:\temp\export.csv -NoTypeInformation
    

    Results:

    "DataCenter","Type","January","February","March","April","May","June","July","August","September","October","November","December"
    "center2","l","1/1/2015","","","","","","","","","","",""
    "center3","m","1/1/2015","2/1/2015","","","","","","","","","",""
    "center1","l","1/1/2015","2/1/2015","","","","","","","","","",""
    "center3","l","1/1/2015","2/1/2015","","","","","","","","","",""
    "center1","s","1/1/2015","2/1/2015","","","","","","","","","",""
    "center2","s","1/1/2015","","","","","","","","","","",""
    "center2","m","1/1/2015","","","","","","","","","","",""
    "center3","s","1/1/2015","2/1/2015","","","","","","","","","",""
    "center1","m","1/1/2015","2/1/2015","","","","","","","","","",""
    
  • #34694

    Participant
    Points: 0
    Rank: Member

    Thank you so much for your help Curtis Smith, I ran the script with a new txt file and is working fine , but if I use any csv file then the script doesn't work .
    Thanks.
    Jose.

The topic ‘I appreciate any help to split a column date into multiples columns’ is closed to new replies.