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

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

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                                 

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: (:slight_smile: [], RuntimeException
    • FullyQualifiedErrorId : nullToObjectInvalidCast

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

:slight_smile:

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()}}}

@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.

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

how are you outputing it?

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

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","","","","","","","","","",""

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.