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
What have you tried so far?
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
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.