Extract Binary *.doc Files from a table

Question

Extract Binary *.doc Files from a table

mmuhlenkort2000

Grasshopper

Points: 15
More actions
October 18, 2010 at 5:59 am

#141190

We have several thousand documents stored in a customized application which need to be extracted to move to a more robust enterprize content management system. In order to accomplish this I need to extract these documents to a folder then upload to the new system. I've see articles on textcopy but that copies single documents. I need to automate the process to extract all the items from the table.
Any help or guidance is greatly appreciated.
Mark

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

since you mentioned textcopy, here's pair of procedures to help you use it a little easier. I had this saved in my snippets.

In TSQL, you will have to loop thru your table and process each image one at a time.

I think it's easier in a programming language, but both can get the job done.

--**************************************

--

-- Name: Procedure to Export/Import Imag

-- es In/From SQL SERVER

-- Description:the procedure is used as

-- an Interface to the TextCopy Utility sup

-- plied with SQL SERVER in order to simpli

-- fy handling of BLOBS (images/docs)

-- By: Eli Leiba

--

-- Inputs:@runpath varchar(100), -- text

-- Copy Location

@srvrvarchar(50), -- server TO LOAD

@db varchar(50), -- DATABASE TO LOAD

@usrvarchar(50), -- login USER

@pwdvarchar(50), -- login password

@tblvarchar(50), -- TABLE TO load/unload

@colvarchar(50), -- COLUMN TO load/unload

@whrvarchar(200), -- WHERE clause

@filvarchar(100), -- filename including path

@modchar(1) -- I FOR LOAD INTO Sql

--

-- Assumes:-- usage (assuming TextCopy.e

-- xe is in c:\)

CREATE TABLE pic (pic_id int,picture image)

INSERT INTO pic VALUES (1,null)

UPDATE pic SET picture = 'xx'

-- Inserting image

EXEC sp_imp_exp_images 'c:\textCopy.exe',

'PCN1943',

'PUBS',

'sa',

'NotARealPassword',

'pic',

'picture',

'"where pic_id = 1"',

'c:\pic.jpg',

'I'

-- Extracting Image

EXEC sp_imp_exp_images 'c:\textCopy.exe',

'PCN1943',

'PUBS',

'sa',

'NotARealPassword',

'pic',

'picture',

'"where pic_id = 1"',

'D:\pic.jpg',

'O'

--

-- Side Effects:Import/Export of BLOBS

--

--This code is copyrighted and has-- limited warranties.Please see http://

-- www.1SQLStreet.com/xq/ASP/txtCodeId.498/

-- lngWId.5/qx/vb/scripts/ShowCode.htm--for details.--**************************************

--

CREATE PROCEDURE sp_imp_exp_images

(@runpath varchar(100), -- textCopy Location

@srvrvarchar(50), -- server TO LOAD

@db varchar(50), -- DATABASE TO LOAD

@usrvarchar(50), -- login USER

@pwdvarchar(50), -- login password

@tblvarchar(50), -- TABLE TO load/unload

@colvarchar(50), -- COLUMN TO load/unload

@whrvarchar(200), -- WHERE clause

@filvarchar(100), -- filename including path

@modchar(1)) -- I FOR LOAD INTO Sql , O FOR output FROM SQL

AS

DECLARE @cmd varchar(1000)

SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +

' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +

' /F ' + @fil+ ' /' + @mod

EXEC Master..xp_cmdShell @cmd

GO

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!