Beyond Flat and Delimited Files: How to Load XML or JSON Data

Picture this: it's a bright, sunny morning, and you're spinning on your chair, enjoying the warmth of the sun on your face. Suddenly, your boss approaches you with an intriguing question: "Hey Om! Can we load data using XMLs or JSON file formats in OneStream?" Without hesitation, you respond with enthusiasm, "Yeah, why not!"

What follows is a journey into the vast world of OneStream, where you set sail in search of the solution to this intriguing query.With your curiosity piqued, you embark on a journey into the OneStream universe, eager to uncover how to load data into the OneStream using XML or JSON file formats. Armed with determination, you navigate through OneStream's extensive knowledge resources, determined to find the answer.As you delve deeper into the OneStream knowledge hub, you soon stumble upon a game-changing option: the Connector that uses a file.

When we enable the Connector Uses File setting as True, when going through an Import Workflow Step, the system will ask for a file, despite being a Connector DataSource. Once uploaded, the file will be placed in the directory "C:\Windows\TEMP\XF\StageLoad\YourFile"

Now you can parse that file inside a Connector Business Rule. Using api.FileInfo you can access variours properties related to that file. Here's sample code to implement a Connector that can read a JSON file with a basic structure:

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

Namespace OneStream.BusinessRule.Connector.SampleConnectorFileBased

	Public Class MainClass

		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer, ByVal args As ConnectorArgs) As Object
			Try
				'Get the Field name list or load the data 
				Select Case args.ActionType
					Case Is = ConnectorActionTypes.GetFieldList
						'-----------------------------------------------------------
						'Return Field Name List to get field list in GetFieldList
						'-----------------------------------------------------------
						Dim fieldList As List(Of String) = GetFieldList(si, globals, api)
						Return fieldList
					Case Is = ConnectorActionTypes.GetData
						'-----------------------------------------------------------
						'Process Data from your file in this section.
						'-----------------------------------------------------------
						Dim DataTable As DataTable = GetFileAsDataTable(si, globals, api)
						api.Parser.ProcessDataTable(si, DataTable, False, api.ProcessInfo)
						Return Nothing
				End Select
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function

		'Create the field list function to get field list
		'-----------------------------------------------------------
		Private Function GetFieldList(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer) As List(Of String)
			Try
				Dim fields As New List(Of String)
				'Replace sample field lines below with actual fields
				fields.add("FIELD1")
				fields.add("FIELD2")
				Return fields
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function

		'The GetFileAsDataTable function return's JSON as DataTable 
		Private Function GetFileAsDataTable(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer) As DataTable
			Try
				Dim filePath As String = api.FileInfo.SourceFilePath
				Dim json As String = File.ReadAllText(filePath)
				Dim dataTable As DataTable = JsonConvert.DeserializeObject(Of DataTable)(json)
				Return DataTable
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

There are numerous advantages of using this option:

• Ability to Implement Drill Back Options: Loading data using JSON or XML files enables you to implement drill-back options, allowing you to trace the source of data easily.
• Parsing Complex File Structures: With this approach, you can parse complex file structures and load them into OneStream, providing flexibility and scalability to your data integration processes.
• Solving Client Problems: Having the option to load data from JSON or XML files can help solve a variety of client-specific data integration challenges, making life easier for both clients and developers.

In conclusion, exploring beyond flat and delimited files by loading data using JSON or XML file formats can open up new possibilities for data integration and analysis within OneStream. With the right setup and code, you can harness the power of structured data sources and streamline your data integration processes. So, the next time your boss asks if it's possible, you can confidently say... "of course, it's OneStream!"

About the Author

Omkareshwar is a Senior Consultant at Archetype Consulting with a background in Electrical Engineering. Specializing as a OneStream Technical Developer, he leverages technical expertise to architect and implement financial solutions. Joining the industry in August 2021, Omkareshwar is not only eager to learn and stay at the forefront of innovations but is also passionate about contributing to the community of finance and technology professionals.
:link: Omkareshwar on LinkedIn
:briefcase: Archetype Consulting Services