Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Formatting issues while exporting to Excel within Powershell

Posted on 2011-04-21
10
Medium Priority
?
759 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:kerry_dsouza
  • 6
  • 4
10 Comments
 
LVL 13

Expert Comment

by:soostibi
ID: 35439977
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

0
 
LVL 13

Expert Comment

by:soostibi
ID: 35440007
If you still insist on Excel, use solutions from here:

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

0
 

Author Comment

by:kerry_dsouza
ID: 35440016
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kerry_dsouza
ID: 35440032
@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!
0
 

Author Comment

by:kerry_dsouza
ID: 35440188
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
0
 
LVL 13

Accepted Solution

by:
soostibi earned 400 total points
ID: 35440641
The last line should be this:

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

Format-Table (ft) must not be followed by any other expression, it is meant to output info onto the screen, you must not export it.
0
 

Author Comment

by:kerry_dsouza
ID: 35446299
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.
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35446317
Have you tried my suggestion?
0
 

Author Comment

by:kerry_dsouza
ID: 35446886
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?

0
 

Author Comment

by:kerry_dsouza
ID: 35446913
Found the saving the excel file bit here --> http://pathologicalscripter.wordpress.com/about/
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
A walk-through example of how to obtain and apply new DID phone numbers to your cloud PBX enabled users that are configured in Office 365. Whether you have 1, 10 or 100+ users in your tenant, it's quite easy to get them phone-enabled and making/rece…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Loops Section Overview

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question