write data into excel using powershell from sql table

Question

I have a .xlsx file which was made into data table by oledb provider.Now I want to add value to that .xlsx based on the sql table data I have
(which is also converted into a csv file Book1.csv)

The sql table consists of name and notes...
Where name column is same in both .xlsx file and sql variable $sql

I want to add that close notes to f column of .xlsx file if the value of name matches with the value of sql table "A" column One I wrote below is very slow and not effective.
Any help would be highly appreciated.

$Excel = New-Object -ComObject Excel.Application 
$Workbook = $Excel.Workbooks.Open('C:\Users\VIKRAM\Documents\Sample - Superstore.xlsx')
$workSheet = $Workbook.Sheets.Item(1)
$WorkSheet.Name
$Found = $WorkSheet.Cells.Find('$Data.number')
$Found.row
$Found.text
$Excel1 = New-Object -ComObject Excel.Application 
$file = $Excel1.Workbooks.Open('C:\Users\VIKRAM\Documents\Book1.xlsx')
$ff=$file.Sheets.Item(1)
$ff.Name
$ff1=$ff.Range("A1").entirecolumn
$ff1.Value2

foreach ($line in $ff1.value2){

if( $found.text -eq $line)
{
    Write-Host "success"
    $fff=$ff1.Row   
    $WorkSheet.Cells.item($fff,20) =$ff.cells.item($fff,2)
}
}

Data in .xlsx file

Number  Priority  Comment
612721  4 - High

Data in Book1.csv

Number Clo_notes
612721 Order has been closed

I need to update clo_notes value to comment in .xlsx file if this "number" column in each file matches update the clos_notes to the corresponding column of comment

Have you looked into the ImportExcel module? Install-Module ImportExcel : Here — jrider
– jrider, Commented May 1, 2018 at 14:04
@jrider Challenge is I was not able to install any module in my environment due to security constraints. — Rmm
– Rmm, Commented May 1, 2018 at 14:32
Just out of curiosity, what has Nebraska to do with the structure of your data`? — user6811411
– user6811411, Commented May 1, 2018 at 14:41
That raises the question how $Data.Numberis defined at the point you use it? — user6811411
– user6811411, Commented May 1, 2018 at 16:24

anu start · Accepted Answer · 2018-05-02 14:20:24Z

It looks like you answered my question about where "Nebraska" falls into the data.

Make sure to release any COM objects, or you'll have orphaned Excel processes.

You might try something like this. I was able to write the Clo_notes value into column 6 as you were requesting:

## function to close all com objects
function Release-Ref ($ref) {
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

## open Excel data
$Excel = New-Object -ComObject Excel.Application 
$Workbook = $Excel.Workbooks.Open('C:\Users\51290\Documents\_temp\StackOverflowAnswers\Excel.xlsx')
$workSheet = $Workbook.Sheets.Item(1)
$WorkSheet.Name

## open SQL data
$Excel1 = New-Object -ComObject Excel.Application 
$file = $Excel1.Workbooks.Open('C:\Users\51290\Documents\_temp\StackOverflowAnswers\SQL.xlsx')
$sheetSQL = $file.Sheets.Item(1)
$dataSQL = $sheetSQL.Range("A1").currentregion

$foundNumber = 0
$row_idx = 1
foreach ($row in $WorkSheet.Rows) {
    "row_idx = " + $row_idx
    if ($row_idx -gt 1) {
        $foundNumber = $row.Cells.Item(1,1).Value2
        "foundNumber = " + $foundNumber

        if ($foundNumber -eq "" -or $foundNumber -eq $null) {
            Break
        }

        foreach ($cell in $dataSQL.Cells) {
            if ($cell.Row -gt 1) {
                if ($cell.Column -eq 1 -and $cell.Value2 -eq $foundNumber) {
                    $clo_notes = $sheetSQL.Cells.Item($cell.Row, 2).Value2
                    Write-Host "success"
                    $WorkSheet.Cells.item($row_idx, 6).Value2 = $clo_notes
                }

            }

        }
    }
    $row_idx++
}


$Excel.Quit()
$Excel1.Quit()

## close all object references
Release-Ref($WorkSheet)
Release-Ref($WorkBook)
Release-Ref($Excel)
Release-Ref($Excel1)

Yes this logic helps!!...what if there are 10 rows?how to increment it
I modified the logic to loop through the "Excel" data and find the Number column in the "SQL" data. I'm not an expert with the Range object in Excel, so there's probably a better/faster way to approach this.

Collectives™ on Stack Overflow

write data into excel using powershell from sql table

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related