¶ Overview
The File import module is used to import data from a known format excel file
¶ How to Use
You can access the File Import module from the User Profile menu
After clicking on the “Import File” menu option the Import file Dialog will be presented:
Select a pre-configured File Import module from the dropdown list
Click on the Upload file icon. This will open up the File Dialog on your computer
Once the file has been selected, click on the “Confirm” button to execute the import script for the selected module.
The import module will print any information or results in a result dialog:
¶ How to configure an import module
¶ Principles
The core functionality of the File import module relies on the following:
- An Entry in the qx_ImportModule Table
- An Excel file that contains the source data
- A Stored-procedure to process the Data and present results to the user
The Import module will import the data from the Excel file into import tables in the database, following which ith will execute a stored procedure. For examples of how this can be implemented see the sections below.
¶ Example Module
An entry in the qx_ImportModule Table needs the following:
- a Name used in the list of modules.
- a Description for the import module
- The name of the stored procedure to execute
¶ Example import file
Consider the following example excel file:
In this example file, there are two sheets:
When the module executes it will create and import this data into tables in the database. it uses the name of the import module and the name of the sheets in the spreadsheet.
qx_Import[importmodule name]_[sheetname]
The resulting tables for the Excel file above will be:
The names of the columns will match the names of the first row:
The types of the columns will automatically match those of the Excel file. Importing Files without column headers is not supported.
¶ Example script
CREATE PROCEDURE [dbo].[qx_Import_PoDataTest_Process]
AS
BEGIN
SET NOCOUNT ON
DECLARE @MESSAGE VARCHAR(500)
DECLARE @SHEET1COUNT INT = (SELECT COUNT(*) FROM qx_ImportPoDataTest_Sheet1)
DECLARE @SHEET2COUNT INT = (SELECT COUNT(*) FROM qx_ImportPoDataTest_Sheet2)
SELECT '<strong>IMPORT SUMMARY</strong><br><br>' +
'<strong>' + CAST(@SHEET1COUNT as varchar) + ' records</strong> imported from SHEET1 <br>' +
'<strong>' + CAST(@SHEET2COUNT as varchar) + ' records</strong> imported from SHEET2 <br><br>' +
'Processing Complete!'
END
GOThe above-mentioned cold block can be seen to perform some basic steps. The exact data validation/manipulation and feedback is up to the author. In the example above it simply collects the count of records from the two import tables, and presents the results back in an HTML-formatted response to the user:
Any result value selected will be presented as feedback back to the user.