Link to home
Start Free TrialLog in
Avatar of Bobby Batts
Bobby BattsFlag for United States of America

asked on

Scripting PowerShell to Read/Write an Excel .CSV file

I modified the following script to read a .csv  excel file with two columns of data.  For example:

Machine Name        Responsible
OBPBASQL03          John Doe
DCLWEBI01            Alice Hyman
DCLWEBI02             James Taylor

For some reason, I cannot read nor write the second column (Responsible).  What additional modifications must I make to read from the .csv excel file and write to a .csv excel file.  Please see the script below:
$erroractionpreference = "SilentlyContinue" 
# Create a New Excel Object for storing Data 
$a = New-Object -comobject Excel.Application 
$a.visible = $True  
$b = $a.Workbooks.Add() 
$c = $b.Worksheets.Item(1) 
# Create the title row 
$c.Cells.Item(1,1) = "Machine Name" 
$c.Cells.Item(1,2) = "Responsible"
$c.Cells.Item(1,3) = "OS" 
$c.Cells.Item(1,4) = "Description" 
$c.Cells.Item(1,5) = "Service Pack" 
$d = $c.UsedRange 
$d.Interior.ColorIndex = 23 
$d.Font.ColorIndex = 2 
$d.Font.Bold = $True 
$intRow = 2 
$colComputers = get-content C:\WinUpVer\Updates1.csv 
# Run through the Array of Computers 
foreach ($strComputer in $colComputers) 
$c.Cells.Item($intRow, 1) = $strComputer.ToUpper() 
# Get Operating System Info 
$colOS =Get-WmiObject -class Win32_OperatingSystem -computername $Strcomputer 
foreach($objComp in $colOS) 
$c.Cells.Item($intRow, 2) = $objComp.Responsible
$c.Cells.Item($intRow, 3) = $objComp.Caption
$c.Cells.Item($intRow, 4) = $objComp.Description 
$c.Cells.Item($intRow, 5) = $objComp.ServicePackMajorVersion 
$intRow = $intRow + 1 
$intRow = $intRow + 1 
# Save workbook data 
# Quit Excel (Remove "#" if you want to quit Excel after the script is completed)

Open in new window

Avatar of footech
Flag of United States of America image

"Reponsible" is not a property of the WMI class Win32_OperatingSystem.

By the way, a .CSV is just a plain text file.  As such it doesn't make sense to save it as an Excel file.  If you really want the formatting of an Excel spreadsheet, continue as you're doing but give the saved file a matching extension like ".xlsx".  If you don't need all the formatting, you can skip the use of Excel via COM object and just use the Export-CSV cmdlet.
is it csv or xlsx file?
Avatar of Bobby Batts


At this point I reading from a .txt file and outputting to a .xlsx file.   I remain unclear on what I must do to read both the Machine Name and the Responsible Name from the .txt file and output both to an .xlsx file.  I modified the file extensions to refelect .txt(input) file and .xlsx(output) file. What must I do within this script to achieve this objective. I now realize that Responsible is not a property of the WMI class Win32_OperatingSystem.

You want to have both machine name and responsible name taken from the text file now?  Then you should use a CSV input file consisting of exactly those two columns, and read the file with Import-CSV.
Avatar of footech
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great feedback and work to resolution.