How to get a date format cell as string from excel with PowerShell?

Koky · 22-06-2009, 03:25 PM

Hi all!

May problem is:
I have a scirpt, that read out the data from an excel file:

Quote:

$excel=New-Object -COM Excel.Application
$ci= [System.Globalization.CultureInfo]'en-US'
$src="path:excel.xls"
$book=$excel.Workbooks.PSBase.GetType().InvokeMember('Open', [Reflection.BindingFlags]::InvokeMethod, $null,$excel.Workbooks, $src, $ci)
$sheet= 1
$sh=$book.sheets.item($sheet)

#the read out command:
[string] $sh.Cells.item($row,$col).formulaLocal

But when read out an date format cell, the result not the date, but a number..for example: 2009.06.15 will be: 39979
How can I solve this problem?
Or how can I do, that the sript read out the cell as a string?

Thx the help in advance!
Koky

**bakuryu** · 22-06-2009, 05:57 PM

Are you sure 2009.06.15 is returned as 39979 ?? it's coming 16th June for me, not 15th.

You can export the excel in csv format and then read all values in string : read excel file - PowerShellCommunity.org - Windows PowerShell Discussion Forums - Using PowerShell - General PowerShell

otherwise you can try this :
$d = Get-Date "01/01/1900"
$add = ([long]$sh.Cells.item($row,$col).formulaLocal) - 1
[string]$d.AddDays($add)

Koky · 22-06-2009, 06:20 PM

Thank you helping!

Yes, I'm sure that the date is: 2009.06.15, and the returned value is: 39979
Thx the helping again. It's working and seems good to me!

Koky

22-06-2009, 05:57 PM	#2
bakuryu ƒ(ψ)=Θº×φ Join Date: May 2006 Location: India Age: 23 Posts: 6,621 Thanks: 19 Thanked 645 Times in 603 Posts Rep Power: 87 OS:	Re: How to get a date format cell as string from excel with PowerShell? Are you sure 2009.06.15 is returned as 39979 ?? it's coming 16th June for me, not 15th. You can export the excel in csv format and then read all values in string : read excel file - PowerShellCommunity.org - Windows PowerShell Discussion Forums - Using PowerShell - General PowerShell otherwise you can try this : $d = Get-Date "01/01/1900" $add = ([long]$sh.Cells.item($row,$col).formulaLocal) - 1 [string]$d.AddDays($add) __________________ Please don't click here

22-06-2009, 06:20 PM	#3
Koky Newbie Join Date: Jun 2009 Age: 24 Posts: 7 Thanks: 0 Thanked 0 Times in 0 Posts Rep Power: 0 OS:	Re: How to get a date format cell as string from excel with PowerShell? Thank you helping! Yes, I'm sure that the date is: 2009.06.15, and the returned value is: 39979 Thx the helping again. It's working and seems good to me! Koky

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

New To Site?	Need Help?
Register to Participate Meet our Staff Refer Forum Rules Contact Us	Frequently Asked Questions Did you forget your password? Mark Forums Read

How to get a date format cell as string from excel with PowerShell?

Programming