Forum Discussion

syedfasiullah's avatar
syedfasiullah
New Contributor II
2 years ago

How to download the database table contents to the Excel file .CSV format in OneStream

Hi all,

How to download the table contents from the System --> database --> Tables or Views, when I tried to download, it is allowing to download the 50 rows only - I'm not sure where is a restriction like this and how would I avoid these issues?

Thanks

 

9 Replies

  • Hector's avatar
    Hector
    New Contributor II
    2 years ago

    Go to security, select your user. Go to preferences and change the Grid Rows Per Page.

     

    HC

    • syedfasiullah's avatar
      syedfasiullah
      New Contributor II
      2 years ago

      Thanks Hector - what if I have more than 1000 rows, then how would I do it? I tried that option, it has maximum of only 1000 rows. 

      Thanks 

      • FredLucas's avatar
        FredLucas
        Icon for OneStream Employee rankOneStream Employee
        2 years ago

        Hi syedfasiullah,

        In that case you might want to export the content of the table to .csv via a business rule.

        You could do this by getting the data you want to export into a data table object like this:

        Dim dtResult As DataTable
Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) 
  Dim strSQL As New Text.StringBuilder
  strSQL.AppendLine("Select * from TableToExport")
  dtResult = BRApi.Database.ExecuteSqlUsingReader(dbConnApp, strSQL.ToString, False)
End Using

        And then use the DataTableToFile function that I've shared on this post:

        https://community.onestreamsoftware.com/t5/Workflow-and-Data-Integration/Export-data-using-a-Method-Query/m-p/35630

         

  • Goodwin1's avatar
    Goodwin1
    New Contributor
    3 days ago

    If you are just trying to get the contents out of the OS table into a csv file, you can create a SQL data adapter and hook it to a grid view component and dashboard. After that, all you have to do is right click on the grid view and export to a csv. 

    • Alvaro's avatar
      Alvaro
      New Contributor III
      3 days ago

      Hi!

      In a grid view, you can't download millions of records; each page has a maximum of 1000 records. This method wouldn't be possible for tables with millions of records.

      • Goodwin1's avatar
        Goodwin1
        New Contributor
        3 days ago

        I agree that, if the tables happen to have millions of rows, then grid view probably isn't a viable option. Having said that, I have exported files from the grid view that was far in excess of 1000 records. In fact, I just did one with a tad under 40,000 rows and it worked fine. 

  • Alvaro's avatar
    Alvaro
    New Contributor III
    4 days ago

    Hello , this code was useful for me and get the same purpose:

    Namespace OneStream.BusinessRule.Extender.Test_ExportTabletoCSV
    Public Class MainClass
     
    Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
                Dim extender As New MainClass()
     
    Try
    Dim strSQL As String = "SELECT * FROM TableName"
    Dim dt As DataTable
    Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
        dt = BRApi.Database.ExecuteSqlUsingReader(dbConnApp, strSQL, True)
    End Using
     
    If dt Is Nothing OrElse dt.Rows.Count = 0 Then
        BRApi.ErrorLog.LogMessage(si, "⚠️ La consulta no devolvió filas.")
        Return "No hay datos para exportar."
    End If
     
    Dim fileName As String = "ExportTablaTest.csv"
        Dim filePathOS As String = $"Documents\Public\{fileName}"
     
        BRApi.ErrorLog.LogMessage(si, "[DEBUG] Generando CSV...")
     
        Dim csvContent As String = DataTableToCSV(dt, ";")
    Dim utf8WithBom As New System.Text.UTF8Encoding(True)
    Dim bytes() As Byte = utf8WithBom.GetBytes(csvContent)
     
        BRApi.ErrorLog.LogMessage(si, $"[DEBUG] CSV generado. Bytes: {bytes.Length}")
     
        Dim fileInfo As New XFFileInfo(fileSystemLocation.ApplicationDatabase, filePathOS)
        Dim xfFile As New XFFile(fileInfo, String.Empty, bytes)
     
        BRApi.ErrorLog.LogMessage(si, "[DEBUG] Intentando guardar archivo...")
     
        BRApi.FileSystem.InsertOrUpdateFile(si, xfFile)
     
        Return $"✅ Exportación completada: {filePathOS}"
     
    Catch ex As Exception
        BRApi.ErrorLog.LogMessage(si, "❌ Error al guardar CSV: " & ex.ToString())
        Return $"❌ Error: {ex.Message}"
    End Try
     
            End Function
     
            ' Convierte el DataTable a texto CSV
    Public Function DataTableToCSV(dt As DataTable, Optional separator As String = ";") As String
        Dim sb As New StringBuilder()
     
        ' --- Cabecera ---
        For i As Integer = 0 To dt.Columns.Count - 1
            Dim colName As String = EscapeCsvValue(dt.Columns(i).ColumnName)
            sb.Append(colName)
            If i < dt.Columns.Count - 1 Then sb.Append(separator)
        Next
        sb.AppendLine()
     
        ' --- Filas ---
        For Each row As DataRow In dt.Rows
            For i As Integer = 0 To dt.Columns.Count - 1
                Dim value As String = EscapeCsvValue(row(i).ToString())
                sb.Append(value)
                If i < dt.Columns.Count - 1 Then sb.Append(separator)
            Next
            sb.AppendLine()
        Next
     
        Return sb.ToString()
    End Function
     
    ' --- Función auxiliar que asegura formato CSV válido ---
    Private Function EscapeCsvValue(value As String) As String
        If value Is Nothing Then value = ""
     
        ' Si contiene separadores, comillas, saltos de línea o guiones, se encierra entre comillas
        Dim needsQuotes As Boolean = value.Contains("""") OrElse
                                     value.Contains(";") OrElse
                                     value.Contains(",") OrElse
                                     value.Contains(vbCr) OrElse
                                     value.Contains(vbLf) OrElse
                                     value.Contains("-")
     
        ' Escapa las comillas internas duplicándolas (" → "")
        value = value.Replace("""", """""")
     
        If needsQuotes Then
            Return $"""{value}"""
        Else
            Return value
        End If
    End Function
        End Class
    End Namespace