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

The next thing that we need to do is to create the sp_DBR_import_do report and add it to myDBR as a report. The user needs to have access rights to the report in order to run the import. The report should have 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 prompt the user to select the file to be imported and then call the sp_DBR_import_do report for each row in the Excel file, passing parameters from the Excel file's row.

We have a remote service 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 runs as soon as the report is executed.

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

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

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

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

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