Hi,

I have a task to perform that will export data from a single table and
create a .csv file. Not a problem I hear - thing is 1 of the columns has
HTML code within a product description. However the .csv export requires
that this HTML code is stripped.

Is it possible to achieve this through DTS? I would rather not do this
through an ASP page, as this needs to be run on a scheduled basis.

thanks for any advice.

Mark

Can you give us a sample row ? and CREATE TABLE statement ?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Mark Roberts" <mark (AT) freshegg_nospam_ (DOT) co.uk> wrote

Hi Allan,

Not just us working today then!

CREATE TABLE [products] (

[ProductId] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,

[ProductName] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[productShortDescription] [text] COLLATE Latin1_General_CI_AS NULL

GO

Sample row might be:

1, Alien Chest Burster ,<P>Few moments in film are as horrifyingly shocking

as the moment where the Alien Chest Burster makes its first appearance at

the dinner table in the sci-fi thriller <b>"Alien"</b>. </P>

Simple example of contents of a couple of the columns to export.

Column 3 shows some HTML that we need to clean up and remove the tags.
Certain formatting removal is not a problem, but we will be replacing
paragraphs and line breaks with the correct ASCII.

Mark

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I have just tested this

CREATE TABLE [products] (

[ProductId] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,

[ProductName] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[productShortDescription] [text] COLLATE Latin1_General_CI_AS NULL)

GO


INSERT Products(ProductName, ProductShortDescription) VALUES('Alien Chest
Burster' ,'<P>Few moments in film are as horrifyingly shocking as the moment
where the Alien Chest Burster makes its first appearance at the dinner table
in the sci-fi thriller <b>"Alien"</b>. </P>')


and results are this

"ProductId","ProductName","productShortDescription "
1,"Alien Chest Burster","<P>Few moments in film are as horrifyingly shocking
as the moment where the Alien Chest Burster makes its first appearance at
the dinner table in the sci-fi thriller <b>""Alien""</b>. </P>"


When I was asked what I wanted the destination text file to look like DTS
had added a check to the "Binary" column. I unchecked this

Even with binary checked I get

"ProductId","ProductName","productShortDescription "
1,"Alien Chest Burster","<P>Few moments in film are as horrifyingly shocking
as the moment where the Alien Chest Burster makes its first appearance at
the dinner table in the sci-fi thriller <b>""Alien""</b>. </P>"


Is this not what you want ?


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Mark Roberts" <mark (AT) freshegg_nospam_ (DOT) co.uk> wrote

Hi Allan,

I think I may have been unclear in my previous post - apologies

What we are trying to do is remove the HTML code so all HTML formatting is
cleaned. The export procedure will run a query on the table, remove the HTML
code from 1 of the column data and export the output to a CSV file.

Therefore the following in the current table:

"ProductId","ProductName","productShortDescription "
1,"Alien Chest Burster","<P>Few moments in film are as horrifyingly
shocking
as the moment where the Alien Chest Burster makes its first appearance at
the dinner table in the sci-fi thriller <b>"Alien"</b>. </P>"

will get exported to a CSV as:

ProductId,ProductName,productShortDescription
1,Alien Chest Burster,Few moments in film are as horrifyingly shocking
as the moment where the Alien Chest Burster makes its first appearance at
the dinner table in the sci-fi thriller Alien(VBCrlf)

etc..

Hope this makes more sense.

Thanks for your continued help.

Mark

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Oh. Yes I did read it incorrectly.

You can use REPLACE but you will need to run through a lot of permetations
on each row of data. Ideally you would want a Regular Expression solution
that would eliminate all text inside and including <>. There is currently
no way of doing this internally SQL Server presently un a function or the
like but you may be able to pass your text file off to an external VB/VB.net
exe and have it return a cleaned up text file.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Mark Roberts" <mark (AT) freshegg_nospam_ (DOT) co.uk> wrote

In message <#dUfIyU0DHA.2448 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Mark Roberts
<mark (AT) freshegg_nospam_ (DOT) co.uk> writes
How about using an ActiveX Script Transform in for the
productShortDescription. In the VBScript you can do some procedural type
code to strip the Html, and can even use regular expressions-

Dim oRegEx
Set oRegEx = CreateObject("VBScript.Regexp")


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Hi there,
Here are my two cents..

CREATE FUNCTION [dbo].[HTMLCLEAN] (@string nvarchar(4000) )
RETURNS nvarchar(4000) AS
BEGIN
Declare @posStr int
Set @posStr=CharIndex('<',@string)
While @posStr > 0
Begin
Set @string=Stuff(@string,@posStr,CharIndex('>',@strin g)-@posStr+1,'')
Set @posStr=CharIndex('<',@string)
End
Return @string
END