Creating ASCII Text File

• donle

  SSC-Addicted

  Points: 401

  More actions

  November 14, 2003 at 5:27 pm

  #81554

  Let say that I have a table that contains 2 columns. Column A has "Hello " in it and Column B has "World!". How do I create an ASCII file "MYFILE.TXT" on my C drive to contain the contents of those 2 columns like "Hello World!".

  I know that I have to call xp_shell something but I believe there are more to it than just that. Please help.

  Don

• nigelrivett

  SSCertifiable

  Points: 5472

  More actions

  November 15, 2003 at 2:29 pm

  #482150

  look at bcp in bol

  Other methods

  http://www.nigelrivett.net/WriteTextFile.html

  Cursors never.

  DTS - only when needed and never to control.

  
  Cursors never.
  DTS - only when needed and never to control.
  

• Janssen

  SSC Veteran

  Points: 233

  More actions

  November 17, 2003 at 2:54 am

  #482172

  try

  declare @cmd varchar(255)

  select @cmd = '"echo ' ? @cola ? ' ' ? @colb ? ' > c:\testsql.txt"'

  exec master..xp_cmdshell @cmd , nooutput

  (NB - change the ?'s to concat character ( plus sign! - ) it doesn't show on my screen!)

  This will write the first line of data to a new file. Change the ">" to ">>" to add new lines to the same file.

  How you prime the variables @cola & @colb is down to you. This technique is only suitable for small amounts of data.

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  November 17, 2003 at 1:18 pm

  #482300

  -- A utilitarian SP I wrote a wile back

  -- Drop Procedure SyExport8KBlobToTextFile_sp

  Create Procedure SyExport8KBlobToTextFile_sp

  (@Blob varchar(8000) = NULL

  ,@OutPutFile varchar(256) = NULL)

  as

  if @Blob + @OutPutFile is NULL begin

  print ' ******* Procedure SyExport8KBlobToTextFile_sp *****

  Creates a file with the passed text data

  Parameters:

  @Blob Less than 8K long string of text

  @OutPutFileFull UNC filename of target file.

  '

  return

  end

  -- Real Work Start Here

  Declare @SQL varchar(8000), @Select varchar(8000)

  set @Select = replace(@Blob, '''', '''''')

  set @Select = 'Select ''' + replace(@Select, '"', ''' + char(34) + ''') + ''''

  set @Select = replace(@Select, Char(13) + Char(10), ''' + char(13) + Char(10) + ''')

  set @Select = replace(@Select, Char(13), ''' + char(13) + ''')

  set @Select = replace(@Select, Char(10), '')

  set @Select = replace(@Select, '''', '''''')

  -- print @Select

  set @SQL = 'master.dbo.xp_cmdshell ''BCP "' + @Select + '" QUERYOUT "' + @OutPutFile + '" -c -S"' + @@Servername + '" -a32768 -T -t""'', no_output '

  --print @SQL

  Exec (@SQL)

  
  
  Once you understand the BITs, all the pieces come together