11-07-2023 10:57 AM - last edited on 11-08-2023 03:12 AM by JackLacava
Hi Everyone.
I am creating a CSV file with the information from a datatable. The problem is that it generates the file with the columns but without the datatable information. I wanted to know if there is another way to create a CSV file correctly.
Dim strFilePathFull_AllocationDetails As String = strFilePath & "/" & strModelName & "_" & strScenarioName & "_" & strTimeName & "_" & dtAllocationDetails.TableName & ".csv"
Dim xfeFile_AllocationDetails As XFFileEx = dtbSH.DataTableToFile(si, dtAllocationDetails, strFilePathFull_AllocationDetails, Nothing)
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)
Return xfeFile
End Function
Solved! Go to Solution.
11-08-2023 12:30 PM - edited 11-08-2023 12:31 PM
I suspect your problem might lie in the checks you do with dt.Rows.Count. In a number of situations, that property doesn't work as expected. I would suggest to just loop on rows; if you want to enforce your "limit", keep track of iterations and then break out:
' it's bad practice to mess with stuff passed as parameter, let's wrap it...
Dim realLimit as Integer = -1
if limit > 0 then realLimit = limit
' keep an iteration index
Dim curr As Integer = 0
' and we're off
For Each row As DataRow In dt.Rows
If curr >= realLimit Then
Exit For
Else
curr += 1
End If
' ... turn your row into a string etc etc
next
The other den of dragons is that check on column names, which might be case sensitive or fail on minimal differences. At a minimum, I would turn that ColumnName to all lowercase or all uppercase, and ensure the elements in that list have had the same treatment.
Beyond that, I would check that your datatable actually contains data, particularly if it came from some .Clone() operation or similar.
Hope that helps!
11-07-2023 12:45 PM
Check out the solutions here: Solved: Saving an export directly to the shared folder - OneStream Community (onestreamsoftware.com)
11-07-2023 01:17 PM
I had already seen the post, but that doesn't solve the problem of the row values not appearing, only the column values are displayed.
11-08-2023 03:15 AM
If the file is created, and it contains something, then the code you posted has done its job.
What has probably not done its job, is the "Me.PrintDataTable" function you have there. You might want to post the content of that function. To verify, you can write that strDt variable to errorlog to see what it actually contains.
11-08-2023 10:42 AM
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
This is the code
11-08-2023 12:30 PM - edited 11-08-2023 12:31 PM
I suspect your problem might lie in the checks you do with dt.Rows.Count. In a number of situations, that property doesn't work as expected. I would suggest to just loop on rows; if you want to enforce your "limit", keep track of iterations and then break out:
' it's bad practice to mess with stuff passed as parameter, let's wrap it...
Dim realLimit as Integer = -1
if limit > 0 then realLimit = limit
' keep an iteration index
Dim curr As Integer = 0
' and we're off
For Each row As DataRow In dt.Rows
If curr >= realLimit Then
Exit For
Else
curr += 1
End If
' ... turn your row into a string etc etc
next
The other den of dragons is that check on column names, which might be case sensitive or fail on minimal differences. At a minimum, I would turn that ColumnName to all lowercase or all uppercase, and ensure the elements in that list have had the same treatment.
Beyond that, I would check that your datatable actually contains data, particularly if it came from some .Clone() operation or similar.
Hope that helps!