Exportizer Pro and Exportizer Enterprise can export data from BLOB columns of database tables to individual files. BLOB columns can store different types of data like large text, images, PDF documents, HTML pages etc.

Export BLOB data to files

Below are examples of exporting the following data from BLOB columns:

Export Text Data from BLOB Columns

Note: In this example, we show how to export text from BLOB columns with text data (CLOB, MEMO etc). But you can apply it to any text compatible non-BLOB columns like char, varchar etc.

Export Steps

  1. Open your database.
  2. Open your source table or SQL query and click Export button .
  3. Choose the Text export format. Exporting Data to Text File
  4. Specify a file name for the target text file.
  5. Proceed to the Field Mappings step.
  6. Write the target file name formulas for each column, which content you need to save to separate files. In Target File column, click '...' button to the right to invoke the Expression Builder. This is the most interesting and the most important step. You will see the default formula: 
    vle_expr(extract_file_path(target_file_name(1)) + 'Name_' + to_string(dataset_row_count(1, 0)) + '.???')

    Here, extract_file_path(target_file_name(1)) extracts the directory path from the target text file name; if you want, replace it with the constant path from your computer (don't forget to add the backslash at the end!), e.g. 'C:\MyData\TextFromDb\'.

    The to_string(dataset_row_count(1, 0)) part of the formula adds the current record number to the end of file name to ensure that the name is unique within the record set.

    Replace the ??? with desired file extension.

    If the source dataset already contains unique file names in some column(s), just remove the default formula and write something like this:

    dataset_field_val(1, 'file_name')

    (replace file_name with the actual name of the field containing the file names).

    Click OK to apply the formula.

  7. [Optional] Click Save button to save the field mappings to file for future use. Or you might want to save all export definitions (including field mappings and other selected options) by choosing Tools | Save Export Definitions....
  8. Click Export and check the results.

Note: Existing files are overwritten without asking.

Export Images from BLOB Columns

Export Steps

  1. Open your database.
  2. Open your source table with the BLOB field and click Export button .
  3. Choose the export format. If you want only export images from the BLOB field, the target format does not matter. Let's choose Text.
  4. Specify a file name for the target text file. This file will not contain any useful information, so you may delete it after all.
  5. Proceed to the Field Mappings step. Delete all field mappings except the mapping for the BLOB field. Empty the target field name in the mapping table.
  6. Write the formula for the target image file name(s). In Target File column, click '...' button to the right to invoke the Expression Builder. As you can see, Exportizer already created a default formula:
    vle_expr(extract_file_path(target_file_name(1)) + 'Name_' + to_string(dataset_row_count(1, 0)) + '.???')

    Here, extract_file_path(target_file_name(1)) extracts the directory path from the target text file name; if you want, replace it with the constant path from your computer (don't forget to add the backslash at the end!).

    The to_string(dataset_row_count(1, 0)) part of the formula adds the current record number to the end of file name to ensure that the name is unique within the record set.

    Replace the ??? with file extension that corresponds to the type of extracted images.

    If the source dataset already contains unique file names in some field, just remove the default formula and write something like this:

    dataset_field_val(1, 'file_name')

    (replace file_name with the actual name of the field containing the file names).

    Click OK to apply the formula.

  7. [Optional] Click Save button to save the field mappings to file for future use. Or you might want to save all export definitions (including field mappings and other selected options) by choosing Tools | Save Export Definitions....
  8. Click Export and check the results.

Note: Existing files are overwritten without asking.

Export PDF Documents from BLOB Columns

Here is an example of how export PDF documents, stored in a BLOB column of the database, to separate files. We'll also create a HTML file with links to those PDF files. And these two different operations will be done in one export step!

Let's suppose we have a database table with the following structure:

idinteger
descriptionvarchar
file_namevarchar
file_contentblob

Initial Conditions

  1. In the example, we will work with HTML export format. We recommend this format as it is good to show the idea. But if your main goal is to export BLOB data to separate files, any target format is suitable.
  2. For simplification, we will create the PDF files in the folder where the main HTML file is created.

Export Steps

  1. Open your database.
  2. Open your source dataset and click Export button .
  3. Choose the HTML export format.
  4. Specify a file name for the target text file.
  5. Turn Use raw data option on. The reason will be explained below.
  6. Proceed to the Field Mappings step. Here is the most important part of the configuring:
    1. Delete all automatically generated field mappings except a mapping for file_name source field.
    2. In the Source Field / Expression column of the remaining mapping, click '...' button to the right to invoke the Expression Builder, and specify the following expression: 
      '<a href="' + dataset_field_val(1, 'file_name') + '">' + dataset_field_val(1, 'description') + '</a>'

      This formula creates an a tag inside the destination .html file; the tag will display the contents from description source field, and will point to the corresponding PDF file from the same folder where the .html file is located.

      The foregoing Use raw data option prevents this generated HTML code from formatting and ensures it is embedded into HTML document as is.

    3. Create a new mapping. For the Source Field / Expression column, choose file_content source field. Leave Target Field column blank.

      Write the formula for the target image file name(s). In Target File column, click '...' button to the right to invoke the Expression Builder. As you can see, Exportizer already created a default formula:

      extract_file_path(target_file_name(1)) + 'Name_' + to_string(dataset_row_count(1, 0)) + '.???'

      Replace it with the following one:

      extract_file_path(target_file_name(1)) + dataset_field_val(1, ''file_name'') + '.pdf'

      If the file_name field contains the file extension already, remove + '.pdf' part.

      The formula explanation:

      extract_file_path(target_file_name(1)) extracts the directory path from the target .html file name;

      dataset_field_val(1, 'file_name') extract the name of the target PDF file from the source dataset.

      Click OK to apply the formula.

    4. [Optional] Click Save button to save the field mappings to file for future use. Or you might want to save all export definitions (field mappings and other selected options) by choosing Tools | Save Export Definitions....
  7. Click Export and check the results.

Note: Existing files are overwritten without asking.

See also

Field Mappings File

Export Definitions File

Practical Cases