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

jose-perez · January 30, 2016, 11:18am

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

curtis-smith · January 30, 2016, 4:38pm

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

jose-perez · January 31, 2016, 6:02am

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

dan-potter · February 1, 2016, 2:14am

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

curtis-smith · February 1, 2016, 11:24am

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

jose-perez · February 1, 2016, 12:22pm

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

curtis-smith · February 1, 2016, 1:40pm

how are you outputing it?

jose-perez · February 1, 2016, 2:04pm

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

curtis-smith · February 1, 2016, 2:34pm

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

jose-perez · February 1, 2016, 3:43pm

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.

Topic		Replies	Views
Split date time into seperate variables PowerShell Help	2	124	June 23, 2015
add date as a column PowerShell Help	2	149	August 17, 2017
Please help me understand this teaching-powershell	1	310	June 6, 2023
General Coding Issue PowerShell Help	0	99	December 31, 2011
New-Object output problem PowerShell Help	11	162	October 24, 2016

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

Related Topics