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 a XLSX Excel file (local only).

Commands

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

Syntax

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

Where:

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

Options:

Sample import

We have a table into which we wish to import data from 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 user to select the local file and it will import the data to the table.

create procedure sp_DBR_my_import()
begin

/* 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';

end

The next thing that we need to do, is to create the sp_DBR_import_do-report and add it to the myDBR as 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 same order.

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

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

end

When the sp_DBR_my_import-report is run, myDBR will ask 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' ,'http://myservice.com/index.php?serviceID=5', 'now';

import_procedure

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:

201\tCar\t2011-09-12
202\tSink\t2011-10-22

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 'dbr.report', 'sp_DBR_import_show_result', 'results_div[]';

select 'dummy';
create procedure sp_DBR_import_end()
begin

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

end