Forum Discussion

Marco's avatar
Marco
Contributor II
2 years ago

Create CSV FILE

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 th...
Admin
Spreadsheet
  • JackLacava's avatar
    JackLacava
    2 years ago

    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!

Marco's avatar
Marco
Contributor II
 

 

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

 

JackLacava's avatar
JackLacava
Honored Contributor
2 years ago

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!