Import rules

In the import definition, Transfers > Data > Define File Structure > Import, the Import Rules tab enables the application of transformations on imported data.

Typical transformations include, but are not limited to:

  • Removing rows not useful for data transfer

  • Altering field content based on specific information

  • Adding content based on other data

Here are some key points about import rules:

  • Rules are integral to the import structure definition and are included in backups/restores.

  • They are written in pure SQL.

  • Applicable to any import method (files/HUB/OData).

  • Reference to the imported data table is made using the variable $import$.

  • Multiple transformations can be made within the same script.

  • For security, rules run under a separate context from the application, preventing alterations to other tables/structures.

  • Accessible to customers/consultants, allowing modification without direct server access.

  • Rules are applied to row data before mapping and aggregation.

 Note:   For the rule to take effect, ensure that you select the Enable Import Rules option.

Import rule samples

Below are some sample import rules.

Note the following:

  • Import rules may span multiple lines, with each transformation applied sequentially.

  • Import rules are processed before applying mapping tables. They use the information from the file/ hub before any conversion.

Change account code

For all accounts (assumed to have 6 digits) starting with '70' and containing intercompany details, replace the original account by taking the first 3 digits and appending '500' as the last 3 digits.

Copy 
update $import$  set Account=left(Account, 3)+'500' where  ISNULL(Partner, '') <> ''  AND left(Account, 2) = '70'

Change account code if the amount is negative

Copy 
update $import$  set Account='N'+Account where  left(Account, 2) = '55' AND NetAmount < 0​

Clear the content of one field

Copy 
Update $import$ set Partner = NULL where Partner = '#'

Load Dimension details and closing data in one go

Copy 
-- Copy all rows to temporary table
Select * into #tmp_no_dim from $import$

-- Update dimgroupcode with DIM (dim is mapped to the costcenter details) in original rows
Update $import$ set DimensionGroup='DIM'

-- Remove all balance sheet accounts from import table because BS does not have dimensions
-- delete from $import$ where left (Account,1) in ('0','1','2','6')
delete from $import$ where DimensionDetail1=0

 -- remove all dimension information from temp
Update #tmp_no_dim set DimensionDetail1 = null

-- copy tmp table to import table
Insert into $import$ select * from #tmp_no_dim

-- Drop temp tables
Drop table #tmp_no_dim​

Intercompany mapping: when the GL is the same but some accounts are different entities

Update an account code for a specific account/company combination.

Copy 
update $import$ set Partner='E20' where account='10080' and company='E40'
update $import$ set Partner='E30' where account='210_E20' and company='E20'
update $import$ set Partner='E40' where account='1550_E20' and company='E20'
update $import$ set Partner='E20' where account='200_E10' and company='E10'
update $import$ set Partner='E20' where account='1550_E10' and company='E10'
update $import$ set Partner='E20' where account='1590_E10' and company='E10'
update $import$ set Partner='E20' where account='1591_E10' and company='E10'
update $import$ set Partner='E20' where account='1592_E10' and company='E10'​

Split data into different accounts based on allocation key

Copy 
/**
SAMPLE VALUES FROM IMPORT

...  ACCOUNT   CUSTOMTEXT2   NETAMOUNT ...

...  '6061100', 'P200', 1000.00
...  '6061105', 'P200', 9000.00

**/


-- Creating mapping table with allocation keys
DECLARE @MappingUnit1 table( Account nvarchar(20), CustomText2 nvarchar(20),Suffix nvarchar(4), Repartition decimal(6,2) )

-- Inserting the keys per account + Custom text 2
Insert into @MappingUnit1(Account, CustomText2, Suffix, Repartition) VALUES ('6061100', 'P200', 'Adm',  0.4)
Insert into @MappingUnit1(Account, CustomText2, Suffix, Repartition) VALUES ('6061100', 'P200', 'CopI', 0.6)

/* ...   */

-- Keeping a copy of original account as CustomText1
Update $import$ Set CustomText1 = Account

-- Create working table
DECLARE @TempImport table( 
 RowNr int
, RowData nvarchar(max)
, ConsoID int
, Company nvarchar(20)
, Account nvarchar(20)

, NetAmount decimal(24,6) 
, CustomText1 nvarchar(20)
, CustomText2 nvarchar(20)
)

-- inserting allocated values from import 
Insert into @TempImport(RowNr, RowData, ConsoID, Company, Account, NetAmount, CustomText1, CustomText2)
Select
    a.RowNr
    , a.RowData
    , a.ConsoID
    , a.Company
    , ( a.Account + b.Suffix ) as Account
    , (a.NetAmount * b.Repartition) as NetAmount
    , a.account as CustomText1
    , a.CustomText2

From $import$ a
    inner join @MappingUnit1 b on (b.Account = a.Account and b.CustomText2 = a.CustomText2)


-- Deleting rows to split
DELETE From $import$ 
    where Account in (Select Account from @MappingUnit1)

-- inserting splitted rows
INSERT INTO $import$(RowNr, RowData, ConsoID, Company, Account, NetAmount, CustomText1, CustomText2)
    Select RowNr, RowData, ConsoID, Company, Account, NetAmount, CustomText1, CustomText2 
        From @TempImport



/**
SAMPLE OUTPUT VALUES FROM RULE

...  ACCOUNT   CUSTOMTEXT1 CUSTOMTEXT2   NETAMOUNT ...

...  '6061100Adm', '6061100' , 'P200',  400.00
...  '6061100CopI','6061100' , 'P200',  600.00
...  '6061105',    '6061105' , 'P200', 9000.00

**/

Split field and use segments in multiple other fields

 Note:  In this use case (from EVS), the challenge was receiving multiple dimensions within a single field. These dimensions are represented by concatenated codes separated by a specific delimiter, which in this case is the 'pipe' (|).

Copy 
/*
Let's assume that the concatenated information was loaded on CustomText1 which is a nvarchar(max), meaning up to 8000 positions

Information is like 'ABC|123|---||DEF'
(some position might be empty)

Let's assume that position 2 should go to dimension 1 and position 5 should go to dimension 2
*/

-- Copy original CustomText1 in CustomText2 to keep the source info untouched
Update $import$
   Set CustomText2 = CustomText1

-- We then need to ignore position 1
Update $import$
   Set CustomText2 = RIGHT(CustomText2,LEN(CustomText2)-CHARINDEX('|',CustomText2)) 

-- We then need to use position 2 for Dimension 1
Update $import$
   Set DimensionDetail1 = LEFT(CustomText2,CHARINDEX('|',CustomText2)-1)
   , CustomText2 = RIGHT(CustomText2,LEN(CustomText2)-CHARINDEX('|',CustomText2))

-- We then need to ignore position 3
Update $import$
   Set CustomText2 = RIGHT(CustomText2,LEN(CustomText2)-CHARINDEX('|',CustomText2))
 
-- We then need to ignore position 4
Update $import$
   Set CustomText2 = RIGHT(CustomText2,LEN(CustomText2)-CHARINDEX('|',CustomText2))   

-- We then need to use position 5 for Dimension 2
Update $import$
   Set DimensionDetail2 = LEFT(CustomText2,CHARINDEX('|',CustomText2)-1)
   , CustomText2 = RIGHT(CustomText2,LEN(CustomText2)-CHARINDEX('|',CustomText2))

Import 12 columns of closing amounts (forecast, budget, etc.)

With the Custom Amount fields, you can import multiple columns from a file using import rules.

Here is an example:

  • NetAmount would be based on Column N

  • All CustomAmount… fields filled in with columns C to N

    We could create a rule like the following to handle the 12 columns:

    Copy 
    -- Copy all rows to temporary table
    Select * into #tmp_budget from $import$

    -- Keep Period information to be used later in the rule
    DECLARE @Period nvarchar(12)
    DECLARE @Year nvarchar(4)
    DECLARE @PeriodSuffix nvarchar(6)

    Select TOP 1 @Period = Period
       , @Year = LEFT(Period, 4)
       , @PeriodSuffix = RIGHT(Period, 6) 
      from #tmp_budget
      Where ISNULL(Period, '') <> ''
      
    -- Update Temporary Table to match the January period
    Update   #tmp_budget
       Set NetAmount = CustomAmount1
       , Period = @Year + '01' + @PeriodSuffix

    -- INSERT January data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- Update Temporary Table to match the Feb. period
    Update   #tmp_budget
       Set NetAmount = CustomAmount2
       , Period = @Year + '02' + @PeriodSuffix

    -- INSERT Feb. data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- Update Temporary Table to match the Mar. period
    Update   #tmp_budget
       Set NetAmount = CustomAmount3
       , Period = @Year + '03' + @PeriodSuffix

    -- INSERT Mar. data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- Update Temporary Table to match the Apr. period
    Update   #tmp_budget
       Set NetAmount = CustomAmount4
       , Period = @Year + '04' + @PeriodSuffix

    -- INSERT Apr. data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- Update Temporary Table to match the May period
    Update   #tmp_budget
       Set NetAmount = CustomAmount5
       , Period = @Year + '05' + @PeriodSuffix

    -- INSERT May. data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- Update Temporary Table to match the Jun. period
    Update   #tmp_budget
       Set NetAmount = CustomAmount6
       , Period = @Year + '06' + @PeriodSuffix

    -- INSERT Jun. data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- Update Temporary Table to match the Jul. period
    Update   #tmp_budget
       Set NetAmount = CustomAmount7
       , Period = @Year + '07' + @PeriodSuffix

    -- INSERT Jul. data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- Update Temporary Table to match the Aug. period
    Update   #tmp_budget
       Set NetAmount = CustomAmount8
       , Period = @Year + '08' + @PeriodSuffix

    -- INSERT Aug. data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- Update Temporary Table to match the Sep. period
    Update   #tmp_budget
       Set NetAmount = CustomAmount9
       , Period = @Year + '09' + @PeriodSuffix

    -- INSERT Sep. data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- Update Temporary Table to match the Oct. period
    Update   #tmp_budget
       Set NetAmount = CustomAmount10
       , Period = @Year + '10' + @PeriodSuffix

    -- INSERT Oct. data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- Update Temporary Table to match the Nov. period
    Update   #tmp_budget
       Set NetAmount = CustomAmount11
       , Period = @Year + '11' + @PeriodSuffix

    -- INSERT Nov. data into the import
    INSERT INTO $import$
    Select * from #tmp_budget

    -- It is assumed that the NetAmount was initially matching the column for december, so no need to add it once again

Import 12 columns of MONTHLY values (forecast, budget, etc.)

Building on the previous use case, let us now assume that the columns contain monthly values instead of year-to-date values.

In such cases, we could use the same code as above, but instead of setting the NetAmount to another Custom Value, we would add it.

Copy 
- Copy all rows to temporary table
Select * into #tmp_budget from $import$

-- Keep Period information to be used later in the rule
DECLARE @Period nvarchar(12)
DECLARE @Year nvarchar(4)
DECLARE @PeriodSuffix nvarchar(6)

Select TOP 1 @Period = Period
   , @Year = LEFT(Period, 4)
   , @PeriodSuffix = RIGHT(Period, 6) 
  from #tmp_budget
  Where ISNULL(Period, '') <> ''
  
-- Remove all existing data in the import as it would probably not be correct
DELETE FROM $import$
  
-- Update Temporary Table to match the January period
-- Monthly January = Year-to-date January
Update   #tmp_budget
   Set NetAmount = ISNULL(CustomAmount1, 0)  -- we handle the NULL by replacing them by zero
   , Period = @Year + '01' + @PeriodSuffix

-- INSERT January data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the Feb. period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount2, 0)  -- we add the monthly value to existing amount
   , Period = @Year + '02' + @PeriodSuffix

-- INSERT Feb. data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the Mar. period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount3, 0)
   , Period = @Year + '03' + @PeriodSuffix

-- INSERT Mar. data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the Apr. period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount4, 0)
   , Period = @Year + '04' + @PeriodSuffix

-- INSERT Apr. data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the May period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount5, 0)
   , Period = @Year + '05' + @PeriodSuffix

-- INSERT May. data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the Jun. period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount6, 0)
   , Period = @Year + '06' + @PeriodSuffix

-- INSERT Jun. data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the Jul. period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount7, 0)
   , Period = @Year + '07' + @PeriodSuffix

-- INSERT Jul. data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the Aug. period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount8, 0)
   , Period = @Year + '08' + @PeriodSuffix

-- INSERT Aug. data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the Sep. period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount9, 0)
   , Period = @Year + '09' + @PeriodSuffix

-- INSERT Sep. data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the Oct. period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount10, 0)
   , Period = @Year + '10' + @PeriodSuffix

-- INSERT Oct. data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the Nov. period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount11, 0)
   , Period = @Year + '11' + @PeriodSuffix

-- INSERT Nov. data into the import
INSERT INTO $import$
Select * from #tmp_budget

-- Update Temporary Table to match the Dec. period
Update   #tmp_budget
   Set NetAmount = ISNULL(NetAmount, 0) + ISNULL(CustomAmount12, 0)
   , Period = @Year + '12' + @PeriodSuffix

-- INSERT Dec. data into the import
INSERT INTO $import$
Select * from #tmp_budget

Import rule fields

The following fields in the $import$ table can be used for the transformation:

Field name Size Description
TmpImportDataID int  
RowNr int  
RowData nvarchar(max)  
Period nchar(12)  
ConsoID int  
Company nvarchar(20)  
Account nvarchar(20)  
Flow nvarchar(20)  
Partner nvarchar(20)  
NetAmount decimal(24,6)  
Debit decimal(24,6)  
Credit decimal(24,6)  
JournalEntry int  
JournalDescription nvarchar(120)  
HistoricalRate decima(24,8)  
TransactionCurrency nvarchar(3)  
TransactionAmount decimal(24,6)  
DimensionGroup nvarchar(25)  
DimensionDetail1 nvarchar(25)  
DimensionDetail2 nvarchar(25)  

DimensionDetail3

nvarchar(25)  

HasConversionError ( = 0 )

bit

 

TableName ( NULL )

varchar(8)

  
DoProcess ( = 1 ) bit  

 

Additional fields available:

Field name Size Description
CustomAmount11    
CustomAmount12    
CustomText1    
CustomText2    
CustomText3    
CustomText4    
CustomText5    
CustomText6    
CustomText7    
CustomText8    
CustomText9    
CustomText10    
CustomText11    
CustomText12