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
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...
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
OneStream Employee
OneStream EmployeeHI 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
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?
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
