Improved debugging in Access OLE Export / SQL Blob Export

Sometimes it happens that you may have some items that Access OLE Export or SQL Blob Export is unable to export correctly.  This can be due to many reasons e.g.

  • the binary data has been compressed by the uploading application
  • the binary data has been encrypted by the uploading application
  • the uploading application has appended additional header bytes to the data
  • an unsupported OLE server was used to upload the file

To troubleshoot these issues, we would ask the user to send us the raw data exactly as stored in the database.  This usually involves using SQL Image Viewer to perform the extraction.

We’ve now added the option to extract the raw data using Access OLE Export / SQL Blob Export.  You can do this by selecting the debugging – create bin files for analysis item.

Now when you have problems exporting your files, selecting this option will cause a raw copy of the data to be exported together, with the .debug.bin extension.  You can then send us these .debug.bin files for further analysis.

Using UTF8Tool

UTF8Tool is an application to encode files to the UTF-8  character set and vice-versa.  You can download the application here.  This application was originally created for a user who needed to decode/encode files stored in a text column in his database.

To use the application, select the option to either decode or encode the files.  Then enter a search pattern in the Input folder option to locate your files.  If we wanted to process only pdf files, we could enter something like this:

Next, enter the folder to store the processed files in, in the Output folder option e.g.

Once you’ve entered the values, click on the Start button to process your files.  Note that any existing files in the output folder will be overwritten.

Extracting binary data from a nvarchar(max) column!

Recently we had a user that needed to extract PDF files stored in a SQL Server nvarchar(max) column.  Now, you wouldn’t normally be able to store binary files correctly in a text column, but the application they used (Deacom) first encoded the PDF file to UTF-8 text, then stored the UTF-8 text into the text column.

While SQL Image Viewer (and SQL Blob Export/Access OLE Export) could extract the UTF-8 encoded data from the table and store it in a file, the resulting PDF file cannot be opened because the data contained therein is invalid.  So we had to provide a small utility to convert the UTF-8 encoded file back to its original contents.  You can read more about the utility here.

After converting the PDF files, the user then compressed the PDF files and now wanted to upload the PDF files back into the database.  They used SQL File Import to perform the upload, but first the PDF files had be to UTF-8 encoded again, otherwise the application (Deacom) could not open the PDF files.  That can also be one using the above utility.

Finally, we had one last issue where only 8 Kb of the encoded PDF file was being uploaded.  The cause was traced to the use of the MDAC/ODBC driver to connect to SQL Server.

In our database products, when you connect to a SQL Server instance, the default is to let the application select the best available drivers.

By design, our products will use the SQL Server Native Client drivers if available, and only use MDAC/ODBC drivers when the Native Client drivers are not available.  However, MDAC/ODBC drivers limit text fields to only 8000 bytes.

When the user attempted to use the Native Client driver, the connection failed.

Installing and using the Native Client drivers then resolved the connection and upload issue.

Using Access OLE Import

Access OLE Import is a Windows application that uploads files into OLE Object-type fields in your databases.  Most of the time, we are uploading into Access databases, but it can also be  client server databases like SQL Server, PostgreSQL, MariaDB/MySQL because Access is popularly used as a front-end to these databases.

Backup, backup, backup

The first step is the most important – back up your database first.  Access OLE Import makes changes to your tables, and having a backup lets you revert to a working database if anything goes wrong.

Preparing the data file

The next step is to prepare a data file.  This is an Excel worksheet containing the data to upload.  If you only want to insert files into a table, your data file would contain a single column, where the column header defines the field name to upload into and the filecontent identifier so that Access OLE Import will upload the contents of the listed files.  E.g.

Here, we want to upload the contents of the listed files into the document field.

If we want to update existing rows, we will need to provide one or more key values e.g.

Here, we are providing the definition that documentID is an integer-type key value.

Note that you can also use Libre Office or Open Office to create the data file.  You just need to save the spreadsheet in the Excel format (xlsx or xls).

Run the Upload Wizard

Probably the easiest step.  Connect to your database, enter the name of the table you want to upload into, select the data file, upload type, object type, and you’re done.

See also:

Introducing Access OLE Import

Access OLE Import is our new product that helps you upload files into OLE Object-type fields in Access and most major database engines (SQL Server, PostgreSQL, MariaDB/MySQL etc), for use in the following scenarios:

  • you are migrating data to a database that uses OLE Object fields
    E.g. you have a new customer that require their data to be migrated to OLE Object fields in your database.  Access OLE Import can upload the files into those fields easily.
  • you need to modify the existing data then upload the changes back into the tables
    E.g. you want to extract images from your database, then resize/add watermarks, then upload these updated images.  Use SQL Image Viewer to extract the images, modify them, then use Access OLE Import to upload the files.
  • you need to update file links in the OLE Object fields
    E.g. you have existing file links that are no longer valid, and want to update those file links.  Access OLE Import can upload file links as well as embedded files.
  • you want to convert embedded content to file links in OLE Object fields
    Use Access OLE Import to easily upload file links to replace the existing embedded content.

You can read more about Access OLE Import on this page, or download a 14-day trial.

See also:

Locking data sets in SQL Image Viewer

We recently fixed a bug in SQL Image Viewer where locking data sets was not working and resulted in errors.  It would seem that not many users are aware of this useful feature, so here’s more details on what locking does.

When you run a query and want to keep the results but need to run another query, your options in most SQL tools is to set up another connection and run the other query.  Or run another instance of the application to be able to run the other query.

In SQL Image Viewer, you can lock the current data set so that it is not replaced by the results of the new query.  Say we want to keep this data set containing 99 rows in SQL Image Viewer:

Click on the Lock data set button to keep that data set.

Once we do that, the page caption changes to indicate the data set has been locked.

Now when we run our other query, its results is displayed on a different tab.

We can then easily switch between the 2 data sets to view/compare the results.  You can lock as many data sets as you require, subject to having enough storage on your computer.

To unlock a data set, select the data set, then click on the Unlock data set button button.  The data set will be immediately released and closed.

So basically locking allows you to persist data sets across queries, instead of having to create additional sessions.

Copying files from different folders to one folder easily

There may be instances where you want to copy files from different folders to another folder.  For e.g. say we want to copy 3 files from 3 different folders to another folder:

Using Windows Explorer, we would need to open each folder in turn and copy the file to our target folder.  If the files were large, we would need to wait for each copy to complete before proceeding to the next file.

In Easy Explorer, we can use the drop stacks feature to copy all 3 files in one go.

First, we need to display the tools panel by selecting the Windows > Display tools panel item from the main menu, or press CONTROL-O.

The tools panel is displayed on the bottom.  Click on the Drop stack tab to open it.

Now we can drag and drop each file we want to copy into the drop stack area.

Once we have all the files we want to copy in the drop stack area, we can open the folder we want to copy to, and simply drag and drop all the files from the drop stack to that folder.  All the files will be copied in a single process.

If you want to copy the set of files to multiple folders, hold down the CONTROL key while dragging and dropping the file(s).  This ensures that the files in the drop stack are not removed once the copy process has completed.  This makes it easy to copy the same set of files (from different folders) to multiple locations.

Note that you can drag files to the drop stack from Windows Explorer, and also copy files from the drop stack to Windows Explorer.

Extract PDF tables to Excel

So you need to extract data from tables in PDF files to Excel.  The first thing you need to try is to copy the data from the PDF file in your PDF reader and paste it into Excel.  It might just work.   Not if you’re using Acrobat Reader though – it’ll just appear as lines of text.  Try Foxit PDF Reader – if your table looks something like this:

copying and pasting should do the trick.  However, if your table has empty cells like this:

or is free form like this:

you’ll still end up with wrongly formatted text.

Your next option is to explore the online extraction services like PDFTables, which is quite good at extracting tables.  However, you may not be comfortable submitting your sensitive files to a third-party.  If so, consider using PdfToXls.

It’s a FREE Windows application that lets you extract data from PDF tables into Excel.  It runs on your machine, so your files stay on YOUR computer.  You define the table and column locations, and the application does the rest.

If your table layout is complex, PdfToXls provides you the tools to adjust the data easily prior to exporting it.

Download your FREE copy now.  Visit our web site for more information.  We also have a couple of videos showing the product in action.

Indexing DICOM images on your desktop

You have a collection of DICOM images from various sources, and you want to be able to search for images matching specific values in their tags.  And you don’t want to have to install an entire Apache/Cloudera Hadoop server.

Bottom line, you just want an easy way to search your collection of DICOM images.

If that’s the case, then give DICOM Search a try.  It is a Windows application (runs on Linux too via Wine). that extracts the value of each and every tag from your images and stores them into a relational database.  You then use regular SQL syntax to run your searches.

There is also a simplified syntax for those of us that don’t know SQL, where you can run searches like this easily:

It’s that simple.  Your results are displayed in a table e.g.

where you can then open the image using your favorite DICOM viewer

or use the internal viewer which displays your search results in the familiar patient/study/series hierarchy.

Imagine the possibilities when you can now search for any image in your collection using the values in the tags, on your desktop computer.

Learn more about the product on our website, or watch the introductory video here.

Download a 14-day trial of DICOM Search now and start searching your DICOM images efficiently and effortlessly to make new discoveries.

Creating an index of the DICOM image tags

A DICOM image can contain anywhere from a hundred to a thousand tags, sometimes even tens of thousands of tags.

It’s usually not an issue to search for an image matching a specific tag value on the equipment used to capture the images using the manufacturer’s software.  However, if you had a collection of images taken from different equipment, you then had to organize your images somehow in order to keep track of each image’s attributes.

Not any more.  DICOM Search is a Windows application that stores the values of each and every tag of a DICOM image into a database.  You can then search that database for images that match one or more tag values easily using SQL queries (or a simplified SQL syntax for beginners).

Basically, you have the power of a relational database engine to search your DICOM images based on their tag values.

Let’s take a closer look at DICOM Search.

Storing the image tag values

Your DICOM images are organized into libraries.  Each library can contain images from different sources, or you could just lump all images into a single library.

You can populate each library with images stored on your folders

or you can connect to a SQL database to retrieve the images to populate your library.

Once your images have been retrieved from your folders or from a database, each tag is then processed and stored in a database table.

Searching for images

Say you want to search for MRI images of the knee where the patient weight is 75 or more.  This is how you would enter the search criteria in DICOM Search.

And this is how DICOM Search displays the search results.

Plain and simple.  Using tags, you can use plain words to refer to a tag, instead of the usual group and element numbers.  Of course, you can use the group/element combination if you so prefer e.g. to retrieve the same results above, you would enter:

Given that you have the power of a relational database engine under the hood, you can write some pretty advanced queries to retrieve your images.

Working with the search results

Once you have the images from your search results, here’s what you can do with them in DICOM Search:

  • copy the images to another folder, making it easier to work with just those images of interest
  • create a HTML slideshow containing those images
  • export the images and all the tag values in the search results into an Excel spreadsheet
  • open the images using your preferred DICOM viewer for further analysis
  • open the images using the internal viewer for further analysis

The above just scratches the surface on what DICOM Search can do.  Learn more about the product on our website, or watch the introductory video here.

Download a 14-day trial of DICOM Search now and start searching your DICOM images efficiently and effortlessly to make new discoveries.