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.
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 imported 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 the number of header lines to skip in import
extra_columns- allows pass constant to import procedure (first parameters)
charset- allows define import files charset (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 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 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. The 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
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.
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 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';
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").click();</script>'); end