Import Commands

Commands

dbr.import.prepare - Calls the preparation report before the import begins
dbr.import.finish - Calls the finish report after the import completes
dbr.import - Calls the import procedure for each imported row
dbr.import.options - Sets 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

Syntax Tips

Where:

• prepare_procedure The name of the stored procedure to be called before the import begins.

• finish_procedure The name of the stored procedure to be called after the import completes. Use the automatic parameter inImportFilename to get the filename.

• import_procedure The import procedure that handles each imported row. myDBR automatically reads the report parameters and matches them to the input file's columns.

• remote_url If the file to be imported is at a URL, provide it here. If not specified, the user is prompted to select a local file.

• worksheet
  The name of the worksheet used for the import

• now
  If specified, no user input is required to initiate the import

• max_size
  The maximum size of the imported file as a number with the unit ("200b", "3Kb", "3Mb", "4Gb")

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 an additional header:

We'll create an import report that prompts the user to select a local file for importing data into 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

Next, create the sp_DBR_import_do report and add it to myDBR. The user needs access rights to this report to run the import. The report should have the same columns (column names and 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 prompts the user to select the file to be imported and calls the sp_DBR_import_do report for each row in the Excel file, passing parameters from that row.

Import From a Remote JSON Source Without User Action

A remote service provides JSON data to be imported. Before the import, the preparation report sp_DBR_import_begin is called. After the import, sp_DBR_import_end is called.

Including 'now' as the last parameter runs the import immediately without user interaction.

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 columns. For a text file with three tab-separated columns (\t is the tab character), the import file and import_procedure look like this:

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

Import with Constants

You can pass constants to the import procedure as the first parameters, defined with the extra_columns option:

select 'dbr.import', 'sp_DBR_import_do';

The following example imports a remote file with constant parameters. No user interaction is required.

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 the Import Is Done

The dbr.import.finish routine can also return JavaScript to the main import routine to trigger additional functionality: for example, 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';

inImportFilename, inImportTotalRows and inImportErrors are automatic parameters filled by myDBR.

create procedure sp_DBR_import_end(
inImportFilename varchar(255),
inImportTotalRows int,
inImportErrors int
)
begin

insert into import_log (filename, total_rows, errors, imported_at)
values (inImportFilename, inImportTotalRows, inImportErrors, now());

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

end