Cannot convert null to type "System.DateTime".

Ask your Windows 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.
Post Reply
User avatar
ITEngineer
Posts: 171
Joined: Wed Oct 12, 2011 10:52 am

Cannot convert null to type "System.DateTime".

Post by ITEngineer » Wed Nov 20, 2019 4:29 pm

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 6 times
/* IT Engineer */

jvierra
Posts: 14009
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Wed Nov 20, 2019 4:37 pm

Just do this:

if($_.date_time){[DateTime]$_.date_time}else{$null}

jvierra
Posts: 14009
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Wed Nov 20, 2019 4:41 pm

I ran your code and it works correctly. Your file has no null values.

User avatar
ITEngineer
Posts: 171
Joined: Wed Oct 12, 2011 10:52 am

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

Post by ITEngineer » Wed Nov 20, 2019 4:51 pm

jvierra wrote:
Wed Nov 20, 2019 4: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: 171
Joined: Wed Oct 12, 2011 10:52 am

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

Post by ITEngineer » Wed Nov 20, 2019 4:53 pm

jvierra wrote:
Wed Nov 20, 2019 4: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 5:00 pm, edited 1 time in total.
/* IT Engineer */

jvierra
Posts: 14009
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Wed Nov 20, 2019 4:54 pm

I get no errors and cannot guess as to your issue. If the file is being downloaded then you may have issues.

jvierra
Posts: 14009
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Wed Nov 20, 2019 4:56 pm

ITEngineer wrote:
Wed Nov 20, 2019 4:51 pm
jvierra wrote:
Wed Nov 20, 2019 4: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: 171
Joined: Wed Oct 12, 2011 10:52 am

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

Post by ITEngineer » Wed Nov 20, 2019 5:03 pm

jvierra wrote:
Wed Nov 20, 2019 4: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: 14009
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Wed Nov 20, 2019 5:14 pm

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 {$_} |

Post Reply