Extraction software

Hi,
I need an extraction software that needs to be capable of capturing a table
(or tables) displayed on the web and converting it to a file as a list of
records with
comma separated values. The records will be converted to insert commands
after the cleansing process. The software will be used for the data
warehousing and mining project I have at UNF.
Thank you,
Mihaela
Google on "screen scraper" and you should get lots of information on these
kinds of products/techniques.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mihaela O" <MihaelaO@.discussions.microsoft.com> wrote in message
news:65926D4C-DF67-4129-877B-544D138A62FB@.microsoft.com...
> Hi,
> I need an extraction software that needs to be capable of capturing a
table
> (or tables) displayed on the web and converting it to a file as a list of
> records with
> comma separated values. The records will be converted to insert commands
> after the cleansing process. The software will be used for the data
> warehousing and mining project I have at UNF.
> Thank you,
> Mihaela
>

Extraction software

Hi,
I need an extraction software that needs to be capable of capturing a table
(or tables) displayed on the web and converting it to a file as a list of
records with
comma separated values. The records will be converted to insert commands
after the cleansing process. The software will be used for the data
warehousing and mining project I have at UNF.
Thank you,
MihaelaGoogle on "screen scraper" and you should get lots of information on these
kinds of products/techniques.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mihaela O" <MihaelaO@.discussions.microsoft.com> wrote in message
news:65926D4C-DF67-4129-877B-544D138A62FB@.microsoft.com...
> Hi,
> I need an extraction software that needs to be capable of capturing a
table
> (or tables) displayed on the web and converting it to a file as a list of
> records with
> comma separated values. The records will be converted to insert commands
> after the cleansing process. The software will be used for the data
> warehousing and mining project I have at UNF.
> Thank you,
> Mihaela
>

Extracting and converting text fields.

Hi All
I have a bug where a text field is saving text to a limit of 256 characters,
which is no where near enough. The table structure is an ID with an
identity Int, and a field called responseText.
I cannot run an alter table as "alter table tempresponses alter column
responsetext varchar(5000)"
Because "Cannot alter column 'responsetext' because it is 'text'."
I have tried to add a temporary column, but this makes it worse:
Alter table tempresponses add temp_column varchar(5000) --Add temp
column
update tempresponses set temp_column = cast(ResponseText as varchar) --
Fill out. Size irrelevant as is max 256 currently
alter table tempresponses drop column ResponseText -- Drop the TEXT
alter table tempresponses add ResponseText varchar (5000) -- read as
varchar
update tempresponses set ResponseText=temp_column
alter table tempresponses drop column temp_column
But this doesn't seem to work because it promptly crops the data at 30 chars
on doing the cast. I cannot see why. I got this solution from the
Microsoft Online Books.
Is there anything else I can try?
I have two tables that are doing this currently, and people are going to be
losing data if I cant figure this out. Shutting the database down is about
my only option at this stage.
Help.
Many Thanks
Ashley> I have a bug where a text field is saving text to a limit of 256
> characters,
Are you sure that is all that is being saved? Or are you relying on Query
Analyzer in its default configuration and counting the characters that it
returns? Try SELECT DATALENGTH(column) to see if the length really is
255/256.
This is a very common symptom and *almost always* it is a mis-diagnosis
because of http://www.aspfaq.com/2272.
If the data really isn't getting into the table, then it is usually either a
stored procedure @.param declaration issue, where the defined size is
VARCHAR(x), or a problem in the application that is generating the call to
do the insert.|||Yeah - the stored procedure that initially padded the table was varchar so I
fixed that instead. I did wonder for a while if it was Query Analyser.
The mis-diagnosis info was very useful and I have put into a FAQ db that I
use for my own references. Sweet, thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uQX11p8kFHA.4000@.TK2MSFTNGP12.phx.gbl...
> Are you sure that is all that is being saved? Or are you relying on Query
> Analyzer in its default configuration and counting the characters that it
> returns? Try SELECT DATALENGTH(column) to see if the length really is
> 255/256.
> This is a very common symptom and *almost always* it is a mis-diagnosis
> because of http://www.aspfaq.com/2272.
> If the data really isn't getting into the table, then it is usually either
> a stored procedure @.param declaration issue, where the defined size is
> VARCHAR(x), or a problem in the application that is generating the call to
> do the insert.
>|||On Fri, 29 Jul 2005 11:04:37 +1200, "amb" <@.> wrote:
(snip)
Hi Ashley,
Aaron already replied to your main question, but he didn't comment on
this part:

>update tempresponses set temp_column = cast(ResponseText as varchar) --
(snip)
>But this doesn't seem to work because it promptly crops the data at 30 char
s
>on doing the cast. I cannot see why.
If varchar (or char) is specified without length, it will in most cases
default to a length of 1 character. The only exception is in a CAST
function: for reasons beyond me, the default is set to 30 characters in
this context.
That's why the statement above crops your data to 30 characters.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)