locked
Import word document into sql server RRS feed

  • Question

  • how to import a word document (including all the bullets and tables) into a sql server database

     

    thanks!

    Wednesday, December 22, 2010 4:06 PM

Answers

  • You can store word docuemnts into SQL Server Database

    You store those documents into a table with VARBINARY(MAX) Column

    You can either use

    a) Import Export Wizard

     see http://social.msdn.microsoft.com/forums/en-us/sqltools/thread/2686485E-AF60-4F35-A115-8E488AEE157F

    b) SSIS

    c) T-SQL

    -- Declare a variable to store the image data
    DECLARE @Doc AS VARBINARY(MAX)
    
    -- Load the image data
    SELECT @doc = CAST(bulkcolumn AS VARBINARY(MAX))
    FROM OPENROWSET( BULK 'C:\Documents\DocName.doc', SINGLE_BLOB ) AS x 
     
     
    INSERT INTO Documents (ID, Document)
    VALUES (1, @doc )
    
    

     

    d) Application (that can be developed in any supported programmign language like .net, Java, Visual Basic 6 etc.)

    e) If you are storing word docs bigger than 1 MB in size and using SQL 2008 or later you may want to enable filestream property of the varbinary(max) columns....

     

     

    • Marked as answer by bladde89 Wednesday, December 22, 2010 8:35 PM
    Wednesday, December 22, 2010 4:24 PM

All replies

  • You can store word docuemnts into SQL Server Database

    You store those documents into a table with VARBINARY(MAX) Column

    You can either use

    a) Import Export Wizard

     see http://social.msdn.microsoft.com/forums/en-us/sqltools/thread/2686485E-AF60-4F35-A115-8E488AEE157F

    b) SSIS

    c) T-SQL

    -- Declare a variable to store the image data
    DECLARE @Doc AS VARBINARY(MAX)
    
    -- Load the image data
    SELECT @doc = CAST(bulkcolumn AS VARBINARY(MAX))
    FROM OPENROWSET( BULK 'C:\Documents\DocName.doc', SINGLE_BLOB ) AS x 
     
     
    INSERT INTO Documents (ID, Document)
    VALUES (1, @doc )
    
    

     

    d) Application (that can be developed in any supported programmign language like .net, Java, Visual Basic 6 etc.)

    e) If you are storing word docs bigger than 1 MB in size and using SQL 2008 or later you may want to enable filestream property of the varbinary(max) columns....

     

     

    • Marked as answer by bladde89 Wednesday, December 22, 2010 8:35 PM
    Wednesday, December 22, 2010 4:24 PM
  • Thank you works great however i have another question after i insert the data appears in the table as sort of a hex code how do i read it.
    Wednesday, December 22, 2010 8:35 PM
  • You are storing you word doucments as binary data so you are seeing it in hex format inside SSMS

    You need to export those documents out from sql server and save it on the disk as .doc or .docx files

    All above mentioned would work to export documents out from the database

    using vanilla T-SQL only

    DECLARE @Command NVARCHAR(4000) 
    
     
    SET @Command = 'bcp "SELECT documents FROM databasename.dbo.tablename" queryout "C:\documents\mydoc.doc" -T -n -CSHAH\SQL2008' 
    
    -- the following assumes you have xp_cmdshell enabled
    EXEC xp_cmdshell @Command 
    
    
    

     

    Wednesday, December 22, 2010 8:54 PM
  • thanks for the reply however i tried the code but it doesnt seem to work as i cant find the exported file in my document
    Wednesday, December 22, 2010 10:19 PM