dump to csv -- image column

Question

Post reply

dump to csv -- image column

Bruin

SSCrazy

Points: 2107
More actions
August 27, 2024 at 11:35 am
Go to Answer
#4450546

If there is an Image Column in mssql table and I wanted to use export-csv to dump this info to file how do you handle that column to be able to import that csv to another DB.
Thanks.
Johan Bijnens

SSC Guru

Points: 135506
More actions
August 27, 2024 at 1:09 pm
Answer
#4450602

Did you check with DBATools ?
ref:
Export-DbaBinaryFile
Import-DbaBinaryFile
Johan

Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Bruin SSCrazy Points: 2107 More actions · Answer 1

I ran into this issue using dbatools

WARNING: [09:31:34][Get-DbaDatabase] Failure | The certificate chain was issued by an authority that is not

trusted

Phil Parkin SSC Guru Points: 247158 More actions · Answer 2

Bruin wrote:

I ran into this issue using dbatools
WARNING: [09:31:34][Get-DbaDatabase] Failure | The certificate chain was issued by an authority that is not trusted

Set Trust Server Certificate to true to jump past this, assuming you do trust it.

Johan Bijnens SSC Guru Points: 135506 More actions · Answer 3

That is normal as security has become tighter with the lastest updates.

To ommit this extra security for dbatools you can use

<# https://docs.dbatools.io/Set-DbatoolsInsecureConnection.html
Synopsis

Sets the default connection settings to trust all server certificates and not require an encrypted connection.
Description

Microsoft changed the default connection settings in the SQL Server connection libraries
to require an encrypted connection and not trust all server certificates.

This command reverts those defaults and sets the default connection settings to trust all server
certificates and not require encrypted connections.

You can read more here: https://dbatools.io/newdefaults
#>
$ItsSet = Set-DbatoolsInsecureConnection -SessionOnly

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Bruin SSCrazy Points: 2107 More actions · Answer 4

This is the code I'm using to dump mssql table. When I check length after loading to Postgresql DB it's smaller than the mssql

ImageSource in mssql is defined as Image

ImageSource in PG is defined as bytea.

# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null;

# Define the connection string
$connectionString = "Server=xxx;Database=xxx;Integrated Security=True;"

# Define the query to retrieve image data
$query = @"
SELECT 
[Id], [ImageName], [ImageType], [ImageSource],
[ReceivedDateTime], [ImagePath], [Site],
[MachineNbr], [LineNbr], [TakeUpNbr], [SpoolNbr],
[ImageIndex], [CameraNbr], [SpoolStartDt],
[SpoolStartTime], [DefectDate], [DefectTime],
[DefectNbr], [DefectClass], [Reviewer], [UserDefectInput]

FROM Image_Classification_Master where id = 11761783;
"@

# Execute the query and store the results
$results = Invoke-Sqlcmd -ConnectionString $connectionString -Query $query 

# Define the path for the CSV file
$csvPath = "C:\fileloading\icm_0615_2024.csv"

# Initialize an array to store the CSV data
$csvData = @()

# Process each row in the results
foreach ($row in $results) {
   
    # Convert the image data to a base64 string
    $base64Image = [Convert]::ToBase64String($row.ImageSource)

    # Create a custom object with the Id and base64 image data
    $csvRow = [PSCustomObject]@{
        Id = $row.Id
        ImageName = $row.ImageName
        ImageType = $row.ImageType
        ImageSource = $base64Image
        ReceivedDateTime = $row.ReceivedDateTime
        ImagePath = $row.ImagePath
        Site = $row.Site
        MachineNbr = $row.MachineNbr
        LineNbr = $row.LineNbr
        TakeUpNbr = $row.TakeUpNbr
        SpoolNbr = $row.SpoolNbr
        ImageIndex = $row.ImageIndex
        CameraNbr = $row.CameraNbr
        SpoolStartDt = $row.SpoolStartDt
        SpoolStartTime = $row.SpoolStartTime
        DefectDate = $row.DefectDate
        DefectTime = $row.DefectTime
        DefectNbr = $row.DefectNbr
        DefectClass = $row.DefectClass
        Reviewer = $row.Reviewer
        UserDefectInput = $row.UserDefectInput


        
    }

    # Add the custom object to the CSV data array
     $csvData += $csvRow
}

# Export the CSV data to a file
$csvData | Export-Csv -Path $csvPath -NoTypeInformation

Bruin SSCrazy Points: 2107 More actions · Answer 5

I have another thread "Display Bytea Image" that has a little more info.

-- This is what I used to verify length's don't match.

In MS SQL Server, you'll use the HASHBYTES function and in PostgreSQL, you'll use the sha256 function or any of the other string/binary functions that match the algorithm choice used for HASHBYTES.

Thanks.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 6

Phil Parkin

SSC Guru

Points: 247158

January 13, 2025 at 1:03 pm

#4514693

What is your question?

Bruin SSCrazy Points: 2107 More actions · Answer 7

The question is am I'm using the correct code to capture the image from mssql to csv export. It seems that when loaded to PG db that the size of that field is smaller than mssql..

Phil Parkin SSC Guru Points: 247158 More actions · Answer 8

Bruin wrote:

The question is am I'm using the correct code to capture the image from mssql to csv export. It seems that when loaded to PG db that the size of that field is smaller than mssql..

Maybe PG handles image data differently from SQL Server? Surely the best test is to compare the actual images rendered from both systems?

Bruin SSCrazy Points: 2107 More actions · Answer 9

After csv load to PG the image won't open in app. seems like size smaller than mssql.

I ran this from PG

SELECT length(imagesource) as image_size, upper(md5(imagesource)) FROM

image_classification_master

Bruin SSCrazy Points: 2107 More actions · Answer 10

Bruin

SSCrazy

Points: 2107

January 13, 2025 at 3:27 pm

#4514805

Image size ==> 2050

Phil Parkin SSC Guru Points: 247158 More actions · Answer 11

If it were me, I'd push exactly the same image into both DBs using the app.

Then you've got something to aim for.

Bruin SSCrazy Points: 2107 More actions · Answer 12

The app is just display engine... I use the PS to export from mssql and load to PG...

This piece of code is the issue.

# Convert the image data to a base64 string

$base64Image = [Convert]::ToBase64String($row.ImageSource)

Thanks.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 13

Bruin wrote:

The app is just display engine... I use the PS to export from mssql and load to PG...
This piece of code is the issue.
# Convert the image data to a base64 string $base64Image = [Convert]::ToBase64String($row.ImageSource)
Thanks.

So how did the image data get inserted into the database?

dump to csv -- image column

Cookies on SQLServerCentral