locked
How to convert Task notes data from RTF to plain Text format in Project Server 2016? RRS feed

  • Question

  • Hi,

    I'm trying to read Task notes data, but the data was stored in some binary format. (FYI, the notes data will be entered by Team members while submitting their task update from the Time sheets)

    I ran the below query to convert the notes data  to plain Text.

     SELECT
           [TASK_UID]
          ,CONVERT(VARCHAR(MAX),[TASK_RTF_NOTES],0) AS NOTES
          ,[TASK_NOTES]
      FROM [WSS_Content_PWA_PSP].[pjpub].[MSP_TASKS] 
      WHERE TASK_HAS_NOTES=1

    Output:

     {\rtf1\ansi\ansicpg1252\deff0\nouicompat\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}{\f1\fnil\fcharset2 Symbol;}}
    {\*\generator Riched20 16.0.4639}\viewkind4\uc1 
    \pard{\pntext\f1\'B7\tab}{\*\pn\pnlvlblt\pnf1\pnindent0{\pntxtb\'B7}}\fi-256\li256\f0\fs17 [Raj Kumar Allepu]\par

    \pard This is my first comment\par
    \par
    \par

    \pard{\pntext\f1\'B7\tab}{\*\pn\pnlvlblt\pnf1\pnindent0{\pntxtb\'B7}}\fi-256\li256 [Raj Kumar Allepu]\par

    \pard This is my 2nd note  comment\par
    \par

    The above output has so much unwanted data. Please let me know if there is any way to remove the unwanted data.

    Any help would be very much appreciated.

    Thanks.


    Wednesday, February 7, 2018 4:30 AM

Answers

  • Thank you paul for your reply..

    Have achieved it using the below function. Firstly, have created a function to convert RTF to Plain Text, then I called this function from the select query.

    Function:


    CREATE FUNCTION [dbo].[CONVERT_RTF2Text]
    (
        @rtf nvarchar(max)
    )
    RETURNS nvarchar(max)
    AS
    BEGIN
        DECLARE @Pos1 int;
        DECLARE @Pos2 int;
        DECLARE @hex varchar(316);
        DECLARE @Stage table
        (
            [Char] char(1),
            [Pos] int
        );

        INSERT @Stage
            (
               [Char]
             , [Pos]
            )
        SELECT SUBSTRING(@rtf, [Number], 1)
             , [Number]
          FROM [master]..[spt_values]
         WHERE ([Type] = 'p')
           AND (SUBSTRING(@rtf, Number, 1) IN ('{', '}'));

        SELECT @Pos1 = MIN([Pos])
             , @Pos2 = MAX([Pos])
          FROM @Stage;

        DELETE
          FROM @Stage
         WHERE ([Pos] IN (@Pos1, @Pos2));

        WHILE (1 = 1)
            BEGIN
                SELECT TOP 1 @Pos1 = s1.[Pos]
                     , @Pos2 = s2.[Pos]
                  FROM @Stage s1
                    INNER JOIN @Stage s2 ON s2.[Pos] > s1.[Pos]
                 WHERE (s1.[Char] = '{')
                   AND (s2.[Char] = '}')
                ORDER BY s2.[Pos] - s1.[Pos];

                IF @@ROWCOUNT = 0
                    BREAK

                DELETE
                  FROM @Stage
                 WHERE ([Pos] IN (@Pos1, @Pos2));

                UPDATE @Stage
                   SET [Pos] = [Pos] - @Pos2 + @Pos1 - 1
                 WHERE ([Pos] > @Pos2);

                SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '');
            END

        SET @rtf = REPLACE(@rtf, '\pard', '');
        SET @rtf = REPLACE(@rtf, '\par', '');
        SET @rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), '');

        WHILE (Right(@rtf, 1) IN (' ', CHAR(13), CHAR(10), '}'))
          BEGIN
            SELECT @rtf = SUBSTRING(@rtf, 1, (LEN(@rtf + 'x') - 2));
            IF LEN(@rtf) = 0 BREAK
          END
        
        SET @Pos1 = CHARINDEX('\''', @rtf);

        WHILE @Pos1 > 0
            BEGIN
                IF @Pos1 > 0
                    BEGIN
                        SET @hex = '0x' + SUBSTRING(@rtf, @Pos1 + 2, 2);
                        SET @rtf = REPLACE(@rtf, SUBSTRING(@rtf, @Pos1, 4),
    CHAR(CONVERT(int, CONVERT (binary(1), @hex,1))));
                        SET @Pos1 = CHARINDEX('\''', @rtf);
                    END
            END

        SET @rtf = @rtf + ' ';

        SET @Pos1 = PATINDEX('%\%[0123456789][\ ]%', @rtf);

        WHILE @Pos1 > 0
            BEGIN
                SET @Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1);

                IF @Pos2 < @Pos1
                    SET @Pos2 = CHARINDEX('\', @rtf, @Pos1 + 1);

                IF @Pos2 < @Pos1
                    BEGIN
                        SET @rtf = SUBSTRING(@rtf, 1, @Pos1 - 1);
                        SET @Pos1 = 0;
                    END
                ELSE
                    BEGIN
                        SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '');
                        SET @Pos1 = PATINDEX('%\%[0123456789][\ ]%', @rtf);
                    END
            END

        IF RIGHT(@rtf, 1) = ' '
            SET @rtf = SUBSTRING(@rtf, 1, LEN(@rtf));

        RETURN @rtf;
    END

    ==================

    Select Query :

    SELECT  
    [dbo].[CONVERT_RTF2Text](CAST([TASK_RTF_NOTES] AS VARCHAR(max))) AS TaskNotes,
    TASK_NOTES AS TaskNotesFromMPP,
    TASK_UID
    FROM    [pjpub].[MSP_EpmTask]  WHERE TASK_HAS_NOTES=1  

    It might be helpful for some one.


    • Edited by Rajkumar Allepu Thursday, February 8, 2018 4:08 AM
    • Proposed as answer by Agasthya.S.P Thursday, February 8, 2018 4:36 AM
    • Marked as answer by PWMatherMVP Thursday, February 8, 2018 9:52 AM
    Thursday, February 8, 2018 4:06 AM

All replies

  • Hello,

    Here is an old example when using SSRS: https://blogs.msdn.microsoft.com/chrisfie/2008/06/04/how-to-display-microsoft-project-tasks-notes-field-in-a-report/

    Please do note, it is not supported to directly query the published schema using T-SQL, you should be using the API to access this data.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, February 7, 2018 9:24 AM
  • Thank you paul for your reply..

    Have achieved it using the below function. Firstly, have created a function to convert RTF to Plain Text, then I called this function from the select query.

    Function:


    CREATE FUNCTION [dbo].[CONVERT_RTF2Text]
    (
        @rtf nvarchar(max)
    )
    RETURNS nvarchar(max)
    AS
    BEGIN
        DECLARE @Pos1 int;
        DECLARE @Pos2 int;
        DECLARE @hex varchar(316);
        DECLARE @Stage table
        (
            [Char] char(1),
            [Pos] int
        );

        INSERT @Stage
            (
               [Char]
             , [Pos]
            )
        SELECT SUBSTRING(@rtf, [Number], 1)
             , [Number]
          FROM [master]..[spt_values]
         WHERE ([Type] = 'p')
           AND (SUBSTRING(@rtf, Number, 1) IN ('{', '}'));

        SELECT @Pos1 = MIN([Pos])
             , @Pos2 = MAX([Pos])
          FROM @Stage;

        DELETE
          FROM @Stage
         WHERE ([Pos] IN (@Pos1, @Pos2));

        WHILE (1 = 1)
            BEGIN
                SELECT TOP 1 @Pos1 = s1.[Pos]
                     , @Pos2 = s2.[Pos]
                  FROM @Stage s1
                    INNER JOIN @Stage s2 ON s2.[Pos] > s1.[Pos]
                 WHERE (s1.[Char] = '{')
                   AND (s2.[Char] = '}')
                ORDER BY s2.[Pos] - s1.[Pos];

                IF @@ROWCOUNT = 0
                    BREAK

                DELETE
                  FROM @Stage
                 WHERE ([Pos] IN (@Pos1, @Pos2));

                UPDATE @Stage
                   SET [Pos] = [Pos] - @Pos2 + @Pos1 - 1
                 WHERE ([Pos] > @Pos2);

                SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '');
            END

        SET @rtf = REPLACE(@rtf, '\pard', '');
        SET @rtf = REPLACE(@rtf, '\par', '');
        SET @rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), '');

        WHILE (Right(@rtf, 1) IN (' ', CHAR(13), CHAR(10), '}'))
          BEGIN
            SELECT @rtf = SUBSTRING(@rtf, 1, (LEN(@rtf + 'x') - 2));
            IF LEN(@rtf) = 0 BREAK
          END
        
        SET @Pos1 = CHARINDEX('\''', @rtf);

        WHILE @Pos1 > 0
            BEGIN
                IF @Pos1 > 0
                    BEGIN
                        SET @hex = '0x' + SUBSTRING(@rtf, @Pos1 + 2, 2);
                        SET @rtf = REPLACE(@rtf, SUBSTRING(@rtf, @Pos1, 4),
    CHAR(CONVERT(int, CONVERT (binary(1), @hex,1))));
                        SET @Pos1 = CHARINDEX('\''', @rtf);
                    END
            END

        SET @rtf = @rtf + ' ';

        SET @Pos1 = PATINDEX('%\%[0123456789][\ ]%', @rtf);

        WHILE @Pos1 > 0
            BEGIN
                SET @Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1);

                IF @Pos2 < @Pos1
                    SET @Pos2 = CHARINDEX('\', @rtf, @Pos1 + 1);

                IF @Pos2 < @Pos1
                    BEGIN
                        SET @rtf = SUBSTRING(@rtf, 1, @Pos1 - 1);
                        SET @Pos1 = 0;
                    END
                ELSE
                    BEGIN
                        SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '');
                        SET @Pos1 = PATINDEX('%\%[0123456789][\ ]%', @rtf);
                    END
            END

        IF RIGHT(@rtf, 1) = ' '
            SET @rtf = SUBSTRING(@rtf, 1, LEN(@rtf));

        RETURN @rtf;
    END

    ==================

    Select Query :

    SELECT  
    [dbo].[CONVERT_RTF2Text](CAST([TASK_RTF_NOTES] AS VARCHAR(max))) AS TaskNotes,
    TASK_NOTES AS TaskNotesFromMPP,
    TASK_UID
    FROM    [pjpub].[MSP_EpmTask]  WHERE TASK_HAS_NOTES=1  

    It might be helpful for some one.


    • Edited by Rajkumar Allepu Thursday, February 8, 2018 4:08 AM
    • Proposed as answer by Agasthya.S.P Thursday, February 8, 2018 4:36 AM
    • Marked as answer by PWMatherMVP Thursday, February 8, 2018 9:52 AM
    Thursday, February 8, 2018 4:06 AM
  • Good to hear you have sorted it - just be aware that it is not really supported to query the published schema directly with T-SQL, you should be using the APIs.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Thursday, February 8, 2018 9:53 AM