Export as CSV instead of a XLS file

Question

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
# ==================

You cant save a whole workbook as a CSV file. you need to save an individual sheet. THis is a guess but $Excel.Worksheets.Item(1).SaveAs("file.csv",6). 6 being the number associated with the CSV file format. — Matt
– Matt, Commented May 22, 2015 at 19:14

Ken White · Accepted Answer · 2015-05-22 20:34:48Z

1

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.

edited May 22, 2015 at 20:34

answered May 22, 2015 at 19:24

Ken White

126k15 gold badges237 silver badges476 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matt Over a year ago

Ken: msdn.microsoft.com/en-us/library/office/ff198017.aspx. Ken white to be specific

Ken White Over a year ago

@Matt: Thanks. Added it to the answer, with credit to you for finding it. :-)

NobleMan Over a year ago

Thanks for the quick response guys. One question though. Does excel need to be installed on the machine? If so, this wont work. We are not allowed to install it on the servers where we can run the script.

Ken White Over a year ago

Absolutely. Where do you think $Excel = New-Object -Com Excel.Application comes from? Your question appears to be How do I modify this script to save to CSV?. It seems to be something different entirely based on your comment. You should probably edit your question so that it's more clear what you're asking, and indicate you want to do so without any of the Excel code you've posted.

NobleMan Over a year ago

Thanks Ken. Actually, I will install Excel and use your method.... it is much easier. I will just have to fight the bureaucracy to get it installed.

Collectives™ on Stack Overflow

Export as CSV instead of a XLS file

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related