Store MS Word document in a table

Question

Store MS Word document in a table

R. M. Joseph

Old Hand

Points: 368
More actions
October 21, 2004 at 12:59 am

#86772

I want to store MS Word document in a table.. for that can I use column data type = Ntext ? How to insert and retrieve?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

David.Poole SSC Guru Points: 75994 More actions · Answer 1

Personally I would use the image field type.

You need to write an app that reads the binary representation of the file then use the ADO.Stream object to post the file into the field.

I would add some error checking that limits the size of the document that can be uploaded.

LinkedIn Profile

David Bytheway SSC-Addicted Points: 457 More actions · Answer 2

I agree with David Poole. Don't forget that if you store the file as a "blob" image you will not be storing the file attributes. What I do is to store the attributes, original filename, modification date etc. in columns in the row alongside the "blob" and then regenerate these when unpacking to a file. I also found it handy to create a separate file-group in the database to hold image/text data rather than allow everything to be dumped into 'Primary'. You can control size and occupancy more easily -- blob-data can be very large causing size limits to be exceeded if you're not careful. Also consider compressing the data before storing in the image field. Word documents tend to be byte-hungry and compress quite dramatically.

Hilary Cotter SSCommitted Points: 1861 More actions · Answer 3

It is entirely possible to store the binary format word doc into a text field, but you have to convert the document on a byte by byte basis to a stream of ascii charachters. Then when you convert back, you have to convert these ascii charchaters to binary.

To convert you need to slurp your word doc into the varbinary data type in chuncks, then take that stream which looks like this:

0x730079006E00630020002D007400220061007500740068006F00720073003100220020002D00640022005C005C00480049004C0041005200590032004B0050005C00430024005C00500072006F006700720061006D002000460069006C00650073005C004D006900630072006F0073006F00660074002000530051004C0020005300650072007600650072005C004D005300530051004C005C005200450050004C0044004100540041005C0075006E0063005C00480049004C0041005200590032004B0050005F0070007500620073005F0070007500620073005C00320030003000340031003000320032003000380031003600340030005C0061007500740068006F00720073005F0030002E00620063007000220020002D0068004F005200440045005200280020005B00610075005F00690064005D0020004100530043002900

and convert the this to text. To do this, grab 2 characters at a time, and store convert to its ascii value, and concatenate this to a char stream, and store it as text.

To retrieve it, you have to do the exact opposite.

It can be done, but its a lot of work. So store it in the image datatype as others have suggested. Here is how I stuff it in. This will stuff all files found in the c:htm directory into your database:

Set objConn = CreateObject("ADODB.Connection")

Set objRS = CreateObject("ADODB.RecordSet")

Set objStream=CreateObject("ADODB.Stream")

objConn.Open "Provider=SQLOLEDB;data Source=ServerName;Initial Catalog=DataBaseName;User Id=Account;Password=Password"

Set objFileSystem=createobject("Scripting.FileSystemObject")

Set objDir=objFileSystem.GetFolder("c:\htm")

for each objFile in objDir.Files

count=count+1

wscript.echo objFile.name

objConn.Execute "insert into TextTable (TextCol) values ('jibberish')"

objRs.Open "select textcol from texttable where pk=" &

count, objConn, 1, 3

objStream.Type = 2

objStream.Open

objStream.LoadFromFile objFile.Path

objRs.Fields("TextCol").Value=objStream.ReadText

objRs.Update

objRs.Close

objStream.Close

Set objStream=nothing

Set objShell=nothing

Set objConn=nothing

Set objFileSystem=nothing

Set objDir=nothing