I'm starting with powershell and this time. I have to read all the values from a excel file In this case from a specific sheet. The ultimate goal of this task is to get the values from two columns and then save this values and export to a SQL Table because I have a StoreProcedure to read the table and then create all extended properties needed in one database with thouse values
This is the code but I not able to get correctly the data from excel
$Excel = New-Object -ComObject Excel.Application
$Path = '' #I'm using a shared folder
$Workbook = $Excel.Workbooks.Open($Path)
$workSheet = $Workbook.Sheets.Item(4) #The number is for because the excel file has 4 sheets
#and the sheet desired is the last one
$Output = @()
$Output = [pscustomobject][ordered]@{ #I think with this I could go through the table but I
#would have to calculate the coordinates first.
Rol = $Worksheet.Range("A4").text
Member = $Worksheet.Range("B4").text
}
Example of how looks the sheet
Rol | Member | Description |
---|---|---|
Admin | name + email | Description of the rol |
Data Lead | name + email | Description of the rol |
Arquitect | name + email | Description of the rol |
DBA | name + email | Description of the rol |
QA | name + email | Description of the rol |
So, my intention is go through the table and get the date as array but to take into account. Sometimes a role can have several members associated with it and that's means more rows in member column
Best Regards and thanks in advance