CODEWITHSUNDEEP
sql-serverpowershell
Sunil Singh
Sunil Singh

Reputation: 31

How could we download Images By a URl from a table in PowerShell

In one stored procedure, I have 5 columns:

enter image description here

How could I download images from provided URL in SQL Server table. Table structure in attached screenshot.

I am using this PowerShell script:

param (
        [string]$DatabaseServer = "codb01d.brandmuscle.local"
)
################## OrderReportExport ################

Import-Module JAMS
 $FileServerDestination = "C:\Temp\$(New-Guid)\"
 New-Item $FileServerDestination -itemType directory

 $OrderReportexport = $FileServerDestination+"OrderReport.csv"

# run reports
#sqlcmd -S $DatabaseServer -E -d "CentivPOS" -Q "Exec [dbo].[SE-OrderReport]" -o $OrderReportexport -W -s ","

function DataResults
{
$sql =@" 
exec [dbo].[LoationImagedata] 
"@
    
    Invoke-Sqlcmd -ServerInstance $Databaseserver -Database "Centiv" -Query $sql -QueryTimeout "2000"
    }

DataResults

Upvotes: 0

Views: 545

Answers (1)

FranciscoNabas
FranciscoNabas

Reputation: 526

First you need to find the final URI that leads to the image. Like: https://imagerepo.com/some/image.png

Then you can use the Invoke-Webrequest cmdlet to download it:

Invoke-WebRequest -Uri https://imagerepo.com/some/image.png -OutFile C:\image.png

To upload it to SQL you can refer to this:

[Byte[]]$file = get-content -Encoding Byte C:\TEMP\pictures\image1.jpg
$hexString = ($file|ForEach-Object ToString X2) -join ''
$hexString = '0x'+$hexString 

$cmd.CommandText ="UPDATE QuoteData SET PII_Image1 = $hexString Where QuoteNumber =  '"+$WorkSheet.Range('G7').Text.replace("'","''")+"'"

Upvotes: 2

Related Questions