Reading dictionary type

[taru]

I am using below sql to get validation messages from the workflow transformation process to be used in  Transformation Event Handler. But, I need to put the output of this dictionary into a flat file. How I can read the contents of this dictionary created.

Thanks so much for your help!

sqlValidation.Append("Select * ")

sqlValidation.Append("From StageToFinanceValidationError ")

sqlValidation.Append("Where StageToFinanceValidationError.WorkflowProfileKey = '" & wfPk.ProfileKey.ToString & "' ")

sqlValidation.Append("And StageToFinanceValidationError.WorkflowTimeKey = '" & wfPk.TimeKey.ToString & "' ")

sqlValidation.Append("And StageToFinanceValidationError.WorkflowScenarioKey = '" & wfPk.ScenarioKey.ToString & "' ")

Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)

dtValidation = BRAPi.Database.ExecuteSqlUsingReader(dbConnApp, sqlValidation.ToString, False)

Dim sb_ValErrorsReport As New Text.StringBuilder()

For Each drVal As DataRow In dtValidation.Rows

Dim strValMsg = drVal("ValidationmessagesXml")'.ToString()

Dim validationMsgList As DataCellValidationMsgList = XmlObject.ReadXmlString(si, GetType(DataCEllValidationMsgList), strValMsg, True)

If Not validationMsgList.Messages Is Nothing And validationMsgList.Messages.Count <> 0 Then

Dim errmessage As String = validationMsgList.Messages(0).ErrorMsg

Dim errdetails As List(Of String) = validationMsgList.Messages(0).StringParams.ToList()

errmessage = string.Format(errmessage, errdetails.ToArray())

If Not errorDict.Keys.Contains(errmessage) Then

Dim templist As New List(Of List(Of String))

        templist.Add(errdetails)

errorDict.Add(errmessage, templist)

Else

errorDict(errmessage).Add(errdetails)

End If

End If

Next

[Henning]

Hi, how about instead of writing the results into a dictionary, use your existing data table (dtValidation) and save it as a CSV directly. 

This post should get you where you want to be:

Solved: Saving an export directly to the shared folder - OneStream Community (onestreamsoftware.com)

[DanielWillis]

I agree, if you can do this during your initial for loop then it would save doing more potentially redundant looping down the track.

I would also suggest including more detail in your code to at least show the definition of the variable that you're asking about.

It looks like your dictionary contains a list of strings for each key. I would strongly advise doing it in your datarow loop but here is some generic VB.net code that could have assisted if that option was not available:

        ' Specify the file path for the CSV file
        Dim filePath As String = "errorOutput.csv"

        ' Generate CSV output
        Using writer As New StreamWriter(filePath)
            ' Write the header (optional)
            writer.WriteLine("Error Code,Description")

            ' Iterate through the dictionary
            For Each kvp As KeyValuePair(Of String, List(Of String)) In errorDict
                Dim key As String = kvp.Key
                For Each value As String In kvp.Value
                    ' Write the key and value to the CSV file
                    writer.WriteLine($"{key},{value}")
                Next
            Next
        End Using

[aformenti]

HI @taru,

This snippet should give you what you need:

#Region "Dictionary To DataTable"				
	'------------------------------------------------------------------------------------------------------------
	'Referenced Code: 	DictionaryToDataTable -> DataTable
	'
	'Description:		Converts Dictionary(Of String, String) to DataTable from Dictionary where column = Key and row = Value
	'
	'Notes:				
	'
	'Usage:				
	'
	'Created By:		OneStream
	'Date Created:		04-04-2023
	'------------------------------------------------------------------------------------------------------------					
	Public Function DictionaryToDataTable(ByVal si As SessionInfo, ByVal dictParam As Dictionary(Of String, String)) As DataTable
		' Method name
		Dim methodName As String = System.Reflection.MethodInfo.GetCurrentMethod().Name	
		' Object to return
		Dim dt As New DataTable()				
		Try
			' Add columns (Keys)
			dt.Columns.AddRange(dictParam.Keys.ToList().Select(Function(lambda) New DataColumn(lambda, GetType(String))).ToArray())
			' Add row (Values)
			dt.Rows.Add(dictParam.Values.ToArray())
			
			' Return
			Return dt
			
		Catch ex As Exception
			Throw ErrorHandler.LogWrite(si, New XFException(ex))					
		End Try		
	End Function
	
#End Region