Community discussion forum

export sqlserver table data to a tab delimited tex

  • 4 years ago



    Hi,
     How to export a sqlserver table data using a select query  to a tab delimited Text file Programmatically in a windows VB.NET application.
    Or
     Please some one tell me, how to use the Import/Export Wizard in sqlserver programmatically in .NET application


    Thanks,
    Suman Sajjana
    Hyderabad


  • 4 years ago

    I always use the bcp utility and the xp_cmdshell extended stored procedure in the master database. For example:


    Code:

    exec master..xp_cmdshell 'bcp "select * from table" queryout "C:\path\filename.ext" -c -t -Usa -P'


    This is the Fastest most efficient way to export data from SQL server to a flat file, and works best when the amount of data is large (10,000+ records for example)
    Look up BCP in the Books Online help that comes with SQL server, for more details. You could put this in a stored procedure and call it from ASP.NET using ADO.NET.


    The other option is to use ADO.NET to get a dataset, and then write a textfile using a StreamWriter object. So assuming you had a dataset object named ds you could execute the following:

    Code:

     Dim strLine As String, filePath, fName, fileExcel, link
           Dim objFileStream As FileStream
           Dim objStreamWriter As StreamWriter


           fName = "location\myFile.csv"


           objFileStream = New FileStream(fName, FileMode.Create, FileAccess.Write)
           objStreamWriter = New StreamWriter(objFileStream)



      For i = 0 To ds.Tables(0).Rows.Count - 1
               For j = 0 To ds.Tables(0).Columns.Count - 1
                       strLine = strLine & ds.Tables(0).Rows(i).Item(j).ToString & Chr(9)
               Next
     
                objStreamWriter.WriteLine(strLine) 'You could change this to objStreamWrite.Write(strLine & Chr(13))    for a carriage return
                strLine = ""


      Next


     objStreamWriter.Close()
     objFileStream.Close()



    I stoll this snippet from here:
    http://www.experts-exchange.com/Programming/ProgrammingLanguages/DotNet/ASPDOTNET/Q_21262813.html

Post a reply

Enter your message below

Sign in or Join us (it's free).

Want to stay in touch with what's going on? Follow us on twitter!