locked
Export CSV,TXT RRS feed

  • 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.

    Mull
    Thursday, 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 format

    bcp "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 format

    bcp "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!

    Mull
    Friday, July 3, 2009 6:08 PM