0

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

1

1 Answer 1

0

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
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.