0

working with Excel sheets, I've the following array (coming from the Import-Excel module)

PS C:\Projects> $AllSheets = Get-ExcelSheetInfo $excel_path
PS C:\Projects> $AllSheets

Name             Index  Hidden Path
----             -----  ------ ----
Applications         1 Visible C:\Projects\Data\policy.xlsx
Certificates         2 Visible C:\Projects\Data\policy.xlsx
Webdomains           3 Visible C:\Projects\Data\policy.xlsx
Ips_Hosts            4 Visible C:\Projects\Data\policy.xlsx
Ips_Hosts_subnet     5 Visible C:\Projects\Data\policy.xlsx
Extensions           6 Visible C:\Projects\Data\policy.xlsx
Files                7 Visible C:\Projects\Data\policy.xlsx
Directories          8 Visible C:\Projects\Data\policy.xlsx

I used to import (with Import-Excel module) data in a single variable like this :

$AllItemsInAllSheets = $AllSheets | ForEach-Object { Import-Excel $_.Path -WorksheetName $_.Name }

But once my sheets are not with the same structure, it's getting messy. I was thinking of dynamically assign the content of each sheet to its own variable like this :

$AllSheets | ForEach-Object { $"$_.name" = Import-Excel $_.Path -WorksheetName $_.Name }

Thinking it would create all $Applications, $Certificates etc... I'm guessing I can't get the syntax right or am not escaping correctly the "$"

Douda
  • 121
  • 8
  • 1
    Use a HashTable, see also: [How do I automaticaly create and use variable names?](https://stackoverflow.com/a/68830451/1701026) `$Sheet = @{}; $AllSheets | ForEach-Object { $Sheet[$_.Name] = Import-Excel ...` – iRon Dec 29 '22 at 14:06
  • Good call, thank you ! I was so focused on a potential syntax error, didn't think of another solution – Douda Dec 29 '22 at 14:24

2 Answers2

2

As iRon already mentioned in the comments the idiomatic solution is to populate a hashtable (an unordered dictionary) with the values and use the "variable name" as the key for each dictionary entry:

$SheetIndex = @{}

$AllSheets | ForEach-Object { $SheetIndex[$_.Name] = $_ }

For completeness, the answer to your original question is:

PowerShell doesn't support "variable variable names" like PHP or Perl - instead, use the New-Variable/Set-Variable cmdlets to update variables by name:

$AllSheets | ForEach-Object { New-Variable -Name $_.Name -Value $_ }
Mathias R. Jessen
  • 157,619
  • 12
  • 148
  • 206
0

Thanks to @iRon, quick solution

$Sheet = @{}; $AllSheets | ForEach-Object { $Sheet[$_.Name] = Import-Excel $_.Path -WorksheetName $_.Name }

Works as excepted

PS C:\Projects> $Sheet

Name                           Value
----                           -----
Ips_Hosts                      {@{ip=10.102.6.177}, @{ip=10.102.6.201}, @{ip=10.128.48.149}, @{ip=10.149.8.84}…}
Applications                   {@{sha2=35028e686ce29f3471d08b845c34851a019688af0ed83cbc7b40dbb8f7808970; name=f_0005a7}, @{sha2=92beaf20b66c8167e6260748e3ae1414f0e5c375ff145298d83693635dc6… 
Ips_Hosts_subnet               {@{ip=10.72.20.0; mask=255.255.255.0}, @{ip=10.72.25.0; mask=255.255.255.0}, @{ip=10.72.28.0; mask=255.255.255.0}}
Files                          {@{pathvariable=[NONE]; path= C:\Program Files (x86)\Dell\UpdateService\Service\InvColPC.exe; scheduled=True; features.1=AUTO_PROTECT; features.2=BEHAVIORAL_… 
Webdomains                     {@{domain=20.0.0.10}, @{domain=198.36.86.161}}
Extensions                     {@{Extensions=ade}, @{Extensions=adp}, @{Extensions=ldb}, @{Extensions=mdb}…}
Certificates                   {@{signature_issuer=Sectigo RSA Code Signing CA; signature_company_name=OpenDNS; signature_fingerprint.algorithm=sha1; signature_fingerprint.value=2dc1e76c8a… 
Directories                    {@{pathvariable=[NONE]; directory= C:\Apps\customer\; recursive=True; scheduled=True; features.1=AUTO_PROTECT; features.2=BEHAVIORAL_ANALYSIS}, @{pathvariabl…
Douda
  • 121
  • 8