RE7 Media Export

Brian Hunter

Hello


Apologies if this is a well trodden path, but I have the dreaded question about exporting embedded media from RE7.


I have seen it mentioned before that it's just not possible, but is this still the case?  Is there no way, in bulk, to extract media from RE7, or is going through each record one-by-one and saving each individual file the only method?  Has anyone had any success with this?

Aaron Rothberg

Hi Brian, yep, that's the only method I'm aware of. If you think about it, Raiser's Edge query and export tools are designed to output data fields. It would be great to have a tool that would let you ask, for example, "Please export all Constituent Ids and then export any files on the media tab", but where would Export store the files? It can't store attachments in individual cells of a CSV. And how would the tool generate a file that indicates which attachment goes with which Id? There would need to be a way for Export to provide everything in a hierarchical zipped format with another application to unpack those files in a meaningful way.


It's a great idea! Sadly, you can't get there from here.

John Heizer

Sadly, Aaron Rothberg‍ is right.  The way the files are stored in the SQL database makes it very difficult to extract them without using the RE user interface.


I'll add a note that Blackbaud does seem to have come up with some sort of automated way to get the Media files out of RE and into the "supplemental" NXT database, but they aren't sharing that with us mere mortals.

Brian Hunter

That's disappointing, but I appreciate the responses.  Saves me wasting time fighting it.


Thanks.

Isaac Comer

For those that aren't hosted, if someone that knows a lot about SQL Server and blob/memo fields and general programming wanted to research it, there might be a way.


I've been able to successfully extract data from the media table, but the catch is it was still just for a single record.  And I needed to edit the data that came out with a hex editor before I could restore the file.


In any case, this is what I had:

using the Windows command line program "bcp" (Bulk Copy Program): 
bcp re7_db.dbo.<name-of-sql-query-no-spaces> OUT myNative.bcp -S <REServerName> -T -n


This extracts the data specified by the query into the file "myNative.bcp".  To convert that file into whatever it used to be, you'll need to edit it (with hex editor) to remove a little bit, then save it back to whatever file type it used to have.


I haven't messed with it enough to know if the number of characters before the start of the file is constant.  


This technique might be able to be built into something that could be used on many files at once.  You would want to have a loop, and temporarily have many queries on the server, each one corresponding to a different row in the Media table.   You would give each outputed .bcp file a name corresponding to that.


The tricky bit is the editing and the guessing the original file type.  If the prefix before the start of the file is constant length, then you could just remove that number of characters every time.  Finally, you could then process the file through Unix's file command to have it tell you what type of file it should be, and add the appropriate file type.

----------------------------------------------

It would be a lot of work to build all that though.  It's weird that Blackbaud doesn't just release some sort of utility to do it.

Dennis Ladnier

This looked somewhat interesting so I spent a few mins with it;


This is the ~sql code to extract the media object into files - the files are named with the RE record ID and the media ID (you can change the select statement, directory location, naming convention to whatever you want) - note: you need to enable ole automation within your sql database (hopefully, you are working in an sql db copy and not your production system)


DECLARE @outPutPath varchar(50) = 'C:\\Blob\\'

select @data=object,@fPath = @outPutPath +cast(parent_id as varchar(15))+'-'+cast(id  as varchar(15))+'.ole' from media where object is not null

    EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT;

    EXEC sp_OASetProperty @init, 'Type', 1; 

    EXEC sp_OAMethod @init, 'Open'; 

    EXEC sp_OAMethod @init, 'Write', NULL, @data;

    EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2;

    EXEC sp_OAMethod @init, 'Close';

    EXEC sp_OADestroy @init;


The result is a sub-dir of media files. Using c#, python, etc you can then extract the header information and rename the file to the specific extension.  Example, you can search for 'Acroexch.Dcoument' in the first ~50chars, if a match you can then discard everything up to the char string '%PDF' - granted this is a very simplistic approach but worked for the 100 or some files I tested which were PDFs.  Am sure there is a pattern for other file types (ex. Word, jpg, etc).

 

Isaac Comer

dennis ladnier:

This looked somewhat interesting so I spent a few mins with it;


This is the ~sql code to extract the media object into files - the files are named with the RE record ID and the media ID (you can change the select statement, directory location, naming convention to whatever you want) - note: you need to enable ole automation within your sql database (hopefully, you are working in an sql db copy and not your production system)


DECLARE @outPutPath varchar(50) = 'C:\\Blob\\'

select @data=object,@fPath = @outPutPath +cast(parent_id as varchar(15))+'-'+cast(id  as varchar(15))+'.ole' from media where object is not null

    EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT;

    EXEC sp_OASetProperty @init, 'Type', 1; 

    EXEC sp_OAMethod @init, 'Open'; 

    EXEC sp_OAMethod @init, 'Write', NULL, @data;

    EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2;

    EXEC sp_OAMethod @init, 'Close';

    EXEC sp_OADestroy @init;


The result is a sub-dir of media files. Using c#, python, etc you can then extract the header information and rename the file to the specific extension.  Example, you can search for 'Acroexch.Dcoument' in the first ~50chars, if a match you can then discard everything up to the char string '%PDF' - granted this is a very simplistic approach but worked for the 100 or some files I tested which were PDFs.  Am sure there is a pattern for other file types (ex. Word, jpg, etc).

 

That's pretty cool!  Thanks for posting it.  (I'll be saving that for if I need it one day.) 


For the pattern part: can confirm-- that's why I mentioned the Unix "file" command.  In part, it works by recognizing a file type identifier that's usually located at the start of the file.  In Unix parlance, these are referred to as "magic numbers" even though they're usually strings or partially strings.