asked on

Formatting issues while exporting to Excel within Powershell

Hi Guys,

I am trying to run the following cmdlet within powershell and it works fine, its gives me what I need but when I export it to Excel as a .xls file the output format is just horrible coz everything is in one coloumn, is there a way I can export to exel with the data in seperate coloumns?

get-wmiobject win32_bios -computer (cat c:\servers.txt) | ft __server,name,@{label='Release Date';expression={$_.ConvertToDateTime($_.releasedate)}} | out-file c:\server.xls

Can anyone help me out on this? Just to get the formatting correct in Excel otherwise the command let is fine!

PS: I have tried using Format-wide and most of the format cmdlets with no luck :-(

I dont want to export it as a csv file that is why I am not using the Export-Csv cmdlet

Thanks in Advance
A
win32-bios.xls

soostibi

PowerShell can not output results to Excel directly. You can extend your script to do so, but it is not very easy. I recommend to export your data to a CSV file, which can easily be opened by Excel.

get-wmiobject win32_bios -computer (cat c:\servers.txt) | select-object __server,name,@{name='Release Date';expression={$_.ConvertToDateTime($_.releasedate)}} | export-csv c:\server.csv -notypeinformation

soostibi

If you still insist on Excel, use solutions from here:

http://pathologicalscripter.wordpress.com/out-excel/

kerry_dsouza

ASKER

I did try doing the Export-csv command and the output gave me junk values....

Can I create a new object and export to excel not that I knw how to do that but just curious...
test.csv

kerry_dsouza

ASKER

@soostibi --> You know I did come across that blog before posting the question here and was hoping there would be alternative solution coz I cant understand how I would use that solution!

kerry_dsouza

ASKER

I tried using the code but the output I get in rather odd output.xlsx

my servers.txt has my 2 servers listed as

DXBEXCH301
DXBFILE300
test.txt

ASKER CERTIFIED SOLUTION

soostibi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kerry_dsouza

ASKER

I was initially using the "Select" cmdlet but whenever I did that I got an "IIllegal Label" error message, so I tried changing it to FT and it worked but the end result was the junk output which I have attached.

soostibi

Have you tried my suggestion?

kerry_dsouza

ASKER

I did actually but not before I un-installed PS V2.0 (ctp3) and installed the KB968930 and yesh if i change the ft to Select it works and I get the excel file output!! whohooo

Get-WmiObject win32_bios -computer (cat c:\server.txt) | select @{label='Server Name';expression={$_.__server}},@{label='Bios Name';expression={$_.name}},@{label='Release Date';expression={$_.ConvertToDateTime($_.releasedate)}} | out-excel

Is there a way we change autosave this excel to a location instead of saving it manually? since this is
invoking a function do we have to modify the function to save the file as well?

kerry_dsouza

ASKER

Found the saving the excel file bit here --> http://pathologicalscripter.wordpress.com/about/