Read Excel values and create variables from column names in PowerShell

Question

Say I have an Excel file:

I need to create n variables called $col1 up to $coln and read in their respective values.

What is the best approch? I have been trying with a hashtable but I need to be able loop through the columns. This is my code. I have not wraped it around a loop yet. I can create the column names manually but I need to be able to index the columns in the Excel file.

$ExcelRowToRead = 2;

$ListOfColumns = @{"Job_name" = 1 ; "Run_time_start" = 2}   
$excel = New-Object -ComObject excel.application;
$workbook = $excel.Workbooks.Open("pathtofile.xlsx");
$workbook.sheets.item(1).activate()
$WorkbookTotal=$workbook.Worksheets.item(1)
$ListOfColumns["Job_name"]  = $WorkbookTotal.Cells.Item($ExcelRowToRead, 1) # This needs to be an index
$ListOfColumns["Job_name"].Text

the simplest solution that does NOT require any 3rd party code is to use the COM object to export the sheet as a CSV and do the other work in PoSh using the Import-CSV cmdlet. ///// if you can use a 3rd party module, take a look at the ImportExcel module. it's quite nifty ... [grin] — Lee_Dailey
– Lee_Dailey, Commented Dec 4, 2020 at 13:17

Jeremy Fortune · Accepted Answer · 2020-12-04 13:54:12Z

1

Taking Lee_Dailey's advice here and saving first to a CSV:

$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Open("pathtofile.xlsx")
$sheet = $workbook.Sheets |
    Where-Object Name -eq "SheetName" |
    Select-Object -First 1
$tempCsv = New-TemporaryFile
$sheet.SaveAs($tempCsv, 6)
$sheetData = Get-Content $tempCsv | ConvertFrom-Csv

At this point you have an object where you can extract its properties via $sheetData.col1, $sheetData.col2, etc. but if you really need them as separate variables, perhaps:

$sheetData |
    Get-Member |
    Where-Object MemberType -Eq NoteProperty |
    ForEach-Object {
        New-Variable -Name $_.Name -Value $sheetData.$($_.Name)
    }

answered Dec 4, 2020 at 13:54

Jeremy Fortune

2,5191 gold badge20 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xhr489 Over a year ago

Thanks, why do you use 6 in the SaveAs method?

Jeremy Fortune Over a year ago

This is the XlFileFormat enum (learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat) from learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas.

user2674513 · Accepted Answer · 2020-12-06 08:38:34Z

In case you don't have / want Excel:

via GitHub - dfinke/ImportExcel

> # Install-Package ImportExcel 

> $sheetData = Import-Excel '.\Excel 001.xlsx'

> $sheetData[0]

col1   col2   col3
----   ----   ----
value1 value2 value3


> $sheetData[0] | 
    Get-Member | 
    Where-Object { $_.MemberType -eq 'NoteProperty' } | 
    Select-Object -Property Name

Name
----
col1
col2
col3


> $sheetData |
    Get-Member |
    Where-Object MemberType -Eq NoteProperty |
    ForEach-Object {
      New-Variable -Name $_.Name -Value $sheetData.$($_.Name)
    }

> Get-Variable col*

Name                           Value
----                           -----
col1                           value1
col2                           value2
col3                           value3

Collectives™ on Stack Overflow

Read Excel values and create variables from column names in PowerShell

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related