Convert RTF data
-
I also have the same situation. When I try the dbo.RTF2TXT function it returns Null. Can someone tell me where I can download a copy of VB to install to see If I can overcome this issue?
Thanks for your help.
Tom
-
tom.goehring (1/19/2009)
I also have the same situation. When I try the dbo.RTF2TXT function it returns Null. Can someone tell me where I can download a copy of VB to install to see If I can overcome this issue?Thanks for your help.
Tom
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Hi SSCrazy,
I have SqlServer2008 i have try doing this, but it returns me result as NULL
please help.
Your help would be really a great job.
here is my code:
create function dbo.RTF2TXT(@in varchar(8000)) RETURNS varchar(8000) AS
BEGIN
DECLARE @object int
DECLARE @hr int
DECLARE @out varchar(8000)
-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT
--EXEC @hr = sp_OASetProperty @object, 'RTF', @in
EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in
EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT
EXEC @hr = sp_OADestroy @object
return @out
END
GO
select dbo.RTF2TXT('{\rtf1\ansi\ansicpg1252\uc1\deff0\stshfdbch0\stshfloch0\stshfhich0\stshfbi0\deflang1033\deflangfe1033{\fonttbl{\f0\froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f42\froman\fcharset238\fprq2 Times New Roman CE;}
{\f43\froman\fcharset204\fprq2 Times New Roman Cyr;}{\f45\froman\fcharset161\fprq2 Times New Roman Greek;}{\f46\froman\fcharset162\fprq2 Times New Roman Tur;}{\f47\froman\fcharset177\fprq2 Times New Roman (Hebrew);}
{\f48\froman\fcharset178\fprq2 Times New Roman (Arabic);}{\f49\froman\fcharset186\fprq2 Times New Roman Baltic;}{\f50\froman\fcharset163\fprq2 Times New Roman (Vietnamese);}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;
\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;
\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \fs24\lang1033\langfe1033\cgrid\langnp1033\langfenp1033 \snext0 Normal;}{\*\cs10 \additive \ssemihidden
Default Paragraph Font;}{\*\ts11\tsrowd\trftsWidthB3\trpaddl108\trpaddr108\trpaddfl3\trpaddft3\trpaddfb3\trpaddfr3\tblind0\tblindtype3\tscellwidthfts0\tsvertalt\tsbrdrt\tsbrdrl\tsbrdrb\tsbrdrr\tsbrdrdgl\tsbrdrdgr\tsbrdrh\tsbrdrv
\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \fs20\lang1024\langfe1024\cgrid\langnp1024\langfenp1024 \snext11 \ssemihidden Normal Table;}}{\*\rsidtbl \rsid2189369\rsid3043591\rsid3308915\rsid4929009\rsid7289738
\rsid11422153\rsid13314608\rsid14579482}{\*\generator Microsoft Word 10.0.6835;}{\info{\title Four Score And Seven Years Ago\''85}{\author lizaguirre}{\operator lizaguirre}{\creatim\yr2007\mo10\dy24\hr11\min47}{\revtim\yr2007\mo10\dy24\hr11\min47}
{\version1}{\edmins0}{ofpages1}{ofwords4}{ofchars28}{\*\company HDS}{ofcharsws31}{\vern16393}{\*\password 00000000}}{\*\xmlnstbl }\paperw12240\paperh15840\margl720\margr720\margt720\margb720\gutter0
\widowctrl\ftnbj\aenddoc\grfdocevents0oxlattoyen\expshrtnoultrlspc\dntblnsbdbospaceforul\formshade\horzdoc\dgmargin\dghspace187\dgvspace180\dghorigin720\dgvorigin720\dghshow1\dgvshow2
\jexpand\viewkind1\viewscale100\pgbrdrhead\pgbrdrfoot\splytwnine\ftnlytwnine\htmautspolnhtadjtbl\useltbaln\alntblind\lytcalctblwd\lyttblrtgr\lnbrkruleobrkwrptbl\snaptogridincell\allowfieldendsel\wrppunct\asianbrkrule\rsidroot3308915 \fet0
{\*\wgrffmtfilter 013f}\sectd \linex0\endnhere\sectlinegrid360\sectdefaultcl\sectrsid2189369\sftnbj {\*\pnseclvl1\pnucrm\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl2\pnucltr\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl3
\pndec\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl4\pnlcltr\pnstart1\pnindent720\pnhang {\pntxta )}}{\*\pnseclvl5\pndec\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl6\pnlcltr\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}
{\*\pnseclvl7\pnlcrm\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl8\pnlcltr\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl9\pnlcrm\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}\pard\plain
\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \fs24\lang1033\langfe1033\cgrid\langnp1033\langfenp1033 {\insrsid7289738 Four Score And Seven Years Ago\''85}{\insrsid11422153
\par }}')
Regards,
Fida
-
Thanks Lowell. You function works like a charm
-
Oleg I'm glad you found a solution here. this thread started back in 2005, and has produced a lot of great ideas and solutions. people find it and it often works out for them.
My idea for the webservice seems to work the best for me, because I was getting a LOT of rtf docs that were greater than 8000 chars, so I needed a different solution.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell
The Webservice solution sounds like a great idea. I have some decent knowledge of Sqlserver 2005 but have just minimal knowledge of the other pieces such as vs2005, IIS, and Webservices. Is there any I could get more detailed steps to complete this solution. such as:
1) Download your webservice solution.
2) Install VS2005, (please including what pieces of VS2005 to install how do I compile your solution)
3) Install IIS (please include with what pieces to install and configure)
4) How do I create a webservice?
5) How do I connect the rtf procedure to the webservice?
6) How do I call the webservice from Sqlserver.
7) anything else that may be of use?
I do have a Windows Admin available who can install stuff but he is not a developer at all.
If this is too much hand holding, feel free to respond accordingly.
Thanks,
Tom
-
wow i could make an article on SSC over this...lots of questions below got my thoughts flowing.
VB6 can be used, but it was l8imited to 8000 chars due to the parameters in sp_OACreate
With SQL2005, i thought, oh yeah, varchar(max) will so fix this issue.
This process really made me angry at one point. I figured that like VB6, i could make a CLR that would be a function that would accept text, stick it in a RichTextControl, and return the .Text property. very simple.
problem: a SQL CLR does not allow any Windows.Forms objects. damn! so you cannot (easily) use the built in functionality available to convert RTF to text.
Instead you have to do a roundablout way ...a Web Service can use the RichTextBox control, and SQL can use a CLR to pass data back and fourth to the web service.
tom.goehring (6/16/2009)
LowellThe Webservice solution sounds like a great idea. I have some decent knowledge of Sqlserver 2005 but have just minimal knowledge of the other pieces such as vs2005, IIS, and Webservices. Is there any I could get more detailed steps to complete this solution. such as:
1) Download your webservice solution.
it's on like page 6 or 7 of this thread:Download The Web Service Converter
2) Install VS2005, (please including what pieces of VS2005 to install how do I compile your solution)
no need to install Visual Studio; deployment of a web service is incredibly easy...you make a virtual folder and drop a single file/web page in it.
3) Install IIS (please include with what pieces to install and configure)
4) How do I create a webservice?
I'll flesh out the exact procedure with screenshots and files this afternoon; at work i don't have access to everything i need for this. it's a lot easier to have a step by step with pics than to try and follow text.
5) How do I connect the rtf procedure to the webservice?
6) How do I call the webservice from Sqlserver.
a very simple CLR function that you pass the raw rtf text as a varchar(max), returns a varchar(max of plain text.
7) anything else that may be of use?
I do have a Windows Admin available who can install stuff but he is not a developer at all.
If this is too much hand holding, feel free to respond accordingly.
Thanks,
Tom
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (6/16/2009)
wow i could make an article on SSC over this...lots of questions below got my thoughts flowing.Sounds like a good idea, Lowell. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
RBarryYoung (6/16/2009)
Lowell (6/16/2009)
wow i could make an article on SSC over this...lots of questions below got my thoughts flowing.Sounds like a good idea, Lowell. 🙂
I second that.... this issue really had us going until our web developer came up with this solution... he calls the webservice though directly from another wep page, but the fact that it could be called from within a CLR makes this much more interesting....
-
Lowell
Did you ever get a chance to create the procedure for the steps below:
3) Install IIS (please include with what pieces to install and configure)
4) How do I create a webservice?
I'll flesh out the exact procedure with screenshots and files this afternoon; at work i don't have access to everything i need for this. it's a lot easier to have a step by step with pics than to try and follow text.
5) How do I connect the rtf procedure to the webservice?
6) How do I call the webservice from Sqlserver.
a very simple CLR function that you pass the raw rtf text as a varchar(max), returns a varchar(max of plain text.
7) anything else that may be of use?
Thanks,
Tom
-
tom.goehring (7/29/2009)
LowellDid you ever get a chance to create the procedure for the steps below:
3) Install IIS (please include with what pieces to install and configure)
4) How do I create a webservice?
I'm sorry, i forgot all about this.
the web service was the easiest part.
in IIS, this is optional, but I created a new virtual folder named "Conversions"
and dropped this zip file contents in it:(here is a link to the zip of the full project itself)
once it is in place, simply go to your localhost to confirm that the ConversionService.asmx works/renders... it looks different when coming from localhost vs an internet/ip address...there are built in test features from localhost
http://localhost/Conversions/ConversionService.asmx
you can copy or rename the .asmx page if you'd like, and I know i made "default.asmx the highest default page if someone just goes to the /Conversions folder.
I'm at work right now, let me see if i can remote access to get the CLR project, and slap together some instructions for getting that peice to work.
I'll try and follow up with another post, but this gets the web portion in place for the CLR to call.
I'll flesh out the exact procedure with screenshots and files this afternoon; at work i don't have access to everything i need for this. it's a lot easier to have a step by step with pics than to try and follow text.
5) How do I connect the rtf procedure to the webservice?
6) How do I call the webservice from Sqlserver.
a very simple CLR function that you pass the raw rtf text as a varchar(max), returns a varchar(max of plain text.
7) anything else that may be of use?
Thanks,
Tom
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
For those who still experice the "NULL" Issue, here is the step you would do:
1. create the function.
2. allow OLE:
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
3.Download http://activex.microsoft.com/controls/vb6/richtx32.cab
and use a unzip program to extract it. you will see two files:
RichTx32.inf
RichTx32.Ocx
Just right click the .inf and select install. this will copy it to c:\windows\system32.
now use command line, CMD, go to system32 folder and run
regsvr32 RichTx32.Ocx
You will see Dll register successful.
4. now go back to SQL and test:
select dbo.RTF2TXT('{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Courier New;}} {\colortbl ;\red0\green0\blue0;} {\*\generator Riched20 12.0.6211.1000;}\viewkind4\uc1 \pard\cf1\f0\fs20 You got it now!\par \par Ext: 1234\par }
')
-
Hi,
Firstable : sorry for my Englsh !
I have a server under Win 2008 R2 64 bits with SQL Server 2008 R2
With the code below I always have the NULL value.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = 'RTF2TXT')
DROP FUNCTION RTF2TXT
GO
CREATE FUNCTION RTF2TXT(@in varchar(8000)) RETURNS varchar(8000) AS
BEGIN
DECLARE @object int
DECLARE @hr int
DECLARE @out varchar(8000)
-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'RichTextLib.RichTextBox', @object OUT
--EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT
EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in
EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT
EXEC @hr = sp_OADestroy @object
RETURN @out
END
GO
select dbo.RTF2TXT('{\rtf1\ansi\ansicpg1252\uc1 aaa}') AS TEST
I don't have VB is installed on this server (in appereance) but I have "Visual Studio 2008 Shell" and "Visual Studio Tools For Application" installed on it.
Must i install a VB version ? If yes : where can I download it (version ?) ?
Thanks a lot for your help.
Best regards
Alf
-
you can use script component and use the following code :
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Windows.Forms
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Try
Dim enc As New System.Text.UTF8Encoding()
Dim RTFConvert As RichTextBox = New RichTextBox
RTFConvert.Rtf = enc.GetString(Row.Body.GetBlobData(0, Convert.ToInt32(Row.Body.Length)))
Row.converted.ResetBlobData()
Row.converted.AddBlobData(System.Text.Encoding.UTF8.GetBytes(RTFConvert.Text))
Catch E As Exception
End Try
End Sub
End Class
Viewing 15 posts - 91 through 105 (of 105 total)
You must be logged in to reply to this topic. Login to reply