Community discussion forum

export sqlserver table data to a tab delimited tex

• • Reply

  4 years ago

  by SumanSajjana

  Suman Sajjana

  Hyderabad, India, India

  Joined 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
  

  
  

  Report abuse
• • Reply

  4 years ago

  by hollystyles

  Holly Styles

  Colchester, England, United Kingdom

  Joined 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

  Report abuse