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

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.

Answer 1

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

Answer 2

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.

Answer 3

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