CODEWITHSUNDEEP
powershell
xhr489
xhr489

Reputation: 2309

Read Excel values and create variables from column names in PowerShell

Say I have an Excel file:

enter image description here

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

Upvotes: 0

Views: 4372

Answers (2)

user2674513
user2674513

Reputation:

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

Upvotes: 1

Jeremy Fortune
Jeremy Fortune

Reputation: 2499

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

Upvotes: 1

Related Questions