Forum Discussion

MarkBird's avatar
MarkBird
Contributor III
9 months ago
Solved

Export data using a Method Query

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 busine...
Data Management
  • FredLucas's avatar
    FredLucas
    9 months ago

    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

     

aformenti's avatar
aformenti
Contributor II
9 months ago

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