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

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  
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.