Retrieve 'Text file' from a table

I've "text file" stored in a column of a table in SQL Server database. How do I retrieve the text file from the database.

more ▼

asked Sep 21, 2011 at 08:50 AM in Default

avatar image

11 1 1 1

@sritej What is the data type of the column storing the text file? If it is varchar(max) or nvarchar(max) and the file is a plain text file then you can see the whole text when you issue a select statement including the column in question. If the file is not a plain text file and/or the data type of the column is varbinary(max) then please elaborate on your requirement a bit. Do you want to just read the contents or you need to read the contents and store them on the disk somewhere?

Sep 21, 2011 at 09:17 AM Oleg
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If I understand you right, you have a table in which there is a column that is either TEXT, NTEXT, VARCHAR(MAX) or NVARCHAR(MAX). You want to save the value in every row to a separate file.

The obvious way of doing this is to use a PowerShell script, especially if you wish to save on the 'client' rather than the Server.

If you need to extract the text as files on the server, as part of an archiving perhaps, or to do a file-based process, there are a variety of different ways. I'd recommend using PowerShell, again, but it depends on the circumstances, and the systems that you are most familiar with.

If you need to do it in TSQL via BCP, then that's possible. If each row represents a file, then I think you'll have to get each 'text file' value from the table and then execute this procedure to save it to disk, supplying the file path for each row. Here is a general procedure to write out a VARCHAR(MAX) to disk as a file. (taken from my article The TSQL of text files . I suspect that there is a much cleaner way of doing this in your case since the text file is already a row in a permanent table, and my routine is for a variable; but I haven't the time to write it (and test it!).

 IF OBJECT_ID (N'dbo.spSaveTextToFile') IS NOT NULL
    DROP PROCEDURE dbo.spSaveTextToFile
   @TheString VARCHAR(MAX),
   @Filename VARCHAR(255),
   @Unicode INT=0
   DECLARE @MySpecialTempTable VARCHAR(255)
   DECLARE @Command NVARCHAR(4000)

 --firstly we create a global temp table with a unique name
   SELECT  @MySpecialTempTable = '##temp'
    + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
 --then we create it using dynamic SQL, & insert a single row
 --in it with the MAX Varchar stocked with the string we want
   SELECT  @Command = 'create table ['
    + @MySpecialTempTable
    + '] (MyID int identity(1,1), Bulkcol varchar(MAX))
 insert into ['
    + @MySpecialTempTable
    + '](BulkCol) select @TheString'
   EXECUTE sp_ExecuteSQL @command, N'@TheString varchar(MAX)',

     --then we execute the BCP to save the file
   SELECT  @Command = 'bcp "select BulkCol from ['
       + @MySpecialTempTable + ']'
       + '" queryout '
       + @Filename + ' '
      + CASE WHEN @Unicode=0 THEN '-c' ELSE '-w' END
       + ' -T -S' + @@servername
   EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT
   EXECUTE ( 'Drop table ' + @MySpecialTempTable )
   RETURN @result

more ▼

answered Sep 23, 2011 at 12:26 AM

avatar image

Phil Factor
4.2k 8 22 20

  • excellent attempt suggestion, let's hope it's close to what the question was asking for...

Sep 23, 2011 at 02:10 AM Fatherjack ♦♦

Sorry I didn't post my question very clearly. I'm new to SQL Server and working on a task to extract files that are stored in SQL Server table and FTP all the files to Vendor.

Table SELECT [ID],[AccountID],[BillDt],[BillType],[FileName],[FileExt],[FileCreateDt],[FileModifiedDt],[FileSize],[EntryDt],[EnteredBy],[ModifiedDt],[ModifiedBy],[BillImage]

FROM [Eclips].[dbo].[tblBill]

Last column 'BillImage' datatype is declared as (Image,null). Depending on the value in column FileExt (file extension) data in 'BillImage' can be a 'txt', 'afp' or 'htm' file. I first want to extract all the txt and html files from 'BillImage' Column and store them to a folder. When I extract it I'm getting the files in Binary format. Is there any way of retrieving and exporting the image files in their original format (text and htm) so that I don't have to writing a conversion program?

Sep 26, 2011 at 06:12 AM sritej

You could use cast of your image column to varchar(max), but there is a small twist. The image data type (which by the way will be deprecated in future versions of SQL Server) cannot be directly converted to varchar. It first needs to be converted to varbinary(max). For example, if you want your BillImage to show the values in the varchar format then you can use this:

    cast(cast([BillImage] as 
        varbinary(max)) as varchar(max)) [BillImage]
    FROM [Eclips].[dbo].[tblBill];
Sep 26, 2011 at 08:31 AM Oleg

There is a great article on Simple-talk by 'Pop Rivett', called 'Pop Rivett and the FTP Directory' http://www.simple-talk.com/sql/t-sql-programming/pop-rivett-and-the-ftp-directory/ but that tells you how to import into SQL Server, rather than export. However the two processes are remarkably similar. With Oleg's suggestion, my stored procedure, and Pop Rivett's code you should be home and dry. Let us know if you need further help with this.

Sep 26, 2011 at 09:10 AM Phil Factor

@Oleg. That's great advice. I had no idea it was that convoluted!

Sep 26, 2011 at 09:11 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

The following BCP utility worked for the issue I posted earlier. The only issue pending is getting the timestamp from BillDt column. For formatting purpose we're inserting @@@ on all header records but what we noticed is that the BillDt (timestamp) is not displayed properly (see below the results from bcp utility). So, How do I get the BillDt in the correct format?

BCP Utility:

C:\>bcp "SELECT TOP 5 '@@@', [BillDt], [FileName], [FileExt], [BillImage] FROM [Eclips].[dbo].[tblBill] WHERE BillType in ('ITBP2007', 'CURRENT_MONTH_DEMAND' )" queryout C:\Temp\eClips\eClipsdata.dat -N -S SQLSERVER01\TEST -T

Results from BCP utility: @ @ @ ?ž H 9 9 9 9 - 9 9 9 9 - 9 9 2 0 1 0 - 1 2 - 0 1 I T B P 2 0 0 7 . h t m H T M öu
@ @ @ !ž H 9 9 9 9 - 9 9 9 9 - 9 9 2 0 1 0 - 1 1 - 0 1 I T B P 2 0 0 7 . t x t T X T u
@ @ @ å J 0 0 3 0 - 0 6 7 3 - 0 1 C U R R E N T M O N T H _ D E M A N D . T X T T X T :[

more ▼

answered Sep 28, 2011 at 04:23 PM

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 21, 2011 at 08:50 AM

Seen: 2388 times

Last Updated: Sep 21, 2011 at 08:50 AM

Copyright 2016 Redgate Software. Privacy Policy