VMware Cloud Community
dwchan
Enthusiast
Enthusiast

Capture Excel data into an array like object

I am trying to using the Excel object in Powershell to read in a table

$Excel = New-Object -COM "Excel.Application"
$Excel.Visible = $False
$WorkBook = $Excel.Workbooks.Open($DataSourcePath)

$WorkSheetname = 'esx02.tataoui.com' # physical ESX host name
$WorkSheet = $WorkBook.Sheets.Item($WorkSheetname)

$NestedESX = $WorkSheet.UsedRange.Rows.Columns.Value()

dwchan_0-1613668721310.png

I am able to pull the table in but at a somewhat unstructure

PS C:\Users\cdominic> echo $NestedESX
Parent Host
Nested ESX Hostname
Nested CPU
Nested Mem
Nested Cache
Nested Capacity
Nested Host IP
Nested Subnet
Nested GW
HostMgmtVLAN
Host DNS1
Host DNS2
Nested Host PW
Host Domain
LocalUser
LocalPW
Nested VCSA IP
esx02.tataoui.com
esx101.tataoui.com
4
32
100
1100
172.16.10.20
255.255.255.0
176.16.10.1
0
192.168.30.2
192.168.30.3
VMware1!
tataoui.com
dwcadmin
VMware1!
172.16.10.30
esx02.tataoui.com
esx102.tataoui.com
4
32
100
1100
172.16.10.21
255.255.255.0
176.16.10.1
0
192.168.30.2
192.168.30.3
VMware1!
tataoui.com
dwcadmin
VMware1!
172.16.10.30
esx02.tataoui.com
esx103.tataoui.com
4
32
100
1100
172.16.10.22
255.255.255.0
176.16.10.1
0
192.168.30.2
192.168.30.3
VMware1!
tataoui.com
dwcadmin
VMware1!
172.16.10.30

I am looking for feedback as to how I can pull this in as an array or in a somewhat more structured format so I can

1. process the information in a loop and call out the information one line at a time e.g echo $NestESX[0] , as it is not an array right now

2. with each line, I can pull individual set of value out like you would do for an object objective properly e.g $NestESX[0].Nest Host IP

Any feedback and suggestion would be strongly appreciated 

Reply
0 Kudos
4 Replies
LucD
Leadership
Leadership

Instead of doing all this with a COM object, why not simply use the ImportExcel module?
It just requires a simple Import-Excel to get the worksheet into a PS array.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
dwchan
Enthusiast
Enthusiast

I would agree as I was doing it that way before but end up switch over to Excel Object as it has cursor control to which cell I am pulling the data from.  It allows a better Excel format for configuration entries

dwchan_0-1613670257975.png

 Perhaps I would used both methods in my script.  Let me take a look at my previous work again.  Ideally, would like to be able to call up the row individual in a loop while able to call up the column value within that row like a property, which if I recall, you can do that with import-excel

Reply
0 Kudos
LucD
Leadership
Leadership

This screenshot doesn't really look like the example at the beginning


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
dwchan
Enthusiast
Enthusiast

Sorry, I got both formats within the same workbook.  And since I pivoted over to Excel Object, totally forgot about Import-Excel, where defining the property of an object (or column in my cause in excel) is so much easier.  Stay tune and thanks for the help, brain fart on my part

Reply
0 Kudos