Answered by:
Export CSV,TXT

Question
-
Hi,
i want to export tables to csv or txt for our controllers.
I use this:
SELECT * INTO tbl_name FROM tbl_name IN 'c:\' 'Text;' FROM tbl_name
This is a example of sql programming book.
Google found something with BCP. But i dont know anything about BCP.
What is best practices to export result from a sp or some queries?
Many thanks for all answers.
MullThursday, July 2, 2009 6:48 PM
Answers
-
you have to use bcp utility to achieve it
http://msdn.microsoft.com/en-us/library/ms162802.aspx
you can get more info here- Marked as answer by mullfreak Friday, July 3, 2009 6:08 PM
Thursday, July 2, 2009 11:16 PM -
BCP would probably be your best bet. Just run the following examples from the command line.
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Here is example of exporting a table to a CSV formatbcp "Databasename..tbl_name " out "c:\temp\test.csv" -c -t, -Sservername -Uusername -Ppassword
Here is example of exporting a query to a CSV format
bcp "Select top 10 * From Databasename..tbl_name " queryout "c:\temp\test.csv" -c -t, -Sservername -Uusername -Ppassword
nuddy- Marked as answer by mullfreak Friday, July 3, 2009 6:08 PM
Thursday, July 2, 2009 11:18 PM
All replies
-
you have to use bcp utility to achieve it
http://msdn.microsoft.com/en-us/library/ms162802.aspx
you can get more info here- Marked as answer by mullfreak Friday, July 3, 2009 6:08 PM
Thursday, July 2, 2009 11:16 PM -
BCP would probably be your best bet. Just run the following examples from the command line.
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Here is example of exporting a table to a CSV formatbcp "Databasename..tbl_name " out "c:\temp\test.csv" -c -t, -Sservername -Uusername -Ppassword
Here is example of exporting a query to a CSV format
bcp "Select top 10 * From Databasename..tbl_name " queryout "c:\temp\test.csv" -c -t, -Sservername -Uusername -Ppassword
nuddy- Marked as answer by mullfreak Friday, July 3, 2009 6:08 PM
Thursday, July 2, 2009 11:18 PM -
Hi,
this was my code:
Public Sub CreateCSVFile(ByVal dt As DataTable, ByVal strFilePath As String) '#Region "Export Grid to CSV" ' Create the CSV file to which grid data will be exported. Dim sw As New StreamWriter(strFilePath, False) ' First we will write the headers. 'DataTable dt = m_dsProducts.Tables[0]; Dim iColCount As Integer = dt.Columns.Count For i As Integer = 0 To iColCount - 1 sw.Write(dt.Columns(i)) If i < iColCount - 1 Then sw.Write(";") End If Next sw.Write(sw.NewLine) ' Now write all the rows. For Each dr As DataRow In dt.Rows For i As Integer = 0 To iColCount - 1 If Not Convert.IsDBNull(dr(i)) Then sw.Write(dr(i).ToString()) End If If i < iColCount - 1 Then sw.Write(";") End If Next sw.Write(sw.NewLine) Next '#End Region sw.Close() End Sub
Works fine!
Thank you for your help!
MullFriday, July 3, 2009 6:08 PM