Skip to content

Dynamic Data Import

Load your own CSV or Excel files into the MetaMiner Database (MMDB) so you can join corporate data, such as an employee list or cost centres, to MetaMiner metadata in your queries and reports.

How it works

The MetaMiner Server (MMS) checks a drop folder on a schedule. For each file it finds, it creates a table named z_<file name> in the MMDB and loads the rows. When you replace a file with a newer version, MMS drops the table and loads it again.

Item Behaviour
Drop folder <install folder>\server\dynamic_data_import (created by MMS if missing)
File types .csv, .xlsx, .xls
Check interval Every 30 minutes, on the hour and at half past; set data.import.period (minutes) to change it
Table name z_ + file name without extension, for example employees.xlsx -> z_employees
Columns One per header cell of the first row, lower case, all of type text
Excel workbooks Only the first sheet is loaded
CSV format UTF-8, comma-separated; set csv.separator.europe.prefs=true for semicolon-separated files
Load status Table dynamic_data_import_control: one row per file with last_status (0 loaded, 1 in progress, 2 error) and last_notes

Set data.import.period and csv.separator.europe.prefs in server\MetaMinerServer.properties, then restart the metaminer service. See Server Properties.

Before you begin

  • You need write access to the MetaMiner Server folder.
  • Give each file a name that is a valid table name: letters, digits and underscores only, no spaces.
  • Put the column names in the first row. Use distinct, non-empty names.

Steps

  1. Prepare the file. Example Excel file with employee data:

    Image of an Excel file with a header row of employee columns, ready to import

    ../images/tips/dynamic-import-excel-file.png

    Legacy image for reference: ../images/excel-to-be-imported.png

  2. Copy the file into <install folder>\server\dynamic_data_import.

    Image of the server\dynamic_data_import folder in Windows Explorer with the file copied in

    ../images/tips/dynamic-import-folder.png

    Legacy image for reference: ../images/dynamic-data-import-folder.png

  3. Wait for the next check (up to 30 minutes by default).

  4. Check the load status right after the check:

    Load status of imported files
    SELECT filename, updated, last_status, last_notes
FROM dynamic_data_import_control
ORDER BY updated DESC;

  5. Query the new table and join it to MetaMiner tables, for example:

    Join imported employees to BusinessObjects users
    SELECT u.cmsid, u.name, u.fullname, e.*
FROM users u
JOIN z_employees e ON lower(e.email) = lower(u.emailaddress);

    Adjust the column names to your file.

Verify

  • SELECT count(*) FROM z_<file name> returns the number of data rows in the file.
  • dynamic_data_import_control shows your file, with a file_timestamp that matches the file's modified time.

You can query the table from Meta Query, the Database Console and direct connections as soon as it is loaded.

Image of the MMC Meta Query tab with a query on an imported z_ table and its results

../images/tips/dynamic-import-meta-query.png

Legacy image for reference: ../images/dynamically-imported-table-in-mmc.png

Remove an imported table

Deleting the file from the drop folder does not drop the table. Drop it yourself with the MMDB owner account, for example in the Database Console:

Drop an imported table
DROP TABLE z_employees;
DELETE FROM dynamic_data_import_control WHERE filename = 'employees';

Troubleshooting

The table was not created

Read last_notes in dynamic_data_import_control and server.log for the error. Common causes: a file name that is not a valid table name, empty or duplicate header cells, or a file still open in Excel when MMS read it.

My updated file was not reloaded

MMS reloads a file only when its modified (or created) time is newer than the time stored for the last load. Save the file again and wait for the next check. To force a reload, delete the file's row from dynamic_data_import_control and drop its z_ table.

Next steps