CODEWITHSUNDEEP
excelpowershellcsv
NobleMan
NobleMan

Reputation: 515

Export as CSV instead of a XLS file

I have a script that places everything nicely into a spread sheet. The problem is, I need it to export as a csv file instead. All the foreach loops are completely baffling me here as far as where to put the export csv functions in the script. If someone could could school me on how to get the fields into a csv file, It would be greatly appreciated. 

$date = 0
$date = get-date -format "yyyy-MMM-dd-hhmm"
$date

#New Excel Application
$Excel = New-Object -Com Excel.Application
$Excel.visible = $False

# Create 1 worksheets
$Excel = $Excel.Workbooks.Add()

# Assign each worksheet to a variable and
# name the worksheet.
$Sheet1 = $Excel.Worksheets.Item(1)
$Sheet1.Name = "HH_SERVERS"

#Create Heading for General Sheet
$Sheet1.Cells.Item(1, 1) = "Machine_Name"
$Sheet1.Cells.Item(1, 2) = "OS"
$Sheet1.Cells.Item(1, 3) = "Software"
$Sheet1.Cells.Item(1, 4) = "Vendor"
$Sheet1.Cells.Item(1, 5) = "Version"

$colSheets = ($Sheet1)
foreach ($colorItem in $colSheets)
{
    $intRow = 2
    $intRowDisk = 2
    $intRowSoft = 2
    $intRowNet = 2
    $WorkBook = $colorItem.UsedRange
    $WorkBook.Interior.ColorIndex = 20
    $WorkBook.Font.ColorIndex = 11
    $WorkBook.Font.Bold = $True
}

#Auto Fit all sheets in the Workbook
foreach ($colorItem in $colSheets)
{
    $WorkBook = $colorItem.UsedRange
    $WorkBook.EntireColumn.AutoFit()
    clear
}

$Servers = get-content "c:\temp\HH_Servers.txt"
foreach ($Server in $Servers)
{
    $GenItems2 = gwmi Win32_OperatingSystem -Comp $Server
    $Software = gwmi Win32_Product -Comp $Server 

    # Populate General Sheet(1) with information
    foreach ($objItem in $GenItems2)
    {
        $Sheet1.Cells.Item($intRow, 2) = $objItem.Caption
    }

    #Populate Software Sheet

    foreach ($objItem in $Software)
    {
        $Sheet1.Cells.Item($intRowSoft, 1) = $Server
        $Sheet1.Cells.Item($intRowSoft, 3) = $objItem.Name
        $Sheet1.Cells.Item($intRowSoft, 4) = $objItem.Vendor
        $Sheet1.Cells.Item($intRowSoft, 5) = $objItem.Version
        $intRowSoft = $intRowSoft + 1
    }
}

$outputfile = "c:\temp\" + $date.toString() + "-HH_Server_Software"
$Excel.SaveAs($outputfile)
$Excel.Close()

Write-Host "*******************************" -ForegroundColor Green
Write-Host "The Report has been completed." -ForeGroundColor Green
Write-Host "*******************************" -ForegroundColor Green
# ========================================================================
# END of Script
# ==================

Upvotes: 0

Views: 184

Answers (1)

Ken White
Ken White

Reputation: 125689

You can't save an entire workbook as CSV. You need to save the individual worksheet instead. The file format value for CSV is 6 (don't remember where I found that out though):

$xlCSV = 6      
$outputfile = "c:\temp\" + $date.toString() + "-HH_Server_Software.csv"
$Sheet1.SaveAs($outputfile, $xlCSV)

(Tested on Windows 7 with Excel 2013.)

Thanks to @Matt for a comment with a link to the XLFileFormat Enumerations.

Upvotes: 1

Related Questions