pezzmedina pezzmedina - 3 months ago 16
Vb.net Question

Export to text file from Access missing carriage return at the end of the line

I have a database in Access and I need to export that information into a text file The problem that I have is that it is not exported in the correct format. This is an example of the text file that is been exported:

info|info1|info3|info4|info5|info6|info7|info8| <this is the wrong format
info|info1|info3|info4|info5|info6|info7|info8 <this is the correct format


As you can see, my code is inserting a vertical line at the end of the row instead of a carriage return.

Please see my code and any help will be great:

Dim connetionString As String
Dim cnn As OleDbConnection
connetionString = "connection string.accdb;"
cnn = New OleDbConnection(connetionString)

Dim dtResult As New DataTable
cnn.Open()
'Change the query
Dim dataAdap As New OleDbDataAdapter("SELECT * FROM table", cnn)
dataAdap.Fill(dtResult)
cnn.Close()

'Change the path to your desired path
Dim RUTA As String = "path were i want to put the text file\"
Dim ARCHIVOTXT As String = "filename of the text file.txt"

If Not Directory.Exists(RUTA) Then
Directory.CreateDirectory(RUTA)
End If

Dim writer As New StreamWriter(RUTA + ARCHIVOTXT)
Try
Dim sb As New StringBuilder
For Each row As DataRow In dtResult.Rows
sb = New StringBuilder
For Each col As DataColumn In dtResult.Columns
sb.Append(row(col.ColumnName) & "|")
Next
writer.WriteLine(sb.ToString())
Next
Catch ex As Exception
Throw ex
Finally
If Not writer Is Nothing Then writer.Close()
End Try
MsgBox("Done")
End Sub

Answer

Instead of:

For Each col As DataColumn In dtResult.Columns
        sb.Append(row(col.ColumnName) & "|")
Next
writer.WriteLine(sb.ToString())

You could just use: writer.WriteLine(String.Join("|", row.ItemArray))

String.Join("|", row.ItemArray) concatenates the ToString() result of each value held by the row separating the values with a "|".