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).

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 the 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 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()
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 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
)
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 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' ,'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