Anyway to account for hard carriage return in a data load file?

MikeKral
New Contributor

SOURCE: ONESTREAM CHAMPIONS

Company has a hard carriage return in their file for a couple of description fields. They are struggling to fix it on their side. Is there code that can be written for the data source to remove this hard return and get everything on one line? Appreciate any guidance or snippet of cod

5 REPLIES 5

I don’t think you can. When the file comes in the column you are trying to fix is already in the second line. This needs to be done at the source. I cannot think of a different way to solve it.

Thanks Celvin! I tend to agree as that it is what I see when I look at the data in Notepad++ The line is already showing up in 3 separate lines within the data file. I will push back on the and try and get them to resolve on their side or determine that this field is not critical to bring into stage. Thanks, Mike

AndyR
New Contributor III

I had a similar problem at one point and had to remove a significant number of special characters (not all shown here). Try this in a Parser BR (sorry the tabs are not pasting in correctly):

Namespace OneStream.BusinessRule.Parser.Parse_Remove_Special_Characters
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As ParserDimension, ByVal args As ParserArgs) As Object
Try

  		If args.Value = "" Then
  			Return "Empty_String"
  			Else

  				Return args.Value.Replace(vbCr, "").Replace(vbLf, "")
  		
  		End If	

  	Catch ex As Exception
  		Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
  	End Try	
  End Function

End Class
End Namespace

For the sake of anyone else that might read this later, you then attach this Business Rule to the dimension in the Data Source that you’re having issues with by selecting it as the Logical Operator/Expression.

MikeKral
New Contributor

Thanks for the suggestion Andy! I will give it a try and let you the group know.

sameburn
New Contributor III

Hi.  I have been testing this.  You can't use a data source / parser rule here because the column you are trying to fix is already in the next line.  However, you can solve for this with a Connector BR and using the TextFieldParser class of the Microsoft.VisualBasic.FileIO namespace.  In this example we are picking up the file from FileShare as csv with semi-colon delimiter

' Custom Import (don't forget to add and uncomment line below)
'Imports Microsoft.VisualBasic.FileIO

' Get File Path (FileShare)
Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
Dim fullPath As String = Path.Combine(FileShareFolderHelper.GetGroupsFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppToken.AppName), "Everyone")
Dim fileName As String = "SampleDataConnector.txt"
Dim fileFullPath As String = String.Join("\", fullPath, fileName)		
				
Dim dt As New DataTable()
												
' Check file Exists
If File.Exists(fileFullPath) Then							
	' Declare TextFieldParser to handle line breaks in source file
	Using parser As New TextFieldParser(fileFullPath)
		' Set delimiter
		parser.Delimiters = New String() {";"}
		' Read the first line as header row
		Dim headers As String() = parser.ReadFields()
		' Loop on headers array
		For Each header As String In headers					
			' Define columns
			dt.Columns.Add(header)					
		Next
								
		' Loop until end of file
		While Not parser.EndOfData				
			' Read the current line as fields
			Dim fields As String() = parser.ReadFields()
									
			' Declare target dataRow
			Dim dr As DataRow = dt.NewRow()
						
			' Loop through CSV content
			For i As Integer = 0 To headers.Length - 1				
				' Assign fields to dataRow
				dr(i) = fields(i)				
			Next					
			' Add dataRow to dataTable
			dt.Rows.Add(dr)				
		End While
	End Using
End If