Hi ,
i need a sample code for Export data from MS-SQL to Notepad.txt using vb.net in Pipe line format

this is my sample table data
ID Name Address
1 Meenu Meenuhouse
2 Ammu MeenuHouse
3 Chinnu Meenuhouse

i need output like

1|Meenu|Meenuhouse
2|Ammu|Meenuhouse
3|Chinnu|Meenuhouse

Can any one help me ? i dont know wat to do... plzzzzzzzzzzz

Recommended Answers

What have you tried so far?

Jump to Post

You could do

For Each row As DataRow In dt.Rows

    Dim line As String = ""

    For Each column As DataColumn In dt.Columns
        'Add the Data rows.
        line += "|" & row(column.ColumnName).ToString()
    Next

    'Add new line
    txt += line.SubString(1)  vbCr

Next

If File.Exists(fileloc) Then
    Using sw As StreamWriter = …
Jump to Post

Sorry about that. My previous post had a typo. Try

For Each row As DataRow In dt.Rows

    Dim line As String = ""

    For Each column As DataColumn In dt.Columns
        'Add the Data rows.
        line += "|" & row(column.ColumnName).ToString()
    Next

    'Add new line
    txt += line.SubString(1) & vbCrLf

Next

If …
Jump to Post

What does your output look like with the above code?

Jump to Post

All 11 Replies

What have you tried so far?

commented: please check the below comment.. im sorry im new in diz group... +0

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim constr As String = New SqlConnection("Data Source=Mypc\SQL12;Initial Catalog=AssetMgmt;Persist Security Info=True;User ID=sa;Password=750101").ConnectionString
    Dim fileloc As String = "Computer\SexpoldeX3\FlashDisk\FA\floor.txt"
    ' Dim varCollection As  = Nothing
    'Dim delimiter As String = varCollection("user::Delimiter").value.tostring()
    Using con As New SqlConnection(constr)

        Using cmd As New SqlCommand("SELECT TOP 1000 [Floor_ID] ,[Floor_Name]FROM [AssetMgmt].[dbo].[Floor_Master]")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    sda.Fill(dt)

                    'Build the Text file data.
                    Dim txt As String = String.Empty


                    For Each column As DataColumn In dt.Columns
                        'Add the Header row for Text file.
                        ' txt += column.ColumnName & "|"
                    Next

                    'Add new line.
                    ' txt += vbCr & vbLf

                    For Each row As DataRow In dt.Rows
                        For Each column As DataColumn In dt.Columns
                            'Add the Data rows.
                            txt += row(column.ColumnName).ToString() & "|"

                        Next

                        'Add new line.
                        txt += vbCr & vbLf
                    Next
                    If File.Exists(fileloc) Then
                        Using sw As StreamWriter = New StreamWriter(fileloc)
                            sw.WriteLine(txt)
                        End Using
                    End If

                End Using
            End Using
        End Using
    End Using
End Sub

End Class

this is what i tried ....

1|meenu|meenuhouse|
its output cmng lyk diz.... how i can remove the last "|" ?

You could do

For Each row As DataRow In dt.Rows

    Dim line As String = ""

    For Each column As DataColumn In dt.Columns
        'Add the Data rows.
        line += "|" & row(column.ColumnName).ToString()
    Next

    'Add new line
    txt += line.SubString(1)  vbCr

Next

If File.Exists(fileloc) Then
    Using sw As StreamWriter = New StreamWriter(fileloc)
        sw.WriteLine(txt)
    End Using
End If

That way each new line is built as "|a|b|c". The SubString chops off the leading "|" before it is added to the output buffer. You might want to use StringBuilder instead of String. It's much faster when building strings usinc concatenation.

Thnx fr d help... :)
But still m facing problem

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.IO
Public Class Form2

Private Sub Form2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim constr As String = New SqlConnection("Data Source=amh_htr\SQL12;Initial Catalog=Argtt;Persist Security Info=True;User ID=sa;Password=750101").ConnectionString
    Dim fileloc As String = "D:\floor.txt"
    ' Dim varCollection As  = Nothing
    'Dim delimiter As String = varCollection("user::Delimiter").value.tostring()
    Using con As New SqlConnection(constr)
        'Dim txt As String = String.Empty
        Using cmd As New SqlCommand("SELECT  [Floor_ID],[Floor_Name]FROM [AssetMgmt].[dbo].[Floor_Master]")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As New DataTable()

                    sda.Fill(dt)

                    Dim txt As String = String.Empty
                    Dim line As String = ""
                    For Each row As DataRow In dt.Rows



                        For Each column As DataColumn In dt.Columns
                            'Add the Data rows.
                            line += "|" & row(column.ColumnName)
                        Next
                        'Add new line
                        txt += line.Substring(1) & vbCrLf


                    Next
                    If File.Exists(fileloc) Then
                        Using sw As StreamWriter = New StreamWriter(fileloc)
                            sw.WriteLine(txt)
                        End Using
                    End If
                End Using
            End Using
        End Using
        End Using

End Sub

End Class

Now m gtng output like
1|Ground Floor
1|Ground Floor|2|1st Floor
1|Ground Floor|2|1st Floor|3|2nd Floor
1|Ground Floor|2|1st Floor|3|2nd Floor|4|3rd Floor
1|Ground Floor|2|1st Floor|3|2nd Floor|4|3rd Floor|5|4th Floor
1|Ground Floor|2|1st Floor|3|2nd Floor|4|3rd Floor|5|4th Floor|6|5th Floor

1|Ground Floor
2|1st Floor
3|2nd Floor
3|2nd Floor

i need output in diz format

Sorry about that. My previous post had a typo. Try

For Each row As DataRow In dt.Rows

    Dim line As String = ""

    For Each column As DataColumn In dt.Columns
        'Add the Data rows.
        line += "|" & row(column.ColumnName).ToString()
    Next

    'Add new line
    txt += line.SubString(1) & vbCrLf

Next

If File.Exists(fileloc) Then
    Using sw As StreamWriter = New StreamWriter(fileloc)
        sw.WriteLine(txt)
    End Using
End If

Im geting same output only nothng changed :(

Dim fileloc As String = "D:\floor.txt"

 Dim fileloc As String = "D:\floor.txt"
        ' Dim varCollection As  = Nothing
        'Dim delimiter As String = varCollection("user::Delimiter").value.tostring()
        Using con As New SqlConnection(constr)
            'Dim txt As String = String.Empty
            Using cmd As New SqlCommand(" SELECT  [Floor_ID],[Floor_Name]FROM [AssetMgmt].[dbo].[Floor_Master]")
                ' SELECT  [Floor_ID],[Floor_Name]FROM [AssetMgmt].[dbo].[Floor_Master]")
                Using sda As New SqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd
                    Using dt As New DataTable()

                        sda.Fill(dt)

                        Dim txt As String = String.Empty
                        Dim line As String = ""
                        For Each row As DataRow In dt.Rows
                            'Dim line As String = ""
                            For Each column As DataColumn In dt.Columns
                                'Add the Data rows.
                                line += "|" & row(column.ColumnName).ToString()
                            Next
                            'Add new line
                            txt += line.Substring(1) & vbCrLf
                        Next
                        If File.Exists(fileloc) Then
                            Using sw As StreamWriter = New StreamWriter(fileloc)
                                sw.WriteLine(txt)
                            End Using
                        End If

                    End Using
                End Using
            End Using
        End Using

    End Sub



End Class


    'Dim delimiter As String = varCollection("user::Delimiter").value.tostring()
    Using con As New SqlConnection(constr)
        'Dim txt As String = String.Empty
        Using cmd As New SqlCommand(" SELECT  [Floor_ID],[Floor_Name]FROM [AssetMgmt].[dbo].[Floor_Master]")
            ' SELECT  [Floor_ID],[Floor_Name]FROM [AssetMgmt].[dbo].[Floor_Master]")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As New DataTable()

                    sda.Fill(dt)

                    Dim txt As String = String.Empty
                    Dim line As String = ""
                    For Each row As DataRow In dt.Rows
                        'Dim line As String = ""
                        For Each column As DataColumn In dt.Columns
                            'Add the Data rows.
                            line += "|" & row(column.ColumnName).ToString()
                        Next
                        'Add new line
                        txt += line.Substring(1) & vbCrLf
                    Next
                    If File.Exists(fileloc) Then
                        Using sw As StreamWriter = New StreamWriter(fileloc)
                            sw.WriteLine(txt)
                        End Using
                    End If

                End Using
            End Using
        End Using
    End Using

End Sub

End Class
'can u just chk ma code wats wrong in it? :(

What does your output look like with the above code?

Sir thanx for ur tym n considertaion i gt d solution wid a simple query

Using cmd As New SqlCommand("SELECT (DM_Code + '| ' + DM_Name) FROM [AssetMgmt].[dbo].[Department_Master]")

change the sql statement like this then if u generate d text file
For Each row As DataRow In dt.Rows

                        For Each column As DataColumn In dt.Columns

                            txt += row(column.ColumnName).ToString()
                            'txt += Custodian_ID '|'+ Custodian_Name

                        Next
txt += vbCrLf 



Thanx alott "Reverend Jim"  u really helped alott