Hello NG!
We would like to convert an Column which contain XMLs to an XML Column.
Actually it ist formatted as plain Text in a big Table with approx. 300.000
Lines.
Trying to convert the Type of the Column to XML does not succed because of a
TimeoutError everytime we try it.
Do someone has an idea how we can solve this Problem?
Thank xou very much
MarkusHave you tried exporting/splitting your table into say, 3 or 6 equal parts
as physical temp tables, convert your field into XML there, and then
re-import into the orginal table? You might need to drop some FKs to do
this.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Markus Nistim" <manistim@.hotmail.com> wrote in message
news:%23ZqoniMNIHA.3516@.TK2MSFTNGP02.phx.gbl...
> Hello NG!
> We would like to convert an Column which contain XMLs to an XML Column.
> Actually it ist formatted as plain Text in a big Table with approx.
> 300.000 Lines.
> Trying to convert the Type of the Column to XML does not succed because of
> a TimeoutError everytime we try it.
> Do someone has an idea how we can solve this Problem?
> Thank xou very much
> Markus
>
Showing posts with label plain. Show all posts
Showing posts with label plain. Show all posts
Thursday, March 22, 2012
Wednesday, March 7, 2012
Convert Seconds to Time
Alright... i have a field Browse_Time which is in seconds... just a plain
numeric value... such as 1664 which in the real world would be
approximately 28 minutes how do I convert seconds to hours:minutes' I am
clueless...Carlos Rapa wrote:
> Alright... i have a field Browse_Time which is in seconds... just a
> plain numeric value... such as 1664 which in the real world would
> be approximately 28 minutes how do I convert seconds to
> hours:minutes' I am clueless...
Hi Carlos
use the code below in customCode
Public Function TimeString(Seconds As Long, Optional Verbose _
As Boolean = False) As String
'if verbose = false, returns
'something like
'02:22.08
'if true, returns
'2 hours, 22 minutes, and 8 seconds
Dim lHrs As Long
Dim lMinutes As Long
Dim lSeconds As Long
lSeconds = Seconds
lHrs = Int(lSeconds / 3600)
lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
lSeconds = Int(lSeconds Mod 60)
Dim sAns As String
If lSeconds = 60 Then
lMinutes = lMinutes + 1
lSeconds = 0
End If
If lMinutes = 60 Then
lMinutes = 0
lHrs = lHrs + 1
End If
sAns = Format(CStr(lHrs), "#####0") & ":" & _
Format(CStr(lMinutes), "00") & "." & _
Format(CStr(lSeconds), "00")
If Verbose Then sAns = TimeStringtoEnglish(sAns)
TimeString = sAns
End Function
Private Function TimeStringtoEnglish(sTimeString As String) _
As String
Dim sAns As String
Dim sHour, sMin As String, sSec As String
Dim iTemp As Integer, sTemp As String
Dim iPos As Integer
iPos = InStr(sTimeString, ":") - 1
sHour = Left$(sTimeString, iPos)
If CLng(sHour) <> 0 Then
sAns = CLng(sHour) & " hour"
If CLng(sHour) > 1 Then sAns = sAns & "s"
sAns = sAns & ", "
End If
sMin = Mid$(sTimeString, iPos + 2, 2)
iTemp = sMin
If sMin = "00" Then
sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
Else
sTemp = IIf(iTemp = 1, " minute", " minutes")
sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
sAns = sAns & Format$(iTemp, "##") & sTemp
End If
iTemp = Val(Right$(sTimeString, 2))
sSec = Format$(iTemp, "#0")
sAns = sAns & sSec & " second"
If iTemp <> 1 Then sAns = sAns & "s"
TimeStringtoEnglish = sAns
End Function
* Source www.freevbcode.com*
regards
Frank|||I feel kind of dumb but where is the customCode area? is it where I right
click on the field click properties? and towards the right hand side there is
Custom: with the button and blank textbox? if so I put it in there and
the field just comes up with the formula...
"Frank Matthiesen" wrote:
> Carlos Rapa wrote:
> > Alright... i have a field Browse_Time which is in seconds... just a
> > plain numeric value... such as 1664 which in the real world would
> > be approximately 28 minutes how do I convert seconds to
> > hours:minutes' I am clueless...
>
> Hi Carlos
> use the code below in customCode
> Public Function TimeString(Seconds As Long, Optional Verbose _
> As Boolean = False) As String
> 'if verbose = false, returns
> 'something like
> '02:22.08
> 'if true, returns
> '2 hours, 22 minutes, and 8 seconds
> Dim lHrs As Long
> Dim lMinutes As Long
> Dim lSeconds As Long
> lSeconds = Seconds
> lHrs = Int(lSeconds / 3600)
> lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
> lSeconds = Int(lSeconds Mod 60)
> Dim sAns As String
>
> If lSeconds = 60 Then
> lMinutes = lMinutes + 1
> lSeconds = 0
> End If
> If lMinutes = 60 Then
> lMinutes = 0
> lHrs = lHrs + 1
> End If
> sAns = Format(CStr(lHrs), "#####0") & ":" & _
> Format(CStr(lMinutes), "00") & "." & _
> Format(CStr(lSeconds), "00")
> If Verbose Then sAns = TimeStringtoEnglish(sAns)
> TimeString = sAns
> End Function
> Private Function TimeStringtoEnglish(sTimeString As String) _
> As String
> Dim sAns As String
> Dim sHour, sMin As String, sSec As String
> Dim iTemp As Integer, sTemp As String
> Dim iPos As Integer
> iPos = InStr(sTimeString, ":") - 1
> sHour = Left$(sTimeString, iPos)
> If CLng(sHour) <> 0 Then
> sAns = CLng(sHour) & " hour"
> If CLng(sHour) > 1 Then sAns = sAns & "s"
> sAns = sAns & ", "
> End If
> sMin = Mid$(sTimeString, iPos + 2, 2)
> iTemp = sMin
> If sMin = "00" Then
> sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
> Else
> sTemp = IIf(iTemp = 1, " minute", " minutes")
> sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
> sAns = sAns & Format$(iTemp, "##") & sTemp
> End If
> iTemp = Val(Right$(sTimeString, 2))
> sSec = Format$(iTemp, "#0")
> sAns = sAns & sSec & " second"
> If iTemp <> 1 Then sAns = sAns & "s"
> TimeStringtoEnglish = sAns
> End Function
> * Source www.freevbcode.com*
> regards
> Frank
>
>|||Alright I figured out where the customcode is but unfortunately i keep getting
####0:00.0 as a result...
i have the field set up as
code.TimeString(Sum(Fields!browse_time.Value))
are there anyother settings i am forgetting'
Thanks a bunch,
Carlos
"Frank Matthiesen" wrote:
> Carlos Rapa wrote:
> > Alright... i have a field Browse_Time which is in seconds... just a
> > plain numeric value... such as 1664 which in the real world would
> > be approximately 28 minutes how do I convert seconds to
> > hours:minutes' I am clueless...
>
> Hi Carlos
> use the code below in customCode
> Public Function TimeString(Seconds As Long, Optional Verbose _
> As Boolean = False) As String
> 'if verbose = false, returns
> 'something like
> '02:22.08
> 'if true, returns
> '2 hours, 22 minutes, and 8 seconds
> Dim lHrs As Long
> Dim lMinutes As Long
> Dim lSeconds As Long
> lSeconds = Seconds
> lHrs = Int(lSeconds / 3600)
> lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
> lSeconds = Int(lSeconds Mod 60)
> Dim sAns As String
>
> If lSeconds = 60 Then
> lMinutes = lMinutes + 1
> lSeconds = 0
> End If
> If lMinutes = 60 Then
> lMinutes = 0
> lHrs = lHrs + 1
> End If
> sAns = Format(CStr(lHrs), "#####0") & ":" & _
> Format(CStr(lMinutes), "00") & "." & _
> Format(CStr(lSeconds), "00")
> If Verbose Then sAns = TimeStringtoEnglish(sAns)
> TimeString = sAns
> End Function
> Private Function TimeStringtoEnglish(sTimeString As String) _
> As String
> Dim sAns As String
> Dim sHour, sMin As String, sSec As String
> Dim iTemp As Integer, sTemp As String
> Dim iPos As Integer
> iPos = InStr(sTimeString, ":") - 1
> sHour = Left$(sTimeString, iPos)
> If CLng(sHour) <> 0 Then
> sAns = CLng(sHour) & " hour"
> If CLng(sHour) > 1 Then sAns = sAns & "s"
> sAns = sAns & ", "
> End If
> sMin = Mid$(sTimeString, iPos + 2, 2)
> iTemp = sMin
> If sMin = "00" Then
> sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
> Else
> sTemp = IIf(iTemp = 1, " minute", " minutes")
> sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
> sAns = sAns & Format$(iTemp, "##") & sTemp
> End If
> iTemp = Val(Right$(sTimeString, 2))
> sSec = Format$(iTemp, "#0")
> sAns = sAns & sSec & " second"
> If iTemp <> 1 Then sAns = sAns & "s"
> TimeStringtoEnglish = sAns
> End Function
> * Source www.freevbcode.com*
> regards
> Frank
>
>|||Carlos Rapa wrote:
> code.TimeString(Sum(Fields!browse_time.Value))
> are there anyother settings i am forgetting'
Don't know...are the results ok?
regards
Frank
www.xax.de|||You could use the TimeSpan structure:
=new TimeSpan(0, 0, Fields!Browse_Time.Value).Minutes & ":" & new
TimeSpan(0, 0, Fields!Browse_Time.Value).Seconds
See also:
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemTimeSpanClassTopic.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carlos Rapa" <Carlos Rapa@.discussions.microsoft.com> wrote in message
news:FF418AD5-D687-456A-ACA6-BE9D8FD49670@.microsoft.com...
> Alright... i have a field Browse_Time which is in seconds... just a plain
> numeric value... such as 1664 which in the real world would be
> approximately 28 minutes how do I convert seconds to hours:minutes' I
am
> clueless...
numeric value... such as 1664 which in the real world would be
approximately 28 minutes how do I convert seconds to hours:minutes' I am
clueless...Carlos Rapa wrote:
> Alright... i have a field Browse_Time which is in seconds... just a
> plain numeric value... such as 1664 which in the real world would
> be approximately 28 minutes how do I convert seconds to
> hours:minutes' I am clueless...
Hi Carlos
use the code below in customCode
Public Function TimeString(Seconds As Long, Optional Verbose _
As Boolean = False) As String
'if verbose = false, returns
'something like
'02:22.08
'if true, returns
'2 hours, 22 minutes, and 8 seconds
Dim lHrs As Long
Dim lMinutes As Long
Dim lSeconds As Long
lSeconds = Seconds
lHrs = Int(lSeconds / 3600)
lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
lSeconds = Int(lSeconds Mod 60)
Dim sAns As String
If lSeconds = 60 Then
lMinutes = lMinutes + 1
lSeconds = 0
End If
If lMinutes = 60 Then
lMinutes = 0
lHrs = lHrs + 1
End If
sAns = Format(CStr(lHrs), "#####0") & ":" & _
Format(CStr(lMinutes), "00") & "." & _
Format(CStr(lSeconds), "00")
If Verbose Then sAns = TimeStringtoEnglish(sAns)
TimeString = sAns
End Function
Private Function TimeStringtoEnglish(sTimeString As String) _
As String
Dim sAns As String
Dim sHour, sMin As String, sSec As String
Dim iTemp As Integer, sTemp As String
Dim iPos As Integer
iPos = InStr(sTimeString, ":") - 1
sHour = Left$(sTimeString, iPos)
If CLng(sHour) <> 0 Then
sAns = CLng(sHour) & " hour"
If CLng(sHour) > 1 Then sAns = sAns & "s"
sAns = sAns & ", "
End If
sMin = Mid$(sTimeString, iPos + 2, 2)
iTemp = sMin
If sMin = "00" Then
sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
Else
sTemp = IIf(iTemp = 1, " minute", " minutes")
sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
sAns = sAns & Format$(iTemp, "##") & sTemp
End If
iTemp = Val(Right$(sTimeString, 2))
sSec = Format$(iTemp, "#0")
sAns = sAns & sSec & " second"
If iTemp <> 1 Then sAns = sAns & "s"
TimeStringtoEnglish = sAns
End Function
* Source www.freevbcode.com*
regards
Frank|||I feel kind of dumb but where is the customCode area? is it where I right
click on the field click properties? and towards the right hand side there is
Custom: with the button and blank textbox? if so I put it in there and
the field just comes up with the formula...
"Frank Matthiesen" wrote:
> Carlos Rapa wrote:
> > Alright... i have a field Browse_Time which is in seconds... just a
> > plain numeric value... such as 1664 which in the real world would
> > be approximately 28 minutes how do I convert seconds to
> > hours:minutes' I am clueless...
>
> Hi Carlos
> use the code below in customCode
> Public Function TimeString(Seconds As Long, Optional Verbose _
> As Boolean = False) As String
> 'if verbose = false, returns
> 'something like
> '02:22.08
> 'if true, returns
> '2 hours, 22 minutes, and 8 seconds
> Dim lHrs As Long
> Dim lMinutes As Long
> Dim lSeconds As Long
> lSeconds = Seconds
> lHrs = Int(lSeconds / 3600)
> lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
> lSeconds = Int(lSeconds Mod 60)
> Dim sAns As String
>
> If lSeconds = 60 Then
> lMinutes = lMinutes + 1
> lSeconds = 0
> End If
> If lMinutes = 60 Then
> lMinutes = 0
> lHrs = lHrs + 1
> End If
> sAns = Format(CStr(lHrs), "#####0") & ":" & _
> Format(CStr(lMinutes), "00") & "." & _
> Format(CStr(lSeconds), "00")
> If Verbose Then sAns = TimeStringtoEnglish(sAns)
> TimeString = sAns
> End Function
> Private Function TimeStringtoEnglish(sTimeString As String) _
> As String
> Dim sAns As String
> Dim sHour, sMin As String, sSec As String
> Dim iTemp As Integer, sTemp As String
> Dim iPos As Integer
> iPos = InStr(sTimeString, ":") - 1
> sHour = Left$(sTimeString, iPos)
> If CLng(sHour) <> 0 Then
> sAns = CLng(sHour) & " hour"
> If CLng(sHour) > 1 Then sAns = sAns & "s"
> sAns = sAns & ", "
> End If
> sMin = Mid$(sTimeString, iPos + 2, 2)
> iTemp = sMin
> If sMin = "00" Then
> sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
> Else
> sTemp = IIf(iTemp = 1, " minute", " minutes")
> sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
> sAns = sAns & Format$(iTemp, "##") & sTemp
> End If
> iTemp = Val(Right$(sTimeString, 2))
> sSec = Format$(iTemp, "#0")
> sAns = sAns & sSec & " second"
> If iTemp <> 1 Then sAns = sAns & "s"
> TimeStringtoEnglish = sAns
> End Function
> * Source www.freevbcode.com*
> regards
> Frank
>
>|||Alright I figured out where the customcode is but unfortunately i keep getting
####0:00.0 as a result...
i have the field set up as
code.TimeString(Sum(Fields!browse_time.Value))
are there anyother settings i am forgetting'
Thanks a bunch,
Carlos
"Frank Matthiesen" wrote:
> Carlos Rapa wrote:
> > Alright... i have a field Browse_Time which is in seconds... just a
> > plain numeric value... such as 1664 which in the real world would
> > be approximately 28 minutes how do I convert seconds to
> > hours:minutes' I am clueless...
>
> Hi Carlos
> use the code below in customCode
> Public Function TimeString(Seconds As Long, Optional Verbose _
> As Boolean = False) As String
> 'if verbose = false, returns
> 'something like
> '02:22.08
> 'if true, returns
> '2 hours, 22 minutes, and 8 seconds
> Dim lHrs As Long
> Dim lMinutes As Long
> Dim lSeconds As Long
> lSeconds = Seconds
> lHrs = Int(lSeconds / 3600)
> lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
> lSeconds = Int(lSeconds Mod 60)
> Dim sAns As String
>
> If lSeconds = 60 Then
> lMinutes = lMinutes + 1
> lSeconds = 0
> End If
> If lMinutes = 60 Then
> lMinutes = 0
> lHrs = lHrs + 1
> End If
> sAns = Format(CStr(lHrs), "#####0") & ":" & _
> Format(CStr(lMinutes), "00") & "." & _
> Format(CStr(lSeconds), "00")
> If Verbose Then sAns = TimeStringtoEnglish(sAns)
> TimeString = sAns
> End Function
> Private Function TimeStringtoEnglish(sTimeString As String) _
> As String
> Dim sAns As String
> Dim sHour, sMin As String, sSec As String
> Dim iTemp As Integer, sTemp As String
> Dim iPos As Integer
> iPos = InStr(sTimeString, ":") - 1
> sHour = Left$(sTimeString, iPos)
> If CLng(sHour) <> 0 Then
> sAns = CLng(sHour) & " hour"
> If CLng(sHour) > 1 Then sAns = sAns & "s"
> sAns = sAns & ", "
> End If
> sMin = Mid$(sTimeString, iPos + 2, 2)
> iTemp = sMin
> If sMin = "00" Then
> sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
> Else
> sTemp = IIf(iTemp = 1, " minute", " minutes")
> sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
> sAns = sAns & Format$(iTemp, "##") & sTemp
> End If
> iTemp = Val(Right$(sTimeString, 2))
> sSec = Format$(iTemp, "#0")
> sAns = sAns & sSec & " second"
> If iTemp <> 1 Then sAns = sAns & "s"
> TimeStringtoEnglish = sAns
> End Function
> * Source www.freevbcode.com*
> regards
> Frank
>
>|||Carlos Rapa wrote:
> code.TimeString(Sum(Fields!browse_time.Value))
> are there anyother settings i am forgetting'
Don't know...are the results ok?
regards
Frank
www.xax.de|||You could use the TimeSpan structure:
=new TimeSpan(0, 0, Fields!Browse_Time.Value).Minutes & ":" & new
TimeSpan(0, 0, Fields!Browse_Time.Value).Seconds
See also:
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemTimeSpanClassTopic.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carlos Rapa" <Carlos Rapa@.discussions.microsoft.com> wrote in message
news:FF418AD5-D687-456A-ACA6-BE9D8FD49670@.microsoft.com...
> Alright... i have a field Browse_Time which is in seconds... just a plain
> numeric value... such as 1664 which in the real world would be
> approximately 28 minutes how do I convert seconds to hours:minutes' I
am
> clueless...
Convert RTF to plain text in sql
Hi All,
Can anyone tell me how to convert RTF formatted data into plain text?
I have a column in sql server 2005 table which stores the rtf data. I
want to convert and store this data as plain text in another table.
So then I can display this data in my SSRS 2005 report.
Thanks,RTF data is plain text which has some pieces of the text marked up by
XML tags. There are a couple of versions of RTF, and the type of RTF
data you have will be marked by the first XML tag you see in your RTF
document. RTF also allows authors to create custom (non-standard) RTF
tags which may appear in your RTF data.
Below appears the core code we use to extract text from RTF files. It
is written in VB.NET 1.1
'RTFreader is a customized .NET binary reader that has a method added
to it to
'parse out text from an rtf file. Uses same methods and properties as
binary
'reader does.
Public Class RTFreader
Inherits System.IO.BinaryReader
Const openCurlyBrace As System.Byte = Asc("{")
Const closedCurlyBrace As System.Byte = Asc("}")
Const openRoundBrace As System.Byte = Asc("(")
Const closedRoundBrace As System.Byte = Asc(")")
Const doubleQuote As System.Byte = 34
Const backSlash As System.Byte = Asc("\")
Const hyphen As System.Byte = Asc("-")
Const space As System.Byte = Asc(" ")
Const asterisk As System.Byte = Asc("*")
Const apostroph As System.Byte = Asc("'")
Const semicolon As System.Byte = Asc(";")
Const nullSpace As System.Byte = 0
Const tab As System.Byte = 9
Const linefeed As System.Byte = 10
Const carriageReturn As System.Byte = 13
Const forwardSlash As System.Byte = Asc("/")
Const pipe As System.Byte = Asc("|")
Const underscore As System.Byte = Asc("_")
Const tilde As System.Byte = Asc("~")
Const lessThan As System.Byte = Asc("<")
Const greaterThan As System.Byte = Asc(">")
'CONSTRUCTOR
Sub New(ByVal st As System.IO.Stream)
MyBase.New(st)
End Sub
'READTEXT
'strips tabs,carriage returns, line feeds, and non-alphabetic
characters and returns everything else
Public Function ReadText(ByVal count As System.Int32, ByRef
strError As System.String, Optional ByVal keyword As System.String ="") As System.String
Dim result As System.String = "OK"
Dim text As System.String = ""
Dim prevchar As Byte = 0
Dim l As System.Int32 = 0
Dim p As System.Int32 = 0
Dim prev As System.Byte = 0
Try
Dim rtfData() As Byte = MyBase.ReadBytes(count)
l = rtfData.Length() - 1
Dim i As System.Int32 = 0
'parse the rtfData and copy characters out of it into
result
For i = 0 To l
Select Case rtfData(i) 'CURRENT CHARACTER
Case carriageReturn
'ignore
Case linefeed
'ignore
Case nullSpace
'ignore
Case Else 'its text
If rtfData(i) <> 32 Or (rtfData(i) = 32 And
prev <> 32) Then
text = text + Chr(rtfData(i))
prev = rtfData(i)
End If
End Select
Next
Catch of As OverflowException
result = "ReadText: " + rptError(of)
Catch re As NullReferenceException
result = "ReadText: " + rptError(re)
Catch ir As IndexOutOfRangeException
result = "ReadText: " + rptError(ir)
Catch io As IO.IOException
result = "ReadText: " + rptError(io)
Catch ex As Exception
result = "ReadText: " + rptError(ex)
End Try
If result = "OK" Then
If l + 1 >= count Then
strError = "MORE"
Else
strError = result
End If
Else
strError = result
End If
text = Replace(text, "[^\w\.@.-]", " ")
Return text
End Function
Public Function IsRTF() As System.Boolean
Dim blnResult As System.Boolean = False
Dim result As System.String = ""
Dim strError As System.String = ""
result = ReadRTF(100, strError, "rtf")
If Not (strError <> "OK" And strError <> "MORE") Then
If Len(result) >= 3 And Left(result, 3) = "rtf" Then
blnResult = True
End If
End If
Return blnResult
End Function
'READRTF
'strips control words from rtf text and returns just the text
Public Function ReadRTF(ByVal count As System.Int32, ByRef
strError As System.String, Optional ByVal keyword As System.String ="") As System.String
Dim result As System.String = "OK"
Dim text As System.String = ""
Dim ctrlSymbol As System.Char = ""
Dim prevchar As Byte = 0
Dim l As System.Int32 = 0
Dim p As System.Int32 = 0
Try
Dim rtfData() As Byte = MyBase.ReadBytes(count)
l = rtfData.Length() - 1
Dim i As System.Int32 = 0
Dim controlWord As System.String = ""
Dim controlSymbol As System.Byte = 0
Dim curlyCount As System.Int32 = 0
Dim parm As System.String = ""
Dim junk As System.String = ""
'parse the rtfData and copy characters out of it into
result
For i = 0 To l
Select Case rtfData(i) 'CURRENT CHARACTER
Case openCurlyBrace 'START OF GROUP
curlyCount = curlyCount + 1
Case closedCurlyBrace 'END OF GROUP
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
Case backSlash 'THIS IS THE START OF A CONTROL
WORD or CONTROL SYMBOL
i = i + 1 'goto the next character to
determine what it is
If i <= l Then
If isLetter(rtfData(i)) Then 'its a
control word
controlWord = breakOutControlWord(i,
rtfData, l, parm)
If controlWord = keyword Then
text = controlWord + parm
Exit For
Else
Select Case controlWord
Case "fonttbl"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "colortbl"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "stylesheet"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "listtable"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "info"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "template"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "docvar"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "datafield"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "listlevel"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "pnseclvl"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "pict"
ignorePict(i, rtfData, l,
curlyCount)
Case "object"
ignoreObject(i, rtfData,
l, curlyCount)
Case "comment"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "footnote"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "footer"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "footerl"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "footerr"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "footerf"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "ftnsep"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "ftnsepc"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "ftncn"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "pn"
ignoreGroup(i, rtfData, l,
curlyCount)
text = text + " "
Case "pntext"
ignoreGroup(i, rtfData, l,
curlyCount)
text = text + " "
Case "par"
text = text + " "
Case "tab"
text = text + " "
Case "sect"
text = text + " "
Case "lquote"
text = text + " "
Case "generator"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "sn"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "sv"
ignoreGroup(i, rtfData, l,
curlyCount)
End Select
End If
Else 'its a control symbol
controlSymbol =Asc(breakOutControlSymbol(i, rtfData, l))
If controlSymbol = 32 Or
isLetterOrDigit(controlSymbol) Then
text = text + Chr(controlSymbol)
End If
End If
End If
Case carriageReturn
'ignore
Case linefeed
'ignore
Case nullSpace
'ignore
Case Else 'its text
text = text + Chr(rtfData(i))
End Select
Next
Catch of As OverflowException
result = "ReadRTF: " + rptError(of)
Catch re As NullReferenceException
result = "ReadRTF: " + rptError(re)
Catch ir As IndexOutOfRangeException
result = "ReadRTF: " + rptError(ir)
Catch io As IO.IOException
result = "ReadRTF: " + rptError(io)
Catch ex As Exception
result = "ReadRTF: " + rptError(ex)
End Try
If result = "OK" Then
If l + 1 >= count Then
strError = "MORE"
Else
strError = result
End If
Else
strError = result
End If
text = Replace(text, "[^\w\.@.-]", " ")
Return text
End Function
Private Function dump(ByRef i As System.Int32, ByRef rtfData() As
Byte, ByVal l As System.Int32, ByRef w As System.Int32) As
System.String
Dim result As System.String = ""
Dim s As System.Int32 = i
Dim e As System.Int32 = i + w
Dim c As System.Int32 = 0
For c = s To e
result = result + Chr(rtfData(c))
Next
Return " >>DUMP:" + result + "<<< "
End Function
Private Sub ignoreObject(ByRef i As System.Int32, ByRef rtfData()
As Byte, ByVal l As System.Int32, ByRef curlyCount As System.Int32)
Dim startCurlyCount As System.Int32 = curlyCount
Dim controlWord As System.String = ""
Dim parm As System.String = ""
Dim blockEnd As System.Int32 = 0
i = i + 1
While i <= l And curlyCount >= startCurlyCount
Select Case rtfData(i)
Case openCurlyBrace
curlyCount = curlyCount + 1
Case closedCurlyBrace
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
Case carriageReturn
'ignore
Case linefeed
'ignore
Case space
'ignore (we shouldn't be processing text in a
object)
Case backSlash 'THIS IS THE START OF A CONTROL WORD or
CONTROL SYMBOL
i = i + 1 'goto the next character to determine
what it is
If i <= l Then
If isLetter(rtfData(i)) Then 'its a control
word
controlWord = breakOutControlWord(i,
rtfData, l, parm)
Select Case controlWord
Case "objdata"
blockEnd = Array.IndexOf(rtfData,
closedCurlyBrace, i)
If blockEnd > 0 Then
i = blockEnd
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
End If
End Select
End If
End If
End Select
i = i + 1
End While
i = i - 1 'when the last curly bracket is done, we are one
past the position of the closing bracket
End Sub
Private Sub ignorePict(ByRef i As System.Int32, ByRef rtfData() As
Byte, ByVal l As System.Int32, ByRef curlyCount As System.Int32)
Dim startCurlyCount As System.Int32 = curlyCount
Dim controlWord As System.String = ""
Dim parm As System.String = ""
Dim blockEnd As System.Int32 = 0
i = i + 1 ' move off of the /pict control word to parse what
is after it
While i <= l And curlyCount >= startCurlyCount
Select Case rtfData(i)
Case openCurlyBrace
curlyCount = curlyCount + 1
Case closedCurlyBrace
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
Case carriageReturn
'ignore
Case linefeed
'ignore
Case space
'ignore (we shouldn't be processing text in a
picture)
Case backSlash 'THIS IS THE START OF A CONTROL WORD or
CONTROL SYMBOL
i = i + 1 'goto the next character to determine
what it is
If i <= l Then
If isLetter(rtfData(i)) Then 'its a control
word
controlWord = breakOutControlWord(i,
rtfData, l, parm)
End If
End If
Case Else 'its plain text on the \pict tag
If curlyCount = startCurlyCount Then
blockEnd = Array.IndexOf(rtfData,
closedCurlyBrace, i, l - i)
If blockEnd > 0 Then
i = blockEnd
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
End If
End If
End Select
i = i + 1
End While
i = i - 1 'when the last curly bracket is done, we are one
past the position of the closing bracket
End Sub
Private Sub ignoreGroup(ByRef i As System.Int32, ByRef rtfData()
As Byte, ByVal l As System.Int32, ByRef curlyCount As System.Int32)
Dim startCurlyCount As System.Int32 = curlyCount
While i <= l And curlyCount >= startCurlyCount
Select Case rtfData(i)
Case openCurlyBrace
curlyCount = curlyCount + 1
Case closedCurlyBrace
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
End Select
i = i + 1
End While
i = i - 1 'when the last curly bracket is done, we are one
past the position of the closing bracket
'so we have to backup by 1
End Sub
Private Function breakOutGroup(ByRef i As System.Int32, ByRef
rtfData() As Byte, ByVal l As System.Int32, ByRef curlyCount As
System.Int32) As System.String
Dim result As System.String = ""
Dim startCurlyCount As System.Int32 = curlyCount
While i <= l And curlyCount >= startCurlyCount
Select Case rtfData(i)
Case openCurlyBrace
curlyCount = curlyCount + 1
Case closedCurlyBrace
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
End Select
result = result + Chr(rtfData(i))
i = i + 1
End While
i = i - 1 'when the last curly bracket is done, we are one
past the position of the closing bracket
'so we have to backup by 1
Return result
End Function
Private Function breakOutControlWord(ByRef i As System.Int32,
ByRef rtfData() As Byte, ByVal l As System.Int32, ByRef parm As
System.String) As System.String
Dim ctrlWord As System.String = ""
'get the control word (which is made out of only letters)
While i <= l And isLetter(rtfData(i))
ctrlWord = ctrlWord + LCase(Chr(rtfData(i)))
i = i + 1
End While
'check if there's a parameter
If isParm(rtfData(i)) Then
If rtfData(i) = hyphen Then
i = i + 1 'we want to get the value which follows the
hyphen
End If
parm = breakOutParm(i, rtfData, l)
ElseIf rtfData(i) <> space Then
i = i - 1
End If
Return Trim(ctrlWord)
End Function
Private Function breakOutControlSymbol(ByRef i As System.Int32,
ByRef rtfData() As Byte, ByVal l As System.Int32) As System.Char
Dim result As System.Char = ""
Dim strValue As System.String = ""
Dim nValue As System.Int16 = 0
Select Case rtfData(i)
Case asterisk
'code in group belongs to new RTF standard
result = ""
Case backSlash
result = Chr(rtfData(i))
Case openCurlyBrace
result = Chr(rtfData(i))
Case closedCurlyBrace
result = Chr(rtfData(i))
Case pipe
result = "F"
Case tilde
result = " "
Case hyphen
result = "-"
Case underscore
result = "-"
Case nullSpace
result = "-"
Case apostroph
result = " "
i = i + 1
strValue = breakOutParm(i, rtfData, l)
nValue = Val("&H" + strValue)
If nValue > 0 And nValue <= 255 Then
If isLetterOrDigit(nValue) = True Then
result = Chr(nValue)
End If
End If
Case lessThan
result = " "
i = i + 1
strValue = breakOutParm(i, rtfData, l)
nValue = Val(strValue)
If nValue > 0 And nValue <= 255 Then
If isLetterOrDigit(nValue) = True Then
result = Chr(nValue)
End If
End If
Case Else
result = ""
End Select
Return result
End Function
Private Function breakOutParm(ByRef i As System.Int32, ByRef
rtfData() As Byte, ByVal l As System.Int32) As System.String
Dim result As System.String = ""
Dim value As System.String = ""
While i <= l And isLetterOrDigit(rtfData(i))
value = value + Chr(rtfData(i))
i = i + 1
End While
If rtfData(i) <> space Then
i = i - 1
End If
Return value
End Function
Private Function isLetter(ByVal nLetter As System.Byte) As
System.Boolean
Dim blnResult As System.Boolean = False
Dim code As System.Int16 = 0
If nLetter >= 97 Then
code = nLetter - 32
Else
code = nLetter
End If
If code >= 65 And code <= 90 Then
blnResult = True
End If
Return blnResult
End Function
Private Function isDigit(ByVal code As System.Byte) As
System.Boolean
Dim blnResult As System.Boolean = False
If code >= 48 And code <= 57 Then
blnResult = True
End If
Return blnResult
End Function
Private Function isLetterOrDigit(ByVal code As System.Byte) As
System.Boolean
Dim blnResult As System.Boolean = False
If isDigit(code) Or isLetter(code) Then
blnResult = True
End If
Return blnResult
End Function
Private Function isParm(ByVal code As System.Byte) As
System.Boolean
Dim blnResult As System.Boolean = False
If isDigit(code) Or code = hyphen Then
blnResult = True
End If
Return blnResult
End Function
Private Function isDelimeter(ByVal code As System.Byte) As
System.Boolean
Dim blnResult As System.Boolean = False
If Not isLetterOrDigit(code) Then
blnResult = True
End If
Return blnResult
End Function
Private Function rptError(ByVal objError As System.Object) As
System.String
Dim result As System.String
Select Case objError.GetType.Name
Case "HttpException"
result = "http exception: " + vbCrLf + "HTTP Error: "
+ objError.GetHttpCode.ToString + vbCrLf +
objError.GetHtmlErrorMessage + vbCrLf + vbCrLf +
objError.ErrorCode.ToString + " " + objError.Message + vbCrLf +
objError.Source + vbCrLf + objError.StackTrace
Case "SoapException"
result = "soap exception: " + vbCrLf +
objError.Message + vbCrLf + objError.StackTrace + vbCrLf
Case "WebException"
result = "web exception: " + vbCrLf +
objError.Status.ToString() + ":" + vbCrLf + objError.Message + vbCrLf
Case "XmlException"
result = "xml exception: " + objError.Source + " on
line " + objError.LineNumber.ToString + " at position " +
objError.LinePosition.ToString + vbCrLf + objError.Message + vbCrLf +
objError.StackTrace
Case "SqlException"
result = "SQL exception: " +
objError.Number.ToString() + " " + objError.Message + vbCrLf +
objError.Server
Case "FormatException"
result = "Numeric Format exception: " +
objError.Source + vbCrLf + objError.Message + vbCrLf +
objError.StackTrace
Case "Exception"
result = "exception: " + vbCrLf + objError.Message +
vbCrLf + objError.Source + vbCrLf + objError.StackTrace
Case "String"
result = "Error: " + objError
Case "OverflowException"
result = "OverflowException: " + objError.Source +
vbCrLf + objError.Message + vbCrLf + objError.StackTrace
Case "IndexOutOfRangeException"
result = "Index exception: " + objError.Source +
vbCrLf + objError.Message + vbCrLf + objError.StackTrace
Case "NullReferenceException"
result = "Null reference exception: " +
objError.Source + vbCrLf + objError.Message + vbCrLf +
objError.StackTrace
Case "InvalidOperationException"
result = "Invalid Operation: " + objError.Source +
vbCrLf + objError.Message + vbCrLf + objError.HelpLink + vbCrLf +
objError.StackTrace
Case "ArgumentException"
result = "Bad parameter value for " +
objError.ParamName + ":" + vbCrLf + objError.Source + vbCrLf +
objError.Message + vbCrLf + objError.HelpLink + vbCrLf +
objError.StackTrace
Case "InvalidCastException"
result = "Type mis-match: " + objError.Source + vbCrLf
+ objError.Message + vbCrLf + objError.StackTrace
Case "XPathException"
result = "Bad XPath: " + objError.Source + vbCrLf +
objError.Message + vbCrLf + objError.StackTrace
Case Else
result = "An unanticipated error has occured for " +
objError.GetType.Name
End Select
Return result
End Function
End Class
Can anyone tell me how to convert RTF formatted data into plain text?
I have a column in sql server 2005 table which stores the rtf data. I
want to convert and store this data as plain text in another table.
So then I can display this data in my SSRS 2005 report.
Thanks,RTF data is plain text which has some pieces of the text marked up by
XML tags. There are a couple of versions of RTF, and the type of RTF
data you have will be marked by the first XML tag you see in your RTF
document. RTF also allows authors to create custom (non-standard) RTF
tags which may appear in your RTF data.
Below appears the core code we use to extract text from RTF files. It
is written in VB.NET 1.1
'RTFreader is a customized .NET binary reader that has a method added
to it to
'parse out text from an rtf file. Uses same methods and properties as
binary
'reader does.
Public Class RTFreader
Inherits System.IO.BinaryReader
Const openCurlyBrace As System.Byte = Asc("{")
Const closedCurlyBrace As System.Byte = Asc("}")
Const openRoundBrace As System.Byte = Asc("(")
Const closedRoundBrace As System.Byte = Asc(")")
Const doubleQuote As System.Byte = 34
Const backSlash As System.Byte = Asc("\")
Const hyphen As System.Byte = Asc("-")
Const space As System.Byte = Asc(" ")
Const asterisk As System.Byte = Asc("*")
Const apostroph As System.Byte = Asc("'")
Const semicolon As System.Byte = Asc(";")
Const nullSpace As System.Byte = 0
Const tab As System.Byte = 9
Const linefeed As System.Byte = 10
Const carriageReturn As System.Byte = 13
Const forwardSlash As System.Byte = Asc("/")
Const pipe As System.Byte = Asc("|")
Const underscore As System.Byte = Asc("_")
Const tilde As System.Byte = Asc("~")
Const lessThan As System.Byte = Asc("<")
Const greaterThan As System.Byte = Asc(">")
'CONSTRUCTOR
Sub New(ByVal st As System.IO.Stream)
MyBase.New(st)
End Sub
'READTEXT
'strips tabs,carriage returns, line feeds, and non-alphabetic
characters and returns everything else
Public Function ReadText(ByVal count As System.Int32, ByRef
strError As System.String, Optional ByVal keyword As System.String ="") As System.String
Dim result As System.String = "OK"
Dim text As System.String = ""
Dim prevchar As Byte = 0
Dim l As System.Int32 = 0
Dim p As System.Int32 = 0
Dim prev As System.Byte = 0
Try
Dim rtfData() As Byte = MyBase.ReadBytes(count)
l = rtfData.Length() - 1
Dim i As System.Int32 = 0
'parse the rtfData and copy characters out of it into
result
For i = 0 To l
Select Case rtfData(i) 'CURRENT CHARACTER
Case carriageReturn
'ignore
Case linefeed
'ignore
Case nullSpace
'ignore
Case Else 'its text
If rtfData(i) <> 32 Or (rtfData(i) = 32 And
prev <> 32) Then
text = text + Chr(rtfData(i))
prev = rtfData(i)
End If
End Select
Next
Catch of As OverflowException
result = "ReadText: " + rptError(of)
Catch re As NullReferenceException
result = "ReadText: " + rptError(re)
Catch ir As IndexOutOfRangeException
result = "ReadText: " + rptError(ir)
Catch io As IO.IOException
result = "ReadText: " + rptError(io)
Catch ex As Exception
result = "ReadText: " + rptError(ex)
End Try
If result = "OK" Then
If l + 1 >= count Then
strError = "MORE"
Else
strError = result
End If
Else
strError = result
End If
text = Replace(text, "[^\w\.@.-]", " ")
Return text
End Function
Public Function IsRTF() As System.Boolean
Dim blnResult As System.Boolean = False
Dim result As System.String = ""
Dim strError As System.String = ""
result = ReadRTF(100, strError, "rtf")
If Not (strError <> "OK" And strError <> "MORE") Then
If Len(result) >= 3 And Left(result, 3) = "rtf" Then
blnResult = True
End If
End If
Return blnResult
End Function
'READRTF
'strips control words from rtf text and returns just the text
Public Function ReadRTF(ByVal count As System.Int32, ByRef
strError As System.String, Optional ByVal keyword As System.String ="") As System.String
Dim result As System.String = "OK"
Dim text As System.String = ""
Dim ctrlSymbol As System.Char = ""
Dim prevchar As Byte = 0
Dim l As System.Int32 = 0
Dim p As System.Int32 = 0
Try
Dim rtfData() As Byte = MyBase.ReadBytes(count)
l = rtfData.Length() - 1
Dim i As System.Int32 = 0
Dim controlWord As System.String = ""
Dim controlSymbol As System.Byte = 0
Dim curlyCount As System.Int32 = 0
Dim parm As System.String = ""
Dim junk As System.String = ""
'parse the rtfData and copy characters out of it into
result
For i = 0 To l
Select Case rtfData(i) 'CURRENT CHARACTER
Case openCurlyBrace 'START OF GROUP
curlyCount = curlyCount + 1
Case closedCurlyBrace 'END OF GROUP
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
Case backSlash 'THIS IS THE START OF A CONTROL
WORD or CONTROL SYMBOL
i = i + 1 'goto the next character to
determine what it is
If i <= l Then
If isLetter(rtfData(i)) Then 'its a
control word
controlWord = breakOutControlWord(i,
rtfData, l, parm)
If controlWord = keyword Then
text = controlWord + parm
Exit For
Else
Select Case controlWord
Case "fonttbl"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "colortbl"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "stylesheet"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "listtable"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "info"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "template"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "docvar"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "datafield"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "listlevel"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "pnseclvl"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "pict"
ignorePict(i, rtfData, l,
curlyCount)
Case "object"
ignoreObject(i, rtfData,
l, curlyCount)
Case "comment"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "footnote"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "footer"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "footerl"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "footerr"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "footerf"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "ftnsep"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "ftnsepc"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "ftncn"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "pn"
ignoreGroup(i, rtfData, l,
curlyCount)
text = text + " "
Case "pntext"
ignoreGroup(i, rtfData, l,
curlyCount)
text = text + " "
Case "par"
text = text + " "
Case "tab"
text = text + " "
Case "sect"
text = text + " "
Case "lquote"
text = text + " "
Case "generator"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "sn"
ignoreGroup(i, rtfData, l,
curlyCount)
Case "sv"
ignoreGroup(i, rtfData, l,
curlyCount)
End Select
End If
Else 'its a control symbol
controlSymbol =Asc(breakOutControlSymbol(i, rtfData, l))
If controlSymbol = 32 Or
isLetterOrDigit(controlSymbol) Then
text = text + Chr(controlSymbol)
End If
End If
End If
Case carriageReturn
'ignore
Case linefeed
'ignore
Case nullSpace
'ignore
Case Else 'its text
text = text + Chr(rtfData(i))
End Select
Next
Catch of As OverflowException
result = "ReadRTF: " + rptError(of)
Catch re As NullReferenceException
result = "ReadRTF: " + rptError(re)
Catch ir As IndexOutOfRangeException
result = "ReadRTF: " + rptError(ir)
Catch io As IO.IOException
result = "ReadRTF: " + rptError(io)
Catch ex As Exception
result = "ReadRTF: " + rptError(ex)
End Try
If result = "OK" Then
If l + 1 >= count Then
strError = "MORE"
Else
strError = result
End If
Else
strError = result
End If
text = Replace(text, "[^\w\.@.-]", " ")
Return text
End Function
Private Function dump(ByRef i As System.Int32, ByRef rtfData() As
Byte, ByVal l As System.Int32, ByRef w As System.Int32) As
System.String
Dim result As System.String = ""
Dim s As System.Int32 = i
Dim e As System.Int32 = i + w
Dim c As System.Int32 = 0
For c = s To e
result = result + Chr(rtfData(c))
Next
Return " >>DUMP:" + result + "<<< "
End Function
Private Sub ignoreObject(ByRef i As System.Int32, ByRef rtfData()
As Byte, ByVal l As System.Int32, ByRef curlyCount As System.Int32)
Dim startCurlyCount As System.Int32 = curlyCount
Dim controlWord As System.String = ""
Dim parm As System.String = ""
Dim blockEnd As System.Int32 = 0
i = i + 1
While i <= l And curlyCount >= startCurlyCount
Select Case rtfData(i)
Case openCurlyBrace
curlyCount = curlyCount + 1
Case closedCurlyBrace
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
Case carriageReturn
'ignore
Case linefeed
'ignore
Case space
'ignore (we shouldn't be processing text in a
object)
Case backSlash 'THIS IS THE START OF A CONTROL WORD or
CONTROL SYMBOL
i = i + 1 'goto the next character to determine
what it is
If i <= l Then
If isLetter(rtfData(i)) Then 'its a control
word
controlWord = breakOutControlWord(i,
rtfData, l, parm)
Select Case controlWord
Case "objdata"
blockEnd = Array.IndexOf(rtfData,
closedCurlyBrace, i)
If blockEnd > 0 Then
i = blockEnd
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
End If
End Select
End If
End If
End Select
i = i + 1
End While
i = i - 1 'when the last curly bracket is done, we are one
past the position of the closing bracket
End Sub
Private Sub ignorePict(ByRef i As System.Int32, ByRef rtfData() As
Byte, ByVal l As System.Int32, ByRef curlyCount As System.Int32)
Dim startCurlyCount As System.Int32 = curlyCount
Dim controlWord As System.String = ""
Dim parm As System.String = ""
Dim blockEnd As System.Int32 = 0
i = i + 1 ' move off of the /pict control word to parse what
is after it
While i <= l And curlyCount >= startCurlyCount
Select Case rtfData(i)
Case openCurlyBrace
curlyCount = curlyCount + 1
Case closedCurlyBrace
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
Case carriageReturn
'ignore
Case linefeed
'ignore
Case space
'ignore (we shouldn't be processing text in a
picture)
Case backSlash 'THIS IS THE START OF A CONTROL WORD or
CONTROL SYMBOL
i = i + 1 'goto the next character to determine
what it is
If i <= l Then
If isLetter(rtfData(i)) Then 'its a control
word
controlWord = breakOutControlWord(i,
rtfData, l, parm)
End If
End If
Case Else 'its plain text on the \pict tag
If curlyCount = startCurlyCount Then
blockEnd = Array.IndexOf(rtfData,
closedCurlyBrace, i, l - i)
If blockEnd > 0 Then
i = blockEnd
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
End If
End If
End Select
i = i + 1
End While
i = i - 1 'when the last curly bracket is done, we are one
past the position of the closing bracket
End Sub
Private Sub ignoreGroup(ByRef i As System.Int32, ByRef rtfData()
As Byte, ByVal l As System.Int32, ByRef curlyCount As System.Int32)
Dim startCurlyCount As System.Int32 = curlyCount
While i <= l And curlyCount >= startCurlyCount
Select Case rtfData(i)
Case openCurlyBrace
curlyCount = curlyCount + 1
Case closedCurlyBrace
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
End Select
i = i + 1
End While
i = i - 1 'when the last curly bracket is done, we are one
past the position of the closing bracket
'so we have to backup by 1
End Sub
Private Function breakOutGroup(ByRef i As System.Int32, ByRef
rtfData() As Byte, ByVal l As System.Int32, ByRef curlyCount As
System.Int32) As System.String
Dim result As System.String = ""
Dim startCurlyCount As System.Int32 = curlyCount
While i <= l And curlyCount >= startCurlyCount
Select Case rtfData(i)
Case openCurlyBrace
curlyCount = curlyCount + 1
Case closedCurlyBrace
curlyCount = curlyCount - 1
If curlyCount < 0 Then
curlyCount = 0
End If
End Select
result = result + Chr(rtfData(i))
i = i + 1
End While
i = i - 1 'when the last curly bracket is done, we are one
past the position of the closing bracket
'so we have to backup by 1
Return result
End Function
Private Function breakOutControlWord(ByRef i As System.Int32,
ByRef rtfData() As Byte, ByVal l As System.Int32, ByRef parm As
System.String) As System.String
Dim ctrlWord As System.String = ""
'get the control word (which is made out of only letters)
While i <= l And isLetter(rtfData(i))
ctrlWord = ctrlWord + LCase(Chr(rtfData(i)))
i = i + 1
End While
'check if there's a parameter
If isParm(rtfData(i)) Then
If rtfData(i) = hyphen Then
i = i + 1 'we want to get the value which follows the
hyphen
End If
parm = breakOutParm(i, rtfData, l)
ElseIf rtfData(i) <> space Then
i = i - 1
End If
Return Trim(ctrlWord)
End Function
Private Function breakOutControlSymbol(ByRef i As System.Int32,
ByRef rtfData() As Byte, ByVal l As System.Int32) As System.Char
Dim result As System.Char = ""
Dim strValue As System.String = ""
Dim nValue As System.Int16 = 0
Select Case rtfData(i)
Case asterisk
'code in group belongs to new RTF standard
result = ""
Case backSlash
result = Chr(rtfData(i))
Case openCurlyBrace
result = Chr(rtfData(i))
Case closedCurlyBrace
result = Chr(rtfData(i))
Case pipe
result = "F"
Case tilde
result = " "
Case hyphen
result = "-"
Case underscore
result = "-"
Case nullSpace
result = "-"
Case apostroph
result = " "
i = i + 1
strValue = breakOutParm(i, rtfData, l)
nValue = Val("&H" + strValue)
If nValue > 0 And nValue <= 255 Then
If isLetterOrDigit(nValue) = True Then
result = Chr(nValue)
End If
End If
Case lessThan
result = " "
i = i + 1
strValue = breakOutParm(i, rtfData, l)
nValue = Val(strValue)
If nValue > 0 And nValue <= 255 Then
If isLetterOrDigit(nValue) = True Then
result = Chr(nValue)
End If
End If
Case Else
result = ""
End Select
Return result
End Function
Private Function breakOutParm(ByRef i As System.Int32, ByRef
rtfData() As Byte, ByVal l As System.Int32) As System.String
Dim result As System.String = ""
Dim value As System.String = ""
While i <= l And isLetterOrDigit(rtfData(i))
value = value + Chr(rtfData(i))
i = i + 1
End While
If rtfData(i) <> space Then
i = i - 1
End If
Return value
End Function
Private Function isLetter(ByVal nLetter As System.Byte) As
System.Boolean
Dim blnResult As System.Boolean = False
Dim code As System.Int16 = 0
If nLetter >= 97 Then
code = nLetter - 32
Else
code = nLetter
End If
If code >= 65 And code <= 90 Then
blnResult = True
End If
Return blnResult
End Function
Private Function isDigit(ByVal code As System.Byte) As
System.Boolean
Dim blnResult As System.Boolean = False
If code >= 48 And code <= 57 Then
blnResult = True
End If
Return blnResult
End Function
Private Function isLetterOrDigit(ByVal code As System.Byte) As
System.Boolean
Dim blnResult As System.Boolean = False
If isDigit(code) Or isLetter(code) Then
blnResult = True
End If
Return blnResult
End Function
Private Function isParm(ByVal code As System.Byte) As
System.Boolean
Dim blnResult As System.Boolean = False
If isDigit(code) Or code = hyphen Then
blnResult = True
End If
Return blnResult
End Function
Private Function isDelimeter(ByVal code As System.Byte) As
System.Boolean
Dim blnResult As System.Boolean = False
If Not isLetterOrDigit(code) Then
blnResult = True
End If
Return blnResult
End Function
Private Function rptError(ByVal objError As System.Object) As
System.String
Dim result As System.String
Select Case objError.GetType.Name
Case "HttpException"
result = "http exception: " + vbCrLf + "HTTP Error: "
+ objError.GetHttpCode.ToString + vbCrLf +
objError.GetHtmlErrorMessage + vbCrLf + vbCrLf +
objError.ErrorCode.ToString + " " + objError.Message + vbCrLf +
objError.Source + vbCrLf + objError.StackTrace
Case "SoapException"
result = "soap exception: " + vbCrLf +
objError.Message + vbCrLf + objError.StackTrace + vbCrLf
Case "WebException"
result = "web exception: " + vbCrLf +
objError.Status.ToString() + ":" + vbCrLf + objError.Message + vbCrLf
Case "XmlException"
result = "xml exception: " + objError.Source + " on
line " + objError.LineNumber.ToString + " at position " +
objError.LinePosition.ToString + vbCrLf + objError.Message + vbCrLf +
objError.StackTrace
Case "SqlException"
result = "SQL exception: " +
objError.Number.ToString() + " " + objError.Message + vbCrLf +
objError.Server
Case "FormatException"
result = "Numeric Format exception: " +
objError.Source + vbCrLf + objError.Message + vbCrLf +
objError.StackTrace
Case "Exception"
result = "exception: " + vbCrLf + objError.Message +
vbCrLf + objError.Source + vbCrLf + objError.StackTrace
Case "String"
result = "Error: " + objError
Case "OverflowException"
result = "OverflowException: " + objError.Source +
vbCrLf + objError.Message + vbCrLf + objError.StackTrace
Case "IndexOutOfRangeException"
result = "Index exception: " + objError.Source +
vbCrLf + objError.Message + vbCrLf + objError.StackTrace
Case "NullReferenceException"
result = "Null reference exception: " +
objError.Source + vbCrLf + objError.Message + vbCrLf +
objError.StackTrace
Case "InvalidOperationException"
result = "Invalid Operation: " + objError.Source +
vbCrLf + objError.Message + vbCrLf + objError.HelpLink + vbCrLf +
objError.StackTrace
Case "ArgumentException"
result = "Bad parameter value for " +
objError.ParamName + ":" + vbCrLf + objError.Source + vbCrLf +
objError.Message + vbCrLf + objError.HelpLink + vbCrLf +
objError.StackTrace
Case "InvalidCastException"
result = "Type mis-match: " + objError.Source + vbCrLf
+ objError.Message + vbCrLf + objError.StackTrace
Case "XPathException"
result = "Bad XPath: " + objError.Source + vbCrLf +
objError.Message + vbCrLf + objError.StackTrace
Case Else
result = "An unanticipated error has occured for " +
objError.GetType.Name
End Select
Return result
End Function
End Class
Convert RTF to plain text
I have a SQL Server 2000 table with a few fields of "text" data type
that contain rich text. I have to downstream this data and the
recipient cannot handle rich text. I need to figure out a way to
convert it back to plain text. Any suggetions?
that contain rich text. I have to downstream this data and the
recipient cannot handle rich text. I need to figure out a way to
convert it back to plain text. Any suggetions?
TIAIt's painful, but you can loop thru every character. Use the ASCII
function to identify and remove any non-alphanumerics (0-9 or a-z or
A-Z or space or period or comma ...).|||"Ted" <teddy_theo@.yahoo.com> wrote in message
news:1108133581.962856.208040@.l41g2000cwc.googlegr oups.com...
>I have a SQL Server 2000 table with a few fields of "text" data type
> that contain rich text. I have to downstream this data and the
> recipient cannot handle rich text. I need to figure out a way to
> convert it back to plain text. Any suggetions?
> TIA
The best idea is probably to export the data to file and convert it
externally - the MSSQL string functions are extremely basic, and writing a
script in something like Perl, Python, C# or whatever will be much more
efficient. With a bit of Googling, you'll probably be able to find something
for your preferred language - there's already a Perl module, for example.
Simon|||agreed. i was trying to avoid a front end process because the client
is going to be pulling data directly from a view in a production
environment. i'll have to throw a little .net app together to do the
conversion i suppose. thanks for all the feedback!!|||"louis" <louisducnguyen@.gmail.com> wrote:
>It's painful, but you can loop thru every character. Use the ASCII
>function to identify and remove any non-alphanumerics (0-9 or a-z or
>A-Z or space or period or comma ...).
I've written software (as a standalone utility, not in the context of
SQL) that goes the other way, but can't offer anything that helps in
this direction. I can offer some advice though.
You need to be a bit more careful than outlined above. In RTF the
backslash "\" and brace characters "{}" are reserved. RTF is
essentially a markup language and the "tags" start with a backslash,
and can contain alphanumerics (typically alphas and then - optionally
- numerics). Braces are used to delimit sections. Some sections
such as those in the header (info and font tables) can be entirely
discarded from the visible output.
Braces and backslashes in the text are escaped - IIRC - with a
backslash. Using this, you could indeed convert most of the RTF
to text.
This approach would recover most text, but some features such as
lists might come out strange, and it wouldn't be formatted nicely,
unless you wanted to honour the \par and \line tags to give line
formatting, but in the context of insertion into a database I guess
that's the most you's want to do.
Also, depending on the source of the RTF you may be dealing with easy
to parse snippets, as opposed to a fully-featured document.
I should imagine that programming the above in SQL would be - as you
rightly point out - quite painful.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/
convert rtf to plain text
Hi All,
can anybody please tell me how to use below sql function.I have a
requirement to convert rtf formatted string as plain text from sql
table.I found below sql function in google search. but I dont no how
to use this.what is 'RICHTEXT.RichtextCtrl' ?
CREATE function dbo.RTF2Text(@.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 S
-- QL 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
All i need is to retrive this rtf formatted string as plain text.
Any help is appreciated!
ThanksThat is a COM object of some kind that needs to be registered on the server.
I was able to call the object in VBScript, but it sounds like something that
may need to be installed with Word. My guess is that you will be better off
doing this conversion from an application that understands RTF -> text
conversion rather after it retrieves the SQL data, than to call this
external process from within SQL Server...
A
"munnyAnu" <dkanumolu@.gmail.com> wrote in message
news:1188320430.440778.81540@.g4g2000hsf.googlegroups.com...
> Hi All,
> can anybody please tell me how to use below sql function.I have a
> requirement to convert rtf formatted string as plain text from sql
> table.I found below sql function in google search. but I dont no how
> to use this.what is 'RICHTEXT.RichtextCtrl' ?
>
> CREATE function dbo.RTF2Text(@.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 S
> -- QL 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
>
> All i need is to retrive this rtf formatted string as plain text.
> Any help is appreciated!
>
> Thanks
>
can anybody please tell me how to use below sql function.I have a
requirement to convert rtf formatted string as plain text from sql
table.I found below sql function in google search. but I dont no how
to use this.what is 'RICHTEXT.RichtextCtrl' ?
CREATE function dbo.RTF2Text(@.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 S
-- QL 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
All i need is to retrive this rtf formatted string as plain text.
Any help is appreciated!
ThanksThat is a COM object of some kind that needs to be registered on the server.
I was able to call the object in VBScript, but it sounds like something that
may need to be installed with Word. My guess is that you will be better off
doing this conversion from an application that understands RTF -> text
conversion rather after it retrieves the SQL data, than to call this
external process from within SQL Server...
A
"munnyAnu" <dkanumolu@.gmail.com> wrote in message
news:1188320430.440778.81540@.g4g2000hsf.googlegroups.com...
> Hi All,
> can anybody please tell me how to use below sql function.I have a
> requirement to convert rtf formatted string as plain text from sql
> table.I found below sql function in google search. but I dont no how
> to use this.what is 'RICHTEXT.RichtextCtrl' ?
>
> CREATE function dbo.RTF2Text(@.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 S
> -- QL 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
>
> All i need is to retrive this rtf formatted string as plain text.
> Any help is appreciated!
>
> Thanks
>
Friday, February 24, 2012
Convert mssql file
I am running mssql server for a sharepoint site. I have a mssql .bak file
that needs to be converted to plain text sql queries.
Is it possible to convert it somehow?
You have to restore it (in SQL Server) and query its with SQL Server or pump
the data out to some more propetary format liek access.
HTH, Jens Suessmeyer.
"Nick Mirro" <dirdx@.comcast.net> schrieb im Newsbeitrag
news:utFWDpuVFHA.3488@.TK2MSFTNGP10.phx.gbl...
>I am running mssql server for a sharepoint site. I have a mssql .bak file
>that needs to be converted to plain text sql queries.
> Is it possible to convert it somehow?
>
that needs to be converted to plain text sql queries.
Is it possible to convert it somehow?
You have to restore it (in SQL Server) and query its with SQL Server or pump
the data out to some more propetary format liek access.
HTH, Jens Suessmeyer.
"Nick Mirro" <dirdx@.comcast.net> schrieb im Newsbeitrag
news:utFWDpuVFHA.3488@.TK2MSFTNGP10.phx.gbl...
>I am running mssql server for a sharepoint site. I have a mssql .bak file
>that needs to be converted to plain text sql queries.
> Is it possible to convert it somehow?
>
Convert mssql file
I am running mssql server for a sharepoint site. I have a mssql .bak file
that needs to be converted to plain text sql queries.
Is it possible to convert it somehow?
You have to restore it (in SQL Server) and query its with SQL Server or pump
the data out to some more propetary format liek access.
HTH, Jens Suessmeyer.
"Nick Mirro" <dirdx@.comcast.net> schrieb im Newsbeitrag
news:utFWDpuVFHA.3488@.TK2MSFTNGP10.phx.gbl...
>I am running mssql server for a sharepoint site. I have a mssql .bak file
>that needs to be converted to plain text sql queries.
> Is it possible to convert it somehow?
>
that needs to be converted to plain text sql queries.
Is it possible to convert it somehow?
You have to restore it (in SQL Server) and query its with SQL Server or pump
the data out to some more propetary format liek access.
HTH, Jens Suessmeyer.
"Nick Mirro" <dirdx@.comcast.net> schrieb im Newsbeitrag
news:utFWDpuVFHA.3488@.TK2MSFTNGP10.phx.gbl...
>I am running mssql server for a sharepoint site. I have a mssql .bak file
>that needs to be converted to plain text sql queries.
> Is it possible to convert it somehow?
>
Sunday, February 19, 2012
Convert image datatype to varchar
I have a table Table1 which has a Col called "Msg" datatype image<binary>. Msg alreay has the plain text or RTF text as a image datatype (binary)
If I execute the following query "Select Msg from Table1 where id =3" then this query is returning the following ASCII/Binary data.
Msg = "0x7B5C727466315C616E73695C616E7369637067313235325C64656666305C6465666C616E673130333
37B5C666F6E7474626C7B5C66305C6673776973735C66707271325C6663686172736574302041726961
6C3B7D7B5C66315C6673776973735C66707271325C666368617273657430204D6963726F736F667420"
Can any body tell me how can I convert the above binary data to plain text from my query?
Thanks for any reply.
There is no Conversion of Image to Varchar but you can convert Image to Varbinary with limitations, the text below is from the BOL( books online).
Automatic data type conversion is not supported for the text andimagedata types. You can explicitly converttextdata to character data, andimage data tobinaryorvarbinary, but the maximum length is 8000. If you attempt an incorrect conversion (for example, if you convert a character expression that includes letters to anint), SQLServer generates an error message.
Hope this helps.
If I execute the following query "Select Msg from Table1 where id =3" then this query is returning the following ASCII/Binary data.
Msg = "0x7B5C727466315C616E73695C616E7369637067313235325C64656666305C6465666C616E673130333
37B5C666F6E7474626C7B5C66305C6673776973735C66707271325C6663686172736574302041726961
6C3B7D7B5C66315C6673776973735C66707271325C666368617273657430204D6963726F736F667420"
Can any body tell me how can I convert the above binary data to plain text from my query?
Thanks for any reply.
There is no Conversion of Image to Varchar but you can convert Image to Varbinary with limitations, the text below is from the BOL( books online).
Automatic data type conversion is not supported for the text andimagedata types. You can explicitly converttextdata to character data, andimage data tobinaryorvarbinary, but the maximum length is 8000. If you attempt an incorrect conversion (for example, if you convert a character expression that includes letters to anint), SQLServer generates an error message.
Hope this helps.
Subscribe to:
Posts (Atom)