Validate XFD range in Excel for errors

Nokhez
New Contributor II

I am looking for an option to read the parsed Excel file XFD range and check if it contains any #Value! cells. I am able to find a point in TransformationEventHandler business rule to capture the parsed data (TransformerObject.Parser.DelimitedParsedValues), but it's not giving all parsed data every time and giving only few lines sometime.

Can you please suggest some solution to validate Excel file XFD range before loading it into cube.

Also where can I find the details of the classes in OneStream specifically for OneStream.Shared.Engine.StageRangeContent

2 REPLIES 2

OS_Pizza
Contributor III

Can you share the excel file ( just keep 1 row). Also , Share the TEHandler rule and show what are you trying to achieve?

Nokhez
New Contributor II

Hi OS_Pizza,

Here is the trimmed TEHandler business rule.

 

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database

Namespace OneStream.BusinessRule.TransformationEventHandler.TransformationEventHandler
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As TransformationEventHandlerArgs) As Object
    Try
    
    Dim returnValue As Object = args.DefaultReturnValue
    args.UseReturnValueFromBusinessRule = FALSE
    args.Cancel = FALSE
    
    'Evaluate the operation type in order to determine which sub-event is being processed
    Select Case args.OperationName
        Case Is = BREventOperationType.Transformation.ParseAndTrans.DeleteData
            If args.IsBeforeEvent Then
                Dim objTrans As Transformer = args.Inputs(0)
				Dim errorCodes() As String = { "#VALUE!", "#NUM!","#N/A","#REF!" }
				For Each dpValues In objTrans.Parser.DelimitedParsedValues
					If errorCodes.Contains(dpValues, StringComparer.CurrentCultureIgnoreCase) Then
						BRApi.ErrorLog.LogMessage(si,dpValues)
						Throw New XFException(si,Nothing, Nothing, "Invalid Data: Excel file contains " + dpValues + " error in calculated cells")
					End If
				Next
            End If
            
        Case Is = BREventOperationType.Transformation.ParseAndTrans.ProcessTransformationRules
            'Some Business logic            
        Case Is = BREventOperationType.Transformation.ParseAndTrans.StartParseAndTransform
            'Do Nothing
        Case Is = BREventOperationType.Transformation.ParseAndTrans.InitializeExcelRangeLayout
            'Check the before / after flag, we want to handle the AFTER event
            If Not args.IsBeforeEvent Then
                Dim objRange As StageRangeContent = DirectCast(args.Inputs(1), StageRangeContent)
                'Check the transformation rule cache to make sure the mappings are correct.
                Dim objParser As Parser = DirectCast(args.Inputs(0), Parser)
                Dim var As WorkflowUnitPk = objParser.Transformer.WorkflowUnitPk
                Dim wfStatus As WorkflowInfo = BRApi.Workflow.Status.GetWorkflowStatus(si,var,False)
                If wfStatus.Locked Then
                    Throw New XFException(si,Nothing,Nothing,"Cannot load data, the workflow Is locked.")
                Else
                    If Not objRange Is Nothing Then
                        'Loop over the tokens and find the index of the time dimension
                        Dim timeIndex As String = SharedConstants.Unknown.ToString
                        For Each kvpToken As KeyValuePair(Of Integer, String) In objRange.DimensionTokens
                            'Parse the excel token
                            Dim token As StageTokenParts = StageConstants.MasterDimensionTokens.GetTokenParts(kvpToken.Value)
                            'Evaluate the token in order to see if it is time.
                            If token.DimensionName.Equals(StageConstants.MasterDimensionNames.Time, StringComparison.InvariantCultureIgnoreCase) Then
								'< Perform input time validation >
                                
                            End If
                            
                        Next
                        
                    End If
                End If
            End If
    End Select

'				Return returnValue
    Catch ex  As Exception
    Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace

 

  

And this is the sample Excel XFD range I am trying to load
C#:Local
E#:[11111]
P#
S#:[BUDGET]
V#:[Periodic]
A#
F#
O#:[Import]
IC#:[None]
U1#:[None]
U2#:[9999999]
U3#:[None]
U4#:[U4Base]
U5#:[None]
U6#:[None]
U7#:[None]
U8#:[None]
A1#
A2#
SI#:[9999999_ID]
AMT.ZS#
T#:[]:[]:[2023M1]
T#:[]:[]:[2023M2]
T#:[]:[]:[2023M3]
                        
     
123456
EUR
              
12345
0
111
     
123456
EUR
              
444
#VALUE!
333