Link to home
Start Free TrialLog in
Avatar of aarons34
aarons34

asked on

How to write a tab delimited file from a dataset in VB.NET

Hi Experts,

I asked a question on how to transfer tab delimited files into a dataset and eventually into a SQL server table (refer to https://www.experts-exchange.com/questions/21002060/Text-file-management-in-using-VB-NET-and-SQL-server.html?query=writing+tab+delimited+file&topics=866 )

The accepeted solution works like a charm =)

What I need now is the inverse process. I need to write the same tab delimited file from the SQL server table. I am able to run the query, create the dataset, etc.. What I need is how to write it into a tab delimited file. I used to do it in MS Access with Docmd.Outputtext function. Is there a similar way in VB.NET?

Thanks

Avatar of Svetlin_Panayotov
Svetlin_Panayotov

Public Sub Export(ByVal path As String, ByVal table As DataTable)
Dim output As New StreamWriter(path, False, UnicodeEncoding.Default)
Dim delim As String

' Write out the header row
delim = ""
For Each col As DataColumn In table.Columns
output.Write(delim)
output.Write(col.ColumnName)
delim = ","
Next
output.WriteLine()

' write out each data row
For Each row As DataRow In table.Rows
delim = ""
For Each value As Object In row.ItemArray
output.Write(delim)
If TypeOf value Is String Then
output.Write(""""c) ' thats four double quotes and a c
output.Write(value)

output.Write(""""c) ' thats four double quotes and a c
Else
output.Write(value)
End If
delim = ","
Next
output.WriteLine()
Next

output.Close()

End Sub
Well I fear there won't be any method in either DataTabel or dataset to directly create a Tab delimated file, so you need to code it.

here's a function that will do it for you

    Private Sub CreateTabDelimatedFile(ByVal DT As DataTable, ByVal FileName As String)
        Dim DR As DataRow
        Dim ColCt As Integer
        Dim OPString As String
        Dim SW As StreamWriter = New StreamWriter(FileName)

        For Each DR In DT.Rows
            OPString = ""
            For ColCt = 0 To DT.Columns.Count - 1
                If OPString <> "" Then OPString = OPString & vbTab
                OPString = OPString & DR(ColCt)
            Next
            SW.WriteLine(OPString)
        Next
        SW.Close()
    End Sub

you need to call it like
CreateTabDelimatedFile(YourDataTable, "C:\MyLog.txt")


However, it could have been best if you shifted to XML
ASKER CERTIFIED SOLUTION
Avatar of iboutchkine
iboutchkine

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of aarons34

ASKER

Thank you all for your contributions. iboutchkine solution was exactly what I needed

Thanks!