Falcon Space. Import-export

The component allows you to configure import/export for almost any data processed in the system, for example, for a product catalog. 

how to import and export data via Excel

how does the component work from the user's point of view:

  1. Uploading the necessary data to Excel (or only the file template is uploaded)
  2. Editing them in Excel.
  3. Importing data back. The file must strictly follow the initial format. 

Note: 

you can only use the xlsx format (not xls). 

How to configure the import component via Excel

To configure the component, follow these steps: 

1. Insert a snippet on the page

< div class= "as-exportImport" data-code="trace" data-itemid= "123" data-title= "Title" data-desc= "Subtitle" data-exportbtn= "Export" data-exporttemplatebtn= "Template" data-importbtn= "Import" data-disableimport= "1" >
</div>

Parameters: 

  • code-sets the element code (we will configure stored procedures for it)
  • itemID - some external ID (for example, a folder category). 
  • title - the title of the component
  • desc-description-hint
  • exportBtn-Export button
  • exportTemplateBtn-Export Excel template button
  • importBtn-Import button
  • disableImport - if 1, the Template and Import buttons are hidden (it is also mandatory to prohibit import in the settings CP settings). 

2. In the Export section/Import creating a new element with the code code (the name field is informative, not used anywhere). 

3. Implementing the procedure settings

4. Implementing the export procedure export

5. Implementing the import procedure import

Export procedure exportImport_{code}_settings

CREATE PROCEDURE [dbo].[exportImport_example_settings]
	@username nvarchar(64),
	@itemID nvarchar(32),
	@parameters ExtendedDictionaryParameter READONLY
AS
BEGIN
	-- SELECT 1
	select '' Msg, 1 Result,
		'List name' ExcelListName,
		'{code}-{date}-{g}.xlsx' ExportFileName,    -- use {g} {code} {date}
		'' ImportDirectory,  -- "/uploads/importExcel/"
		'{title} ({name})' HeaderColName,  -- col header in export excel
		0 DisableImport,
		1 LogImport,   -- log to table as_exportImportLog, as_exportImportLogItems
	   'admin'	Roles


	-- SELECT 2 Cols
	select * from (
	select 'ID' name, 'Number' title, '1' value, 20 width, 1 ord
	union
	select 'Name' name, 'Name' title, '2' value, 50 width, 2 ord
	union
	select 'Desc' name, 'Description' title, '3' value, 100 width, 3 ord
	) t1
	order by ord

END

At the input: 

  • @username-current user
  • @itemID - itemID from the snippet
  • @parameters-auxiliary parameters are passed here (for example, userGuid, langID). 

output export/import settings: 

SELECT 1 Key settings:

  • Msg, Result - result of the operation
  • ExcelListName-sheet name in Excel
  • ExportFileName-name of the exported file (you can use {g} {code variables} {date})
  • ImportDirectory - where import files will be stored when loading
  • HeaderColName-column name (you can use {title} {name} variables)
  • DisableImport-disables importing by this code (i.e. you can configure, for example, what some users can do and others can't)
  • LogImport-log or not the result of string import operations to the database. 
  • Roles-comma-separated list of roles that have access to the component

SELECT 2 Excel column settings

  • name-column code for identification in the import procedure
  • title-column name
  • value-default value-used as an example upload in the Excel1 template
  • width-column width in excel
  • ord-the ordinal number of the column

Export procedure exportImport_{code}_export

CREATE procedure [dbo].[exportImport_trace_export]
 @page int = 1,         --at the enter page and pageSize, what to limit
 @pageSize int = 5,      --output data set
 @username nvarchar(128)=''
as
begin
    select id ID, header Name, text [Desc] from as_trace
    order by  id desc
    OFFSET @PageSize * (@Page - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY;
end

The output model matches the settings of parameters in the Type of export.

Same fields will be at Excel file.

The procedure of import exportImport_{code}_import

CREATE procedure [dbo].[exportImport_trace_import]
  @ID int,
  @Name nvarchar(512),
  @Desc nvarchar(max),
  @username nvarchar(128)=''
as
begin
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
    BEGIN TRAN
        if(@id>0)begin
        update as_trace set header = @Name, text = @Desc where id = @ID
        select 'Update ID=' + cast(@id as nvarchar) Msg, 1 Result
        end
        if(@id<0)begin
        delete from as_trace where id = -@ID
        select 'Delete ID=' + cast(@id as nvarchar) Msg, 1 Result
        end
        if(@id=0)begin
        insert into as_trace (header, text) values(@Name, @Desc)
        select 'Add ID=' + cast(SCOPE_IDENTITY() as nvarchar) Msg, 1 Result
        end

    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK
    DECLARE @Err varchar(512)
    SET @Err= ERROR_MESSAGE()
    RAISERROR (@Err, 16, 1)
    select @Err Msg, 0 Result
END CATCH

end

Input - a single row model from excel-is set based on parameters of the type

The agreement on operations:

  • ID = 0 add,
  • ID > 0 updating the ID record
  • ID < 0 deleting a record-ID

Output model ResultModel(Msg, Result) 

Information about the result of the operation is output to the import log, and can also be saved in the log tables.

Update 06.06.2020. Added the @username parameter to the export and import procedures. 

Falcon Space is a functional web development platform on a narrow stack MS SQL/Bootstrap. Falcon Space Gettting started
Note