Dynamic Data Import into MetaMiner Database¶
This feature facilitates users to create or update their own custom tables in MetaMiner Database (MMDB).
It allows you to extend the MMTE capabilities for BI data analysis and reporting.
Although you can always add custom tables by using
pgAdmin, and other PostgreSQL tools, the feature described in this article does not require any special knowledge and is safe.
Custom tables are created/updated from source
CSV (Comma Separated Value) or
Microsoft Excel files you make in any office software.
Source File Format¶
The following file formats are supported:
- Comma Separated Value: plain text file format, extension
- Microsoft Excel Open XML Spreadsheet: extension
- Microsoft Excel version 97-2007: legacy format, extension
Adding a Table¶
How to dynamically add your table to MMDB:
Create a file with the data for your new table in one of the formats mentioned above.
The filename will be used by MMTE for generating the table name.
The value titles from the first (heading) row will be used for column names.
Example: create a list of users as a
ID,Name,Seq,Value 1,Helen,349,"some text" 2,Clarice,943,"more value" 3,John,4111,"even more"
Log in to the host where MMTE Server is running.
Navigate to the following directory:
<METAMINER INSTALLATION DIRECTORY>\server\dynamic_data_import
Excelfile to the above folder.
MMTE polls this directory every 30 minutes. When it detects a new file there, it creates a table of the format described in this file in MMDB, if such table does not exist. Or updates it, if it exists.
Created Table Parameters¶
The table is always created in the
public schema of the
The table name is generated from your source file name by adding the
z_ prefix and converting all letters to lower case. In our case, for the
MyTestTable.csv file, the table name is
z_mytesttable. If the filename contains any characters illegal for table names (e. g. spaces) the table will not be created.
The column names are generated from the values of the first line in your file. The data type of all columns is always
text. All column values are not nullable with no default value:
metaminer=# \d public.z_mytesttable Table "public.z_mytesttable" Column | Type | Collation | Nullable | Default --------+------+-----------+----------+--------- ID | text | | | Name | text | | | Seq | text | | | Value | text | | | metaminer=#
The values of the second and consequent lines of the source file are converted into table rows:
metaminer=# SELECT * FROM public.z_mytesttable; ID | Name | Seq | Value ----+---------+--------+------------ 1 | Helen | 349 | some text 2 | Clarice | 943 | more value 3 | John | 4111 | even more (3 rows) metaminer=#
If there are any inconsistencies in the source file content (illegal characters, extra or missing values, etc), no content will be inserted, the table will be created empty.
Using the Created Table¶
As soon as MMTE has imported the source file, you can use its content in your SQL scripts in Meta Query:
You can update the content of the existing custom table by uploading a file with the same name but a different content.
In this case, the table will be
truncated with all the existing content being removed and the new content will be
Dropping the tables
Excel file from the Dynamic Data Import directory does not drop your custom table.
To delete it, use the following command in
pgAdmin, or any other PostgreSQL management software you prefer, for example:
metaminer=# DROP TABLE public.z_mytesttable