Reading Entire Row from Excel and Output to Text File... Or Another Excel File.

Hello everyone,

 

I’ve been trying to figure out how to read from an Excel file to output line by line from an entire row, to a text file, or another Excel file. Ideally, I’m trying to make a script that Reads an Excel File (Which contains computernames, filepaths, and other useless info) to Test-Path the computer name \Computername\Filepath, and if valid output to the text file, or output to Another Excel file. From there If I can figure out the reading of an entire column, I can hopefully set each for a variable and send a Remove-Item “\$Computername\Filepath” … But that’s much further down the road. I haven’t been able to figure out the key details of manipulating XLSX files with PowerShell (partly because I just started learning it a week ago and don’t know any other type of code/script language)

 

If anyone could point me in the direction of reading an entire column and outputting it to another excel file IDEALLY or a text file is fine I would greatly appreciate it. Also if anyone has any valuable resources to read upon/understand excel manipulation in PowerShell, along with objects, variables(like $_, and the like since I’m basically stupid at this)

 

This is what I have so far, I know it looks bad but if anyone could look it over, any advice is appreciated.

[pre]

$xl= New-Object -ComObject “Excel.Application”

$xl.Visible = $true

$wb = $xl.Workbooks.Open(“C:\Users\TEST\Desktop\Test.xlsx”)

$ws = $wb.WorkSheets.item(“Sheet1”)

$usedrange = $ws.UsedRange

$rowcount = $usedrange.rows.count

$ws.activate()

$range = $ws.Range(“A1:A$rowcount”).text

$range | Out-File $env:USERPROFILE\Desktop\Text.txt

[/pre]

 

Appreciate any help.

 

-Sushi

Sushi,
you could make your life much easier when you use the great module from Doug Finke ImportExcel. To output structured data for further use I would recommend to use Export-CSV instead of plain text files.

I really Wish I could install modules, but the group policy doesn’t let me add anything since I don’t have the rights. I will, however, start trying to Export-CSV instead of plaintext files, as you are right, it greatly helps to go through the info. Would you happen to have any other resources dealing directly with Excel, Objects, and Variables?

To install modules in your own scope you shouldn’t need any additional right.

Install-Module -Name ImportExcel -Scope CurrentUser