Read and Get Data from ExcelFile using powershell

Question

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

Answer 1

You could use the ACE OleDb Provider, eg:

$cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';Data Source=c:\temp\test.xlsx;Mode=Share Deny Write;Jet OLEDB:Engine Type=37;";
$cnStr
$cn = New-Object System.Data.OleDb.OleDbConnection $cnStr;
$cn.Open()

$cmd = $cn.CreateCommand()

$cmd.CommandText = "SELECT * FROM [Sheet1$]"
$rdr = $cmd.ExecuteReader();

$dt = new-object System.Data.DataTable

$dt.Load($rdr)

$dt | Out-GridView