CODEWITHSUNDEEP
vb.netms-accesstext-files
Show Man
Show Man

Reputation: 9

Import data from MS Access to a Textfile

I'm trying to import some data into a Textfile from MS Access. The Access database has 3 columns. Student Name, Assignment Name and Marks Scored. But when I do the following code the data is like this:

John Cena
John Cena Assignment Submission System
JohnCenaMNUAssignment Submission System75
JohnCenaMNUAssignment Submission System75
Mark
MarkGussing Game
MarkGussing Game80
MarkGussing Game80

I want to have some space between the Student Name, Assignment Name and Marks Scored. Moreover an extra line between the two student information. This is the code I tried but it doesn't gave the desired results:

Private Sub DataTableToTXT()

    Dim connetionString As String
    Dim cnn As OleDbConnection
    connetionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=.\\reportcard.accdb;Persist Security Info=False"
    cnn = New OleDbConnection(connetionString)

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

    'Change the path to your desired path
    Dim exportPath As String = "D:"
    Dim exportFileName As String = "data.txt"



    Dim writer As New StreamWriter(exportPath + exportFileName)
    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))
                writer.WriteLine(sb.ToString())
            Next
            writer.WriteLine(sb.ToString())
        Next
    Catch ex As Exception
        Throw ex
    Finally
        If Not writer Is Nothing Then writer.Close()
    End Try

End Sub

Upvotes: 0

Views: 45

Answers (1)

Steve
Steve

Reputation: 216358

You should remove the WriteLine inside the inner loop. That line will print progressively the content of the StringBuilder while you loop on the columns. You need just one WriteLine after you have completed the inner loop

Dim writer As New StreamWriter(exportPath + exportFileName)
Try
    Dim sb As 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))
            sb.Append(" ") ' The space between columns values
            ' REMOVE this line
            ' writer.WriteLine(sb.ToString())
        Next
        writer.WriteLine(sb.ToString())
    Next

However, if your table doesn't contain too much data, you could avoid to write a line at every row and accumulate all your output in the StringBuilder and write everything at the end of the loop on the rows

    Dim sb As New StringBuilder

    For Each row As DataRow In dtResult.Rows
        For Each col As DataColumn In dtResult.Columns
            sb.Append(row(col.ColumnName))
            sb.Append(" ")
        Next
        sb.AppendLine()
    Next

    ' Just one write at the end of the rows loop
    writer.WriteLine(sb.ToString())

This should also be faster because you access the File System just one time instead of n times (where n is the number of rows). Of course the real benefit could be negligible if the amount of rows is small.

Finally, probably the best approach involves using string.Join and the ItemArray property of a row. This will remove the inner loop on the columns and it is a lot clearer 

    Dim sb As New StringBuilder

    For Each row As DataRow In dtResult.Rows
        sb.AppendLine(string.Join(" ", row.ItemArray))
    Next
    writer.WriteLine(sb.ToString())

Upvotes: 2

Related Questions