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 Profile photo of Jose Perez Jose Perez 7 months, 4 weeks ago.

Viewing 10 posts - 1 through 10 (of 10 total)
  • Author
    Posts
  • #34588
    Profile photo of Jose Perez
    Jose Perez
    Participant

    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
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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
    Profile photo of Jose Perez
    Jose Perez
    Participant

    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
    Profile photo of Dan Potter
    Dan Potter
    Participant

    🙂

    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
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    @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
    Profile photo of Jose Perez
    Jose Perez
    Participant

    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
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    how are you outputing it?

    #34691
    Profile photo of Jose Perez
    Jose Perez
    Participant

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

    #34693
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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
    Profile photo of Jose Perez
    Jose Perez
    Participant

    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.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.