Excel update by OLEDB

Hey all,

I know just enough PowerShell to get by and can usually figure out how to research how to’s but I cannot seem to find a sound example of how to connect to multiple Excel files and update a “master” spreadsheet via OLEDB/SQL. I am used to sql but certainly open to up-to-date ways to do this…I tend to try to use easier to read format in case code breaks (no nested loops for example) and next coder can figure it out…hence why a 1 liner usually does the trick when I update other sources like a database…

file_01.xlsx is a master sheet that will need to be updated by various sheets
file_02.xlsx contains keywords that are used to try to match a column value in master and if a match update a specific column with file_02.xlsx Column 1 value

the following is by no means meant to work but simply to try to give everyone a way to see what my attempt is - thanks

$directory = split-path -parent $MyInvocation.MyCommand.Definition
$spreadsheet_01 = "$directory\file_01.xlsx"
$spreadsheet_02 = "$directory\file_02.xlsx"

$connection = New-Object System.Data.OleDb.OleDbConnection ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$spreadsheet_01;Extended Properties='Excel 12.0;HDR=YES;IMEX=0';")
$connection.open()

$excel = New-Object -Com Excel.Application
$book  =  $excel.Workbooks.Open($spreadsheet_02)
$sheet = $book.Worksheets.item(1)
$range = $sheet.UsedRange

foreach ($row in $range.Rows) 

  {
    Set connection.Execute("UPDATE [sheet1$] Set [Column_01] ='$row.Cells.Item(1).Text' WHERE [Column_01] -match $row.Cells.Item(2).Text")

  }


  # ... save spreadsheet_01 and close all open connections 

bslintx,
Welcome to the forum. :wave:t4:

Have you considered using the exelent module from Doug Finke

?

It usually makes it pretty easy to work with Excel sheets

Olaf,

Thanks for the welcome. Yes, I had looked into these type of modules and most certainly would be ideal but I am limited to what I can install. So I have to use what’s basically installed by default and cannot import modules which may require permissions. Thanks!

Who is going to run your script? To install something in your own scope you don’t need extended permissions. :wink:

Olaf, Policy on a company imaged computer

I created a way that works as i needed it…

$dir = split-path -parent $MyInvocation.MyCommand.Definition
$spreadsheet_01 = $dir + "\file_01.xlsx"
$spreadsheet_02 = $dir + "\file_02.xlsx"

$strconn = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$spreadsheet_01;Extended Properties="Excel 12.0;HDR=YES;IMEX=0";'


$conn = New-Object System.Data.OleDb.OleDbConnection ($strconn)
$conn.Open()
$cmd = $conn.CreateCommand()


$excel = New-Object -Com Excel.Application
$book  =  $excel.Workbooks.Open($spreadsheet_02)
$sheet = $book.Worksheets.item(1)
$range = $sheet.UsedRange


foreach ($row in $range.Rows) 

  {

    $cmd.CommandText = "UPDATE [Sheet1$] SET [Column_02]='" + $row.Cells.Item(1).Text + "' WHERE [Column_08] LIKE '%" + $row.Cells.Item(2).Text + "%'"
    
  }

$conn.close() 
$excel.Close
$excel.Quit()

Hey hey … cool … and thanks for sharing. :+1:t4: :love_you_gesture:t4: :slightly_smiling_face:

That shouldn’t matter actually … you may try it anyway:

Install-Module -Name ImportExcel -Scope CurrentUser

You may talk to the responsible person about the tools you need to do your work. :wink:

1 Like

lol…amen brother…always having to improvise but d/l recommended package and will see if doable…thanks for the reference!

hmmm … I’m unsure if you got what I meant … you really just have to run this code snippet as it is in a PowerShell console. PowerShell will download the module for you and install it in your profile. No need to download it in advance. :wink: :love_you_gesture:t4:

Yes sir…I was referencing your first post. So, I ran snippet as suggested and thumbs up! Thanks for the persistence and patience…this module should be very useful to me - thanks!

You’re welcome. :+1:t4:

I’m glad it helped. :wink: