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).
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
skip_header- defines number of header lines to skip in import
extra_columns- allows pass constant to import procedure (first parameters)
charset- allows define import files chartset (will be converted to UTF-8)
format 'tab' | 'json' | 'csv' | 'xlsx'- import format file type: 'tab' (tab delimited) or 'json' (JSON), 'xlsx' (Excel)
title- title text for the import dialog
button.text- Import-button text
csv_delimiter- CSV delimiter for the import, default = ,
csv_enclosure- CSV enclosure for the import, default = "
line_ending 'cr'- Line ending for the file default = chr(10)
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
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.
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';
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:
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';
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").click();</script>'); end