locked
How to store formatted text RRS feed

  • Question

  • Hi,

    I want to take extracts from Word document and store them with their formatting in a SQL database so I created a table with an nvarchar(max) field and pasted the following formatted data into it. 

     
    •	This is the header
    And this is the information . . . And this is the information . . . And this is the information . . . 
     this is the information . . . And this is the information . . . And this is the information . . . 
    And this is the information . . . And this is the information . . . And this is the information . . . 
    And this is the 
    

    On retrieval the bullet is correctly shown but the carriage return/line feed is not so that all the subsequent lines runs together. I imagine I'm missing something very simple but any guidance will be welcomed.
    Wednesday, December 9, 2009 4:07 PM

Answers

  • Sean and Marcos,

    Thanks for this - looks as if I just stay with my existing solution of storing the path link to the document.

     
    • Marked as answer by Craigton Thursday, December 10, 2009 6:35 PM
    Thursday, December 10, 2009 4:56 PM

All replies

  • can you tell me where you are displaying the text, If you are displaying the text in web page

    Try with carriage return using \n i.e. while storing you separate the string with \n.
    Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.
    Wednesday, December 9, 2009 7:18 PM
  • Thanks for this. I want to display the Word extract complete with formatting in a web page. 
    Thursday, December 10, 2009 9:58 AM
  • Craigton,

    Word uses markup in it's files to set formatting options such as size, font, color, etc, like rtf. SSMS will strip any formatting other than whitespace when you paste into it. The same thing applies when you query using SSMS, you'll get plain text with whitespace.


    Hope this helps,
    Sean
    Thursday, December 10, 2009 12:11 PM
    Answerer
  • Sean,

    Thanks for this. I'm looking to understand whether there is a way of storing and being able to retrieve formatted text. I know I can use path references to an rtf file but that seems very cumbersome.

    Thursday, December 10, 2009 12:56 PM
  • Craigton,

    This can be done without storing paths. What would need to happen is to get the data including markup from the file, and save that raw data into the column. When you retrieve it, the raw data would have to be interpreted by something that knew how to handle it.

    For example:

    I created a small word document that just says "Hello". Below is part of the document (raw):

    ¤×8ãAÈO1~ÛëÝqé ‚Ãk

    6<A%³ç5}Þ*‰`Ë-· k®’‰Œ–I)_:õƒ%ß1ÔÙœIs Ò

    É`ü C]9N°ë{#k¢VEÄWaI_ù¨¸òrai†¢æ€N_UZBÛ_£…è%¤Dž[S´+´Ûë?ª#áÆ@ú[Ü.zÒ9Ž>$N{9›êÍ+P9Y ¢†vuÇG D²ìÃÆoR€"wàͳ¶

    ÌIÊŠ~‰‰˜8›ïWòZè""V0}¿˜ûßÀ»„´ù">þÁŒýuQwH oî·á&#0;&#0;&#0;ÿÿ&#0;PK&#0;&#0;

    This data along with all the rest in the file would need to be saved to a column (nchar/nvarchar). When you would like to show the document to the user you would have to query the data back out and pump it to something that understood all of the markup.

    Hope this helps,
    Sean

    Thursday, December 10, 2009 2:16 PM
    Answerer
  • Hi,

    Why don't you just import the Word Document to a binary column?

    Take Care!
    MarcosGalvani
    Thursday, December 10, 2009 3:55 PM
  • Sean,

    Thanks for this but my question remains  - what can I use to store and retrieve formatted text in a SQL database. 

    I had previously done a cut and paste from a word document and saw what is stored in the nvarchar(max) field and have seen how it is displayed after retrieval.

    I'm seeking to determine whether there's a simple mechanism that will let me do the insert/retrieval of the formatted text.
    Thursday, December 10, 2009 4:06 PM
  • Craigton,

    Again, the column will hold the formatted text, that's not the problem. Again, cut and paste isn't going to work as SSMS ignores everything but whitespace.

    There is a simple mechanism, SELECT and INSERT. As noted by Marcos you can store it as a BLOB, nvarchar, or as a pointer to a file on a server's filesystem. SSMS has NO text formatting interpretation and will not do what you want it to do as SSMS is just a client tool. If you want to store the ACTUAL document INSIDE of the database you'll have to write a front end application to get the results back in some form of readable manner in the case of BLOB and NVARCHAR.

    You can't copy and paste a word document with formatting into SSMS (well you can but all formatting save for whitespace and unicode chatacers would be lost).

    Hope this helps,
    -Sean
    Thursday, December 10, 2009 4:22 PM
    Answerer
  • Sean and Marcos,

    Thanks for this - looks as if I just stay with my existing solution of storing the path link to the document.

     
    • Marked as answer by Craigton Thursday, December 10, 2009 6:35 PM
    Thursday, December 10, 2009 4:56 PM
  • Craigton,

    Personally I like this way the best. I haven't used the new FILESTREAM at all, but this might be what you are looking for?

    I can say that I wasn't a big proponent of storing BLOBs in a database. That's the job of the file system and it does it's job well so I say keep it there. Others will argue this point, this is just my personal opinion. I could see the use for editing remote documents, etc, but it seems a very high cost of overhead as you'll have to build a GUI to interface with it whichever way you choose. Again, use the right tool for the job - it might not be the answer this time but different situations require different tools.

    Good luck in your endeavors,
    Sean
    Thursday, December 10, 2009 6:25 PM
    Answerer