Import commands

myDBR allows data to be imported from a local file or from a remote file. The import process reads the file and processes it row by row with a given import report. You can make preparations before and after the import process. The imported file can be a tab delimited file, a CSV file, a JSON file or an XLSX Excel file (local only).


dbr.import.prepare - Before the import is done, this preparation report is run
dbr.import.finish - After the import is finished, this report is run
dbr.import - This procedure is called for each import row
dbr.import.options - Set import options


select 'dbr.import.prepare', prepare_procedure
select 'dbr.import.finish', finish_procedure
select 'dbr.import', import_procedure [, remote_url [, 'now']]
select 'dbr.import.options', 'option', value


The name of the stored procedure to be called before the import is initiated
The name of the stored procedure to be called after the import is done
An import procedure which handles the import rows. myDBR will automatically read report parameters and match the parameters to input file's columns
If the file to be imported is a URL pass it here. If not specified a local file is asked from the user
If specified no user input is required to initiate the import


Sample import

We have a table into which we wish to import data from an Excel spreadsheet. The table definition looks like this:

create table parts (
id int, 
name varchar(30), 
creation_date date,
primary key(id)

The Excel sheet has the same columns with additional header:

We'll create an import report which will ask the user to select the local file and it will import the data to the table.

create procedure sp_DBR_my_import()

/* Skip one header line from the Excel file */
select 'dbr.import.options', 'skip_header', 1;
/* Tell that we are importing an Excel file */
select 'dbr.import.options', 'format', 'xlsx';

/* Define the import report which will do the actual import */
select 'dbr.import', 'sp_DBR_import_do';


The next thing that we need to do, is to create the sp_DBR_import_do-report and add it to the myDBR as a report. User needs to have access rights to the report in order to run the import. The report has the same columns (column names/parameter names do not matter) in the same order.

create procedure sp_DBR_import_do(
inID int,
inName varchar(30),
inDate date

insert into (id, name, creation_date)
values ( inID, inName, inDate );


When the sp_DBR_my_import-report is run, myDBR will ask the user the file to be imported and then call the sp_DBR_import_do report against each of the rows in the Excel-file with parameters from the Excel file's row.

Import from a remote JSON source without user action

We have a remote sevice serving JSON which we wish to import. Before the import, a preparation report sp_DBR_import_begin is called. After the import is finished, the sp_DBR_import_end-report is called.

If we include 'now' as a last parameter, no user interaction is required to invoke the import and the import is run as soon as the report is run.

select 'dbr.import.prepare', 'sp_DBR_import_begin';
select 'dbr.import.finish', 'sp_DBR_import_end';
select 'dbr.import.options', 'format', 'json';

select 'dbr.import', 'sp_DBR_import_do' ,'', 'now';


The import procedure should have the same number of parameters as the input file has tab-separated columns. For example, if we have a text file consisting of three columns (\t is a tab character in the file), we would have an import file and an import_procedure that would look like this:


Import with constants

You can pass constants to the import procedure. The constants are passed as first parameters and are defined with the extra_columns-option

select 'dbr.import', 'sp_DBR_import_do';

We have a remote file which we wish to import. Before the import, a preparation report is called. After the import is finished, a report is called. No user interaction is required to invoke the import

select 'dbr.import.options', 'skip_header', 1; /* Treat first line as header and skip it */
select 'dbr.import.options', 'extra_columns', 10; /* Pass constant 10 as first parameter to 'sp_DBR_import_do', rest of the parameters come from import file */
select 'dbr.import', 'sp_DBR_import_do';

Additional UI functionality after import is done

The dbr.import.finish-routine can also return JavaScript to the main import routine to trigger additional functionality. This can for example be utilized to show the imported data.

select 'dbr.import.finish', 'sp_DBR_import_end';
select 'dbr.import', 'sp_DBR_import_do';

select 'dbr.button', 'Show import result', 'invisible import_result';
select '', 'sp_DBR_import_show_result', 'results_div[]';

select 'dummy';
create procedure sp_DBR_import_end()

-- Click the 'Show import result' button once import is done
select concat('dbr.html:Import done<script>$(".import_result")[0].click();</script>');