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 
$d.EntireColumn.AutoFit($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 
 
$b.SaveAs("C:\Boxing\Checklist.csv") 
 
# Quit Excel (Remove "#" if you want to quit Excel after the script is completed)

Open in new window

lipotechSys EngAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

footechCommented:
"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.
YZlatCommented:
is it csv or xlsx file?
lipotechSys EngAuthor Commented:
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.

Lipotech
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
footechCommented:
I think I see what you're after.
You want the input file to be like this.
"Machine","Responsible"
"OBPBASQL03","John Doe"
"DCLWEBI01","Alice Hyman"
"DCLWEBI02","James Taylor"

Open in new window


Then your code would be like
$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 
$d.EntireColumn.AutoFit($True) 
 
$intRow = 2 
 
$colComputers = Import-CSV C:\WinUpVer\Updates1.csv 
 
# Run through the Array of Computers 
 
foreach ($line in $colComputers) 
{ 
 
$c.Cells.Item($intRow, 1) = $line.Machine.ToUpper() 
 
# Get Operating System Info 
 
$colOS =Get-WmiObject -class Win32_OperatingSystem -computername $line.Machine
 
foreach($objComp in $colOS) 
{ 
$c.Cells.Item($intRow, 2) = $line.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 
 
$b.SaveAs("C:\Boxing\Checklist.csv") 
 
# Quit Excel (Remove "#" if you want to quit Excel after the script is completed)

Open in new window


Also, I don't think the foreach loop on line 40 is necessary (but I haven't removed it) - there should only be one object returned by the Get-WmiObject command so no need to loop through it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lipotechSys EngAuthor Commented:
Great feedback and work to resolution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.