September 23, 2015 at 8:04 am
I have a table with an image type field that contains RTF data. I want to convert the image field to a nvarchar without the RTF tags.
I found some code that is supposed to be used in a script component within SSIS to do this but it's not working as planned. The code is posted below. The GDTXFT field is the image field. I run it through the Script Component and transform it with the code below mapping it back to an Image type field. When I look at the data with convert(nvarchar(max), convert(varbinary(max), gdtxft)) i get this: ⼹㈲㈯‵坄匠⁆䄣捣剴煥〭㔳㠸
What am I doing wrong?
Try
Dim RTFConvert As RichTextBox = New RichTextBox
'encode blob to unicode string then put in rtf of rich text box
RTFConvert.Rtf = System.Text.Encoding.Unicode.GetString(Row.GDTXFT.GetBlobData(0, Integer.Parse(Row.GDTXFT.Length.ToString)))
'put rich text box string (without rtf tag) into text field
Row.GDTXFT.ResetBlobData()
Row.GDTXFT.AddBlobData(System.Text.Encoding.UTF8.GetBytes(RTFConvert.Text))
Catch E As Exception
'Row.GDTXFT.ResetBlobData()
End Try
September 23, 2015 at 8:23 am
Hi and welcome to the forums. This would be an excellent place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
I have to ask, why do you have an image column with RTF values? The image datatype has been deprecated for over 10 years. But even if it wasn't why image and not nvarchar?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2015 at 8:26 am
It is a package software so I am sure the vendor has not updated the data types in quite some time. So I am working with what i was given. 🙂 I can convert it to an nvarchar and see the text but it has all the RTF tags. I don't want to scan and replace to remove the tags as it requires so many scans to find all the specific tags.
September 23, 2015 at 8:52 am
eric-470517 (9/23/2015)
It is a package software so I am sure the vendor has not updated the data types in quite some time. So I am working with what i was given. 🙂 I can convert it to an nvarchar and see the text but it has all the RTF tags. I don't want to scan and replace to remove the tags as it requires so many scans to find all the specific tags.
i feel your pain being forced to work with less than optimal systems. I understand what you are trying to do here but you need to provide some details. Without some table definitions and such we are just guessing as to what may or may not work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2015 at 9:14 am
The first table is the Staging table used to extract the data in the exact form as it is on the source system. The GDTXFT field is the field being converted in the VB script I posted earlier.
CREATE TABLE [STAGING].[F00165x](
[GDOBNM] [nchar](10) NOT NULL,
[GDTXKY] [nvarchar](254) NOT NULL,
[GDMOSEQN] [numeric](18, 0) NOT NULL,
[GDTXFT] [image] NULL
The Data Warehouse table is what I am transforming the Staging table into. The Staging.GDTXFT field is being transformed through the VBA script and being mapped to the DataWarehouse.GDTXFT field.
CREATE TABLE [DataWarehouse].[F00165x](
[GDOBNM] [nvarchar](10) NOT NULL,
[GDTXKY] [nvarchar](254) NOT NULL,
[GDMOSEQN] [decimal](18, 0) NOT NULL,
[GDTXFT] [image] NULL
Once the data is in the DataWarehouse table I do a convert(nvarchar(max), convert(varbinary(max), gdtxft)) and that's when i get the Chinese looking characters.
Make Sense??
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply