Export Ditto Clipboard Manager to Files

Background

I wanted to export data from my Ditto clipboard manager database.

Process

I found that I could use the PowerShell module ReallySimpleDatabase to iterate through the rows in the table. But the images were not a valid file format when I saved the byte stream to disk. Turns out, BMP files on disk have an additional couple bytes that BMP files in memory don’t have. Check it out:

http://nickbriz.com/databending101/bmp.html

https://en.wikipedia.org/wiki/BMP_file_format

Knowing this, I just needed to add a set of hex bytes to a file before adding in the rest of the stream. I also used the handy image manipulation toolkit to convert from bmp to png.

Requires PowerShell 7 with ImagePlayground and my forked version of ReallySimpleDatabase

$db = Get-Database ditto.sq3

$db.InvokeSql("
select * from main m inner join data d on d.'lParentID' = m.'lID'
where d.strClipBoardFormat = 'CF_DIB'
--limit 1000
") | ForEach-Object -Parallel {

    $mod_time = Get-Date -UnixTimeSeconds $_.ldate
    $access_time = Get-Date -UnixTimeSeconds $_.lastPasteDate
    $ext = switch -Regex  ($_.strClipBoardFormat) {
        "CF_DIB" { "bmp" }
        "rich text" { "rtf" }
        default { "txt" }
    }
    $name = "{0}-{1}-{2}-{3}.{4}" -f @(
        $access_time.ToString("yyyy-MM-dd hh_mm_ss")
        $_.lID
        $_.strClipBoardFormat
        $_.lID1
        $ext
    )
    if ($ext -eq "bmp") {
        $newName = $name -replace "bmp$", "png"
        $stream = [convert]::FromHexString(("42 4D 06 00 03 00 00 00 00 00 36 00 00 00" -replace " "))
        Set-Content -AsByteStream -Value ($stream + $_.ooData) -Path $name
        try {
            ConvertTo-Image -FilePath "$pwd\$name" -OutputPath "$pwd\$newName"
            Remove-Item $name
            while (-not ($file = Get-ChildItem $newName -ea silent)) { Write-Host "." -ForegroundColor Red; Start-Sleep 1 }
        }
        catch {
            $_
        }
    }
    else {
        Set-Content -AsByteStream -Value $_.ooData -Path $name
        while (-not ($file = Get-ChildItem $Name -ea silent)) { Write-Host "." -ForegroundColor Yellow; Start-Sleep 1 }
    }
    $file.LastAccessTime = $access_time
    $file.LastWriteTime = $mod_time
}

By setting the file name to have the last access time at the start, I am able to sort by name in file explorer and find the most recently used items. And by setting the last write time to the modified time, I can sort by it to see items in the order that they were created/modified in.

If I remove the WHERE statement in the sql query, it will also export ALL clips from the datbase (including RTF) and output to filesystem. Definitely best to use these on something fast like NVMe or Ramdisk.