Skip to main content
M
MarkBird
June 18, 2024
Solved

Export data using a Method Query

  • June 18, 2024
  • 3 replies
  • 1 view

Hi

I have a method query that returns a data table of roughly 300k of records.

Is there anyway to export the result of the method query to a csv file?

I'm not keen on using a spreadsheet business rule or a grid view on a dashboard given the volume of records that is being returned.

Thanks,

Mark

    Best answer by FredLucas

    Hi MarkBird 

    You could use the following snippet:

    		#Region "PrintDataTable"
		Public Function PrintDataTable(ByVal si As SessionInfo, ByVal dt As DataTable, ByVal Optional strSeparator As String = "|", ByVal Optional limit As Integer = 0, ByVal Optional boolPrintTableName As Boolean = True, ByVal Optional list As List(Of String) = Nothing, ByVal Optional strTextSep As String = Nothing) As String
	        Dim strLog As New text.StringBuilder
			Dim strBRName As String = System.Reflection.MethodInfo.GetCurrentMethod().Name
			Dim listColToSkip As New List(Of String) ({"None"})
		    If Not IsNothing(list) Then
		        listColToSkip = list
		    End If
			strLog.AppendLine(String.Format("{0} - Sep: {1}, Limit: {2}, Print Table Name: {3}, Col(s) To Skip: {4}", strBRName, strSeparator, limit.ToString, boolPrintTableName.ToString, String.Join(", ", listColToSkip)))
			
			If strSeparator.XFEqualsIgnoreCase("tab") Then
				strSeparator = vbTab
			End If
			
			Try
	            Dim result As New Text.StringBuilder
				
				If boolPrintTableName Then 
					result.AppendLine("Table name: " & dt.TableName)
				End If

	            Dim header As String = Nothing
	            For Each dc As DataColumn In dt.Columns
					strLog.AppendLine(String.Format("{0} - Col Name: {1}, To Skip: {2}", strBRName, dc.ColumnName, listColToSkip.Contains(dc.ColumnName)))
					If Not listColToSkip.Contains(dc.ColumnName) Then 
	                	header = header & dc.ColumnName & strSeparator
					End If
	            Next
				If Not String.IsNullOrEmpty(header) Then
	            	result.AppendLine(header)
				End If

				If limit = 0 Then limit = dt.Rows.Count -1
				If limit > dt.Rows.Count -1 Then limit = dt.Rows.Count -1
	            For curRow As Integer = 0 To limit
	                Dim row As String = String.Empty
	                For curCol As Integer = 0 To dt.Columns.Count -1
						If Not listColToSkip.Contains(dt.Columns(curCol).ColumnName) Then
	                    	If strTextSep Is Nothing Then 
	                    		row = row & dt.Rows(curRow)(curCol).ToString.Trim & strSeparator
							Else
								row = row & strTextSep & dt.Rows(curRow)(curCol).ToString.Trim & strTextSep & strSeparator
							End If
						End If
	                Next
					If Not String.IsNullOrEmpty(row) Then
	                	result.AppendLine(row)
					End If
	            Next

	            Return result.ToString
	            
	        Catch ex As Exception
				BRApi.ErrorLog.LogMessage(si, strLog.ToString)
	            Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
	        End Try
	        
	    End Function	
		#End Region
		
		
		#Region "DataTableToFile"
		Public Function DataTableToFile(ByVal si As SessionInfo, ByVal dt As DataTable, ByVal strFilePath As String, ByVal listColToSkip As List(Of String), ByVal Optional strSeparator As String = ",") As XFFileEx
			
			Dim strDt As String = Me.PrintDataTable(si, dt, strSeparator, 0, False, listColToSkip, """")
			Dim bytes() As Byte = System.Text.Encoding.UTF8.GetBytes(strDt)
			
			Dim fileinfo As New XFFileInfo(fileSystemLocation.ApplicationDatabase, strFilePath)
			Dim xfFile As New XFFile(fileinfo, String.Empty, bytes)
			BRApi.FileSystem.InsertOrUpdateFile(si, xffile)
			
			Dim xfeFile As New XFFileEx(xffile, DataAccessLevel.AllAccess, False,String.Empty)
			Return xfeFile
			
		End Function
		#End Region

     

    3 replies

    A
    aformenti
    June 18, 2024

    HI MarkBird yes, there is a way to do that. This would do it:

    '1. Declare target dims and DataTable			
				Dim csvContent As New Text.StringBuilder
				Dim qualifier As String = StageConstants.ParserDefaults.DefaultQuoteCharacter
				Dim delimiter As String = StageConstants.ParserDefaults.DefaultDelimiter
				Dim fileName As String = "Test.csv"				
				
				'Create DataTable
				Dim dt As New DataTable
				'Define columns
				dt.Columns.Add("ID", GetType(Integer))
				dt.Columns.Add("Name", GetType(String))
				'Define rows
				Dim row = dt.NewRow
				row("ID") = 1
				row("Name") = "AAA"
				dt.Rows.Add(row)
				row = dt.NewRow
				row("ID") = 2
				row("Name") = "BBB"
				dt.Rows.Add(row)
				row = dt.NewRow
				row("ID") = 3
				row("Name") = "CCC"
				dt.Rows.Add(row)
				
				'2. If rows returned, create CSV file content
				If Not dt Is Nothing Then
				
					'Declare string builder to store csv text
					Dim csv As New Text.StringBuilder
					csv.AppendLine("")
					csv.AppendLine("")
					
					'Create column list from dt
					Dim colList As New List(Of String)
					For Each dc As DataColumn In dt.Columns
						colList.Add(dc.ColumnName)
					Next
					
					'Write the column Definitions
					Dim colDescs As New Text.StringBuilder
					For Each colName As String In colList					

						Dim dc As DataColumn = dt.Columns(colName)
						
						'Check to see if the column data type requires quotes
						Dim quotesRequired As Boolean = False
						If dc.ColumnName.Contains(delimiter) Then
							quotesRequired = True
						End If
						
						If colDescs.Length > 0 Then
							colDescs.Append(delimiter)
							If quotesRequired Then colDescs.Append(qualifier)
							colDescs.Append(dc.ColumnName)
							If quotesRequired Then colDescs.Append(qualifier)
						Else
							If quotesRequired Then colDescs.Append(qualifier)
							colDescs.Append(dc.ColumnName)
							If quotesRequired Then colDescs.Append(qualifier)
						End If
					Next
					csvContent.AppendLine(colDescs.ToString)				
					
					'Write the Data Rows
					For Each dr As DataRow In dt.Rows
						Dim rowVals As New Text.StringBuilder
						For Each colName As String In colList
							Dim dc As DataColumn = dt.Columns(colName)
							Dim rowVal As String = dr(dc.Ordinal).ToString
								
							'Check to see if the row data type requires quotes
							Dim quotesRequired As Boolean = False
							If rowVal.Contains(delimiter) Then
								quotesRequired = True
							End If
																		
													
							If rowVals.Length > 0 Then
								rowVals.Append(delimiter)
								If quotesRequired Then rowVals.Append(qualifier)
								rowVals.Append(rowVal)
								If quotesRequired Then rowVals.Append(qualifier)
							Else
								If quotesRequired Then rowVals.Append(qualifier)
								rowVals.Append(rowVal)
								If quotesRequired Then rowVals.Append(qualifier)
							End If
						Next
						csvContent.AppendLine(rowVals.ToString)
					Next
																												
				'3. Write File to User Temp Folder (to open from dashboard button)	
				' This folder is cleared when user session expires
				Dim fileBytes As Byte() = Encoding.UTF8.GetBytes(csvContent.ToString)
				BRApi.Utilities.SaveFileBytesToUserTempFolder(si, si.UserName, fileName, fileBytes)
				'Add this text To button that calls Function.  Navigation Action.  Open File
				'FileSourceType=Application, UrlOrFullFileName=[Internal/Users/|UserName|/Temp/Test.csv], OpenInXFPageIfPossible=False
				
				End If

     

    E
    emclend
    February 5, 2026

    I know this is old news, but I think this code does exactly what I need.  Where would you actually put this code for it to function correctly.  Does it go within the Dashboard Data Set rule, or does it go elsewhere?

    S
    SteveK
    February 6, 2026

    With my example, you can pretty much put it anywhere (any BR or Workspace assembly)  as it's just a Function that you can call that doesn't expect any UI interaction.

    This means that you could stick it at the bottom of your BR, put it in a Utilities BR and reference the BR in properties, or pop it in a Utilities Assembly and reference the namespace there.

    It's basically the same for Albert's example too, except that the snippet is not wrapped in a Sub/Function.

    I'd suggest to get it working first of all that you just drop it into any BR you have as a Function and check that it gives you what you want.

    Steve

     

    F
    FredLucasAnswer
    June 18, 2024

    Hi MarkBird 

    You could use the following snippet:

    		#Region "PrintDataTable"
		Public Function PrintDataTable(ByVal si As SessionInfo, ByVal dt As DataTable, ByVal Optional strSeparator As String = "|", ByVal Optional limit As Integer = 0, ByVal Optional boolPrintTableName As Boolean = True, ByVal Optional list As List(Of String) = Nothing, ByVal Optional strTextSep As String = Nothing) As String
	        Dim strLog As New text.StringBuilder
			Dim strBRName As String = System.Reflection.MethodInfo.GetCurrentMethod().Name
			Dim listColToSkip As New List(Of String) ({"None"})
		    If Not IsNothing(list) Then
		        listColToSkip = list
		    End If
			strLog.AppendLine(String.Format("{0} - Sep: {1}, Limit: {2}, Print Table Name: {3}, Col(s) To Skip: {4}", strBRName, strSeparator, limit.ToString, boolPrintTableName.ToString, String.Join(", ", listColToSkip)))
			
			If strSeparator.XFEqualsIgnoreCase("tab") Then
				strSeparator = vbTab
			End If
			
			Try
	            Dim result As New Text.StringBuilder
				
				If boolPrintTableName Then 
					result.AppendLine("Table name: " & dt.TableName)
				End If

	            Dim header As String = Nothing
	            For Each dc As DataColumn In dt.Columns
					strLog.AppendLine(String.Format("{0} - Col Name: {1}, To Skip: {2}", strBRName, dc.ColumnName, listColToSkip.Contains(dc.ColumnName)))
					If Not listColToSkip.Contains(dc.ColumnName) Then 
	                	header = header & dc.ColumnName & strSeparator
					End If
	            Next
				If Not String.IsNullOrEmpty(header) Then
	            	result.AppendLine(header)
				End If

				If limit = 0 Then limit = dt.Rows.Count -1
				If limit > dt.Rows.Count -1 Then limit = dt.Rows.Count -1
	            For curRow As Integer = 0 To limit
	                Dim row As String = String.Empty
	                For curCol As Integer = 0 To dt.Columns.Count -1
						If Not listColToSkip.Contains(dt.Columns(curCol).ColumnName) Then
	                    	If strTextSep Is Nothing Then 
	                    		row = row & dt.Rows(curRow)(curCol).ToString.Trim & strSeparator
							Else
								row = row & strTextSep & dt.Rows(curRow)(curCol).ToString.Trim & strTextSep & strSeparator
							End If
						End If
	                Next
					If Not String.IsNullOrEmpty(row) Then
	                	result.AppendLine(row)
					End If
	            Next

	            Return result.ToString
	            
	        Catch ex As Exception
				BRApi.ErrorLog.LogMessage(si, strLog.ToString)
	            Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
	        End Try
	        
	    End Function	
		#End Region
		
		
		#Region "DataTableToFile"
		Public Function DataTableToFile(ByVal si As SessionInfo, ByVal dt As DataTable, ByVal strFilePath As String, ByVal listColToSkip As List(Of String), ByVal Optional strSeparator As String = ",") As XFFileEx
			
			Dim strDt As String = Me.PrintDataTable(si, dt, strSeparator, 0, False, listColToSkip, """")
			Dim bytes() As Byte = System.Text.Encoding.UTF8.GetBytes(strDt)
			
			Dim fileinfo As New XFFileInfo(fileSystemLocation.ApplicationDatabase, strFilePath)
			Dim xfFile As New XFFile(fileinfo, String.Empty, bytes)
			BRApi.FileSystem.InsertOrUpdateFile(si, xffile)
			
			Dim xfeFile As New XFFileEx(xffile, DataAccessLevel.AllAccess, False,String.Empty)
			Return xfeFile
			
		End Function
		#End Region

     

    S
    SteveK
    June 20, 2024
    Not wanting to be left out, here is something that I use. You might want to purge to disk every 50000 rows or so if it's a really big row set. Sorry about formatting - not sure how you make it look fancy here!
     
    #Region "Write DataTable to File"
     
    '==============================================================================================================
    ' Write DataTable to File
    Public Function WriteDataTableToFile(ByVal si As SessionInfo, ByRef dtDataTable As DataTable, ByVal strOutputFileNameAndPath As String, Optional ByVal bIncludeHeader As Boolean = True, Optional ByVal strSeparator As String = ",", Optional bAppendToFile as Boolean = False) As Integer
    '==============================================================================================================
    ' Write out DataTable to supplied fully qualified path, using the supplied separator and optionally including Column names
    ' Note that DataTable is passed by Reference as you don't want to be making-in memory copies of big tables!!
    ' The number of rows written (excluding Header) is returned.
    '--------------------------------------------------------------------------------------------------------------
    Try
    '--------------------------------------------------------------------------------------------------------------
    Dim strOutput As New StringBuilder
    '--------------------------------------------------------------------------------------------------------------
    ' Set up output file in OneStream folder for this user
    If Not System.IO.File.Exists(strOutputFileNameAndPath) Then System.IO.File.Create(strOutputFileNameAndPath).Close
    '--------------------------------------------------------------------------------------------------------------
    If bIncludeHeader Then
    ' Write out Column Headers if required
    For i = 0 To (dtDataTable.Columns.Count - 1)
    strOutput.Append(dtDataTable.Columns(i).ColumnName & strSeparator)
    Next
    strOutput.AppendLine ' Appends CRLF to end of line
    End If
    '--------------------------------------------------------------------------------------------------------------
    ' Write out Rows field by field
    For Each rwRow As DataRow In dtDataTable.Rows
    For Each field In rwRow.ItemArray
       strOutput.Append((field.ToString) & strSeparator)
    Next
    strOutput.AppendLine ' Appends CRLF to end of line
    Next
    '--------------------------------------------------------------------------------------------------------------
    If bAppendToFile Then 
    System.IO.File.AppendAllText(strOutputFileNameAndPath,strOutput.ToString)
    Else
    System.IO.File.WriteAllText(strOutputFileNameAndPath,strOutput.ToString)
    End If
    '--------------------------------------------------------------------------------------------------------------
    Return dtDataTable.Rows.Count
    '--------------------------------------------------------------------------------------------------------------
    Catch ex As Exception
    Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    '--------------------------------------------------------------------------------------------------------------
    End Try
     
    End Function
    #End Region
    Terms & ConditionsAccessibility statement