Export data using a Method Query

[MarkBird]

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

[aformenti · Accepted solution]

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	

[aformenti · Accepted solution]

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	

[FredLucas · Accepted solution]

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

[SteveK]

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