Convert RTF data
-
Anyone know of a way to take rtf data (comes from a table field) and strip out the rtf garbage? we have a 3rd party application that takes from the SQL Db and puts into a Word mail merge but unfortunately the accompaning rtf "stuff" gets put into the word doc too.
-
Word can "Save As" into many formats. Text is one of them. Would that help ?
No doubt someone will have a better idea....I'd hope so.
DB
The systems fine with no users loggged in. Can we keep it that way ?br> -
i've used a quick and dirty vb app that has a RichTextBox control on it. it has a single function; I pass it an ADODB.Field, which it places int eh RichTextBox.RTF, and then returns RichTextBox.Text, which is the contents of the field with the RTF stuff stripped out.
let me know if you need a detailed example, and I can copy and paste a sample form.
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! -
ok i just found an example on another forum that might help:
create function dbo.RTF2TXT(@in varchar(8000)) RETURNS varchar(8000) ASBEGIN
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, '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}')
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! -
I use the same technique for processing rtf.
It is much better to create a new column in your table to store the plain text, so that the client program can insert the plain text for you, before the record is inserted or updated. This is not a good job for SQL Server; object manipulation is not a strong point of T-SQL. I shudder to think of the performance penalty.
Just take a look at how contorted the T-SQL is just to make a simple method call on the object.
Much better to redesign a bit and create the plain text on the client. You would even be able to index and/or search it with ease.
-
Hi Lowell
I have tried to use the function that you posted previously. It is giving error "Property Can not be set" for the statement "EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in".
would you please help me in this.
The OS is Windows XP.
Thanks
Rama Prasad
-
OK I've created a complete solution for this:
I created a vb6 executable which takes command line paramters for RTF_File_In and TXT_File_Out.
it takes the RTF file contents and sends the plain text contents to the designated file;
this can then be called from within SQL Server at will.
Teh vb6 source code,sample file, and sample stored proc are included in the zip file located here:
In my example, i placed everything on the root of the C: drive for testing, you'll need to move them to the proper spot...maybe the \bin folder of SQL server. the stored proc doesn't do much more than open a file quickly and bulk insert it into a table, from which you could then fiddle with it if necessary:
CREATE PROC sp_RTF2TEXT(@RTFFileIn VARCHAR(255),@TXTFileOut VARCHAR(255) )
AS
BEGIN
--usage sp_RTF2TEXT 'c:\test.rtf', 'c:ewfile.txt'
--note that this function will not take kindly to spaces in a filename
SET NOCOUNT ON
DECLARE @COMMAND VARCHAR(1000)
SET @COMMAND = 'c:\FreeRTF2Text.exe ' + @RTFFileIn + ' ' + @TXTFileOut
EXEC master.dbo.xp_cmdshell @COMMAND
CREATE TABLE #tempRTF (line varchar(8000))
EXEC ('bulk INSERT #tempRTF FROM "' + @TXTFileOut + '"')
SELECT * FROM #tempRTF
DROP TABLE #tempRTF
END
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! -
Hi Lowell,
Thanks for your reply.
Actually my rtf stuff is in database already. I need to convert the rtf stuff in database table field to text and then to display on sql reporting services report.
-
oops i hadn't thought of that aspect;
i'll look at it again; in your case, is the RTF stored in a TEXT or an IMAGE field?
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! -
It is in text field
-
Lowell,
Have you had any success with a follow up solution? I have an Oracle Stored proc that does it... I'm not a fundi on T-SQL, perhaps you could convert the code? It is straight Oracle PL/SQL code, just string manipulation...
If you manage it, I would love a copy of the MSQ-SQL stored proc once you're done
Regards,
Adrian
-
If you could send me the proc, i could convert it to TSQL no problem.
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! -
Hi Lowell
This code is really good.
Thanks
-
Can I have a copy of your stored procedure that will convert the RTF field to plain text?. Thanks.
-
My old link from 2005 is broken.... I'll re-find the solution and update.
for a single field, this still works fin on my server:
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, '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 }}')
Results:
Four Score And Seven Years Ago
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!
Viewing 15 posts - 1 through 15 (of 105 total)
You must be logged in to reply to this topic. Login to reply