The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
MarkBird
2 years agoContributor III
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 business rule or a grid view on a dashboard given the volume of records that is being returned.
Thanks,
Mark
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
5 Replies
- aformenti
OneStream Employee
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- emclendNew Contributor II
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?
- SteveKContributor
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
- FredLucas
OneStream Employee
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 - SteveKContributorNot 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 FilePublic 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 userIf Not System.IO.File.Exists(strOutputFileNameAndPath) Then System.IO.File.Create(strOutputFileNameAndPath).Close'--------------------------------------------------------------------------------------------------------------If bIncludeHeader Then' Write out Column Headers if requiredFor i = 0 To (dtDataTable.Columns.Count - 1)strOutput.Append(dtDataTable.Columns(i).ColumnName & strSeparator)NextstrOutput.AppendLine ' Appends CRLF to end of lineEnd If'--------------------------------------------------------------------------------------------------------------' Write out Rows field by fieldFor Each rwRow As DataRow In dtDataTable.RowsFor Each field In rwRow.ItemArraystrOutput.Append((field.ToString) & strSeparator)NextstrOutput.AppendLine ' Appends CRLF to end of lineNext'--------------------------------------------------------------------------------------------------------------If bAppendToFile ThenSystem.IO.File.AppendAllText(strOutputFileNameAndPath,strOutput.ToString)ElseSystem.IO.File.WriteAllText(strOutputFileNameAndPath,strOutput.ToString)End If'--------------------------------------------------------------------------------------------------------------Return dtDataTable.Rows.Count'--------------------------------------------------------------------------------------------------------------Catch ex As ExceptionThrow ErrorHandler.LogWrite(si, New XFException(si, ex))'--------------------------------------------------------------------------------------------------------------End TryEnd Function#End Region
Related Content
- 3 years ago
- 7 months ago
- 3 years ago