Cannot convert null to type "System.DateTime".

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 4 years and 4 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Cannot convert null to type "System.DateTime".

Post by ITEngineer »

Folks,

I need some assistance in how to convert one column in a .CSV file date_time into the yyyy-MM-dd HH:mm:ss tt format ,so I can sort it on the Excel.

this is the script:

Code: Select all

Import-Csv -Path C:\Logs\input.csv |
		ForEach-Object {$_.date_time = ([DateTime]$_.date_time).ToString('yyyy-MM-dd HH:mm:ss tt'); $_} |
	Export-Csv -NoTypeInformation -Path C:\Logs\sample_out.csv
however, I got the error repeated thousands of time:
Cannot convert null to type "System.DateTime".
At line:2 char:19
+ ... ach-Object {$_.date_time = ([DateTime]$_.date_time).ToString('yyyy-MM ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [], RuntimeException
+ FullyQualifiedErrorId : nullToObjectInvalidCast
The sample file is attached.

Any help is appreciated muchly.

Thanks,
Attachments
Input.csv
Input file
(3.7 KiB) Downloaded 172 times
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Cannot convert null to type "System.DateTime".

Post by jvierra »

Just do this:

if($_.date_time){[DateTime]$_.date_time}else{$null}
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Cannot convert null to type "System.DateTime".

Post by jvierra »

I ran your code and it works correctly. Your file has no null values.
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Cannot convert null to type "System.DateTime".

Post by ITEngineer »

jvierra wrote: Wed Nov 20, 2019 3:37 pm Just do this:

if($_.date_time){[DateTime]$_.date_time}else{$null}
Strangely it does not work?

Code: Select all

Import-Csv -Path C:\Logs\input.csv |
        if($_.date_time){[DateTime]$_.date_time}else{$null}
		ForEach-Object {$_.date_time = ([DateTime]$_.date_time).ToString('yyyy-MM-dd HH:mm:ss tt'); $_} |
	Export-Csv -NoTypeInformation -Path C:\Logs\sample_out.csv
Error code:
if : The term 'if' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:2 char:9
+ if($_.date_time){[DateTime]$_.date_time}else{$null}
+ ~~
+ CategoryInfo : ObjectNotFound: (if:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

Cannot convert null to type "System.DateTime".
At line:3 char:19
+ ... ach-Object {$_.date_time = ([DateTime]$_.date_time).ToString('yyyy-MM ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [], RuntimeException
+ FullyQualifiedErrorId : nullToObjectInvalidCast

Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null.
At line:4 char:2
+ Export-Csv -NoTypeInformation -Path C:\Logs\sample_out.csv
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCommand
/* IT Engineer */
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Cannot convert null to type "System.DateTime".

Post by ITEngineer »

jvierra wrote: Wed Nov 20, 2019 3:41 pm I ran your code and it works correctly. Your file has no null values.
No, it has no null values, The date_time column has values as it was exported from Office 365.
However, When I run the Powershell script directly when the files have been downloaded, it gives me the error?

And this is the result in the .CSV result output:
Image
Last edited by ITEngineer on Wed Nov 20, 2019 4:00 pm, edited 1 time in total.
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Cannot convert null to type "System.DateTime".

Post by jvierra »

I get no errors and cannot guess as to your issue. If the file is being downloaded then you may have issues.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Cannot convert null to type "System.DateTime".

Post by jvierra »

ITEngineer wrote: Wed Nov 20, 2019 3:51 pm
jvierra wrote: Wed Nov 20, 2019 3:37 pm Just do this:

if($_.date_time){[DateTime]$_.date_time}else{$null}
Strangely it does not work?

Code: Select all

Import-Csv -Path C:\Logs\input.csv |
        if($_.date_time){[DateTime]$_.date_time}else{$null}
		ForEach-Object {$_.date_time = ([DateTime]$_.date_time).ToString('yyyy-MM-dd HH:mm:ss tt'); $_} |
	Export-Csv -NoTypeInformation -Path C:\Logs\sample_out.csv
Error code:
if : The term 'if' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:2 char:9
+ if($_.date_time){[DateTime]$_.date_time}else{$null}
+ ~~
+ CategoryInfo : ObjectNotFound: (if:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

Cannot convert null to type "System.DateTime".
At line:3 char:19
+ ... ach-Object {$_.date_time = ([DateTime]$_.date_time).ToString('yyyy-MM ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [], RuntimeException
+ FullyQualifiedErrorId : nullToObjectInvalidCast

Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null.
At line:4 char:2
+ Export-Csv -NoTypeInformation -Path C:\Logs\sample_out.csv
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCommand
Of course that won't work. I just posted how to protect the test but you still have to enumerate the values.
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Cannot convert null to type "System.DateTime".

Post by ITEngineer »

jvierra wrote: Wed Nov 20, 2019 3:54 pm I get no errors and cannot guess as to your issue. If the file is being downloaded then you may have issues.
No, it is already downloaded perfectly and then I have clicked the Unblock button to make sure Excel have access.
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Cannot convert null to type "System.DateTime".

Post by jvierra »

This has noting to do with Excel. It is just a text file.
If there are blank lines at the end then you will get an error.

Import-Csv -Path C:\Logs\input.csv | Where {$_} |
This topic is 4 years and 4 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked