Appending a txt file through tsql

• psmith-1145748

  Old Hand

  Points: 310

  More actions

  August 19, 2009 at 10:25 am

  #209622

  i can add text to the bottom of a text file but not at the top, please help.

  using this method:

  --OPEN FILE SPECIFIED BY @FileName AND RETRIEVE @FileID LINK

  EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1

  IF @OLEResult <> 0

  BEGIN

  PRINT 'Error while executing method OpenTextFile'

  RETURN -1

  END

  --APPEND @Text1 TO FILE

  EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1

  IF @OLEResult <> 0

  BEGIN

  PRINT 'Error while executing method WriteLine'

  RETURN -1

  END

• Lowell

  SSC Guru

  Points: 323526

  More actions

  August 19, 2009 at 11:32 am

  #1041748

  there's no direct way to append to the top of a file;

  you have to do it in this order:

  1.create a new file. with your Filesystem object

  2.write the new stuff to the file

  3.close the file

  4.append the old file with the COPY function( copy file1 + file2 + file3) see copy /? from the command prompt for the syntax.

  5. delete the old file. (or save the file as to overwrite it)

  6. rename the new file to the old file name

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• psmith-1145748

  Old Hand

  Points: 310

  More actions

  August 20, 2009 at 4:53 am

  #1042020

  Many thanks for the tip, I used the following:

  xp_cmdshell 'echo Hello World > c:\Filename1.txt' ----to create file

  xp_cmdshell 'echo Hello Again > c:\Filename2.txt' ----to create file

  xp_cmdshell 'copy /B c:\Filename1.txt + c:\Filename2.txt c:newFilename' ----to copy both files in to one file (note: /A = ASCII, /B = Binary)

  xp_cmdshell 'del c:\Filename1.txt c:\Filename2.txt' ------to delete the two unwanted files.

  Job Done, thanks