Capture Excel data into an array like object

[dwchan]

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()

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 

[LucD]

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.

[dwchan]

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

 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

[LucD]

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

[dwchan]

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