Extract data from 350 seperate Excel Files

We have used a template for 350 excel files and now we are trying to
extract certain information from these files to either one excel file
or to an access database. The problem is that in this template the rows
are not necessarily the same in each file. (E.G. If a company started
in 1995 the corresponding rows and columns for the 2000 data will be
different than a company that started in 1999.) I also would like to
change the column headings to rows and the rows into heading columns. I
know its a big task and I am not so sure how to begin. Any thoughts
would be appreciated.<acaseutk@.gmail.com> wrote in message
news:1142284202.900306.197310@.i40g2000cwc.googlegroups.com...
> We have used a template for 350 excel files and now we are trying to
> extract certain information from these files to either one excel file
> or to an access database. The problem is that in this template the rows
> are not necessarily the same in each file. (E.G. If a company started
> in 1995 the corresponding rows and columns for the 2000 data will be
> different than a company that started in 1999.) I also would like to
> change the column headings to rows and the rows into heading columns. I
> know its a big task and I am not so sure how to begin. Any thoughts
> would be appreciated.
I sympathise with you. I seem to have spent much of my career trying to
educate accountants that a spreasheet is a totally lousy way to store data.
My suggestion is that you use Excel macros or cut and paste to get the data
as straight as you can first. Then try saving the files in delimited form
(again you can automate with macros) and import from the intermediate format
to some staging tables. Then you have LOTS of validation and transformation
to do.
You can try DTS or Integration Services straight from the Excel sheets but
in my experience this rarely works in your situation. Each file will have
different formatting, column widths, heading, etc and DTS will choke again
and again unless you are lucky. I can't say I've tried it with IS though -
maybe some things have improved.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||How did you know I was an accountant...Haha. Thanks for the suggestion
I will look into it.

extra space at bottom

Hi;
i want to add extra separation between lines when a field has certain
value... right now, i tried bottom padding and Lineheight but none seems to
work...
this is the expression i had on my table detail's line bottom pading :
=iif(fields!UTIL.value="T",10,2)
Any advice?found the problem...
i has to be: =iif(fields!UTIL.value="T","10pt","2pt")
"Willo" <willoberto@.yahoo.com.mx> wrote in message
news:%235Wc$9FgHHA.668@.TK2MSFTNGP05.phx.gbl...
> Hi;
> i want to add extra separation between lines when a field has certain
> value... right now, i tried bottom padding and Lineheight but none seems
> to work...
> this is the expression i had on my table detail's line bottom pading :
> =iif(fields!UTIL.value="T",10,2)
> Any advice?
>
>

External Images - 404 Not Found and the red X image

Hello,
I have a report that is attempting to go out and grab images for certain
items off of a website. The URL is basically a guess and when it is
incorrect (404 File Not Found) RS returns an image of a red X. Is there an
expression I can use to hide the visibilty of the red X if one should appear?
I havn't been able to figure out how to do this.
Thanks!
Paulchecking the binary stream return value and if there is nothing then hide
the column displaying the image
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:616E1484-1B3D-4393-8974-F7C616A6051A@.microsoft.com...
> Hello,
> I have a report that is attempting to go out and grab images for certain
> items off of a website. The URL is basically a guess and when it is
> incorrect (404 File Not Found) RS returns an image of a red X. Is there
> an
> expression I can use to hide the visibilty of the red X if one should
> appear?
> I havn't been able to figure out how to do this.
> Thanks!
> Paul|||Ok, thanks for the post. How exactly do I go about checking the binary
stream return value in RS?
"Vaibhav" wrote:
> checking the binary stream return value and if there is nothing then hide
> the column displaying the image
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:616E1484-1B3D-4393-8974-F7C616A6051A@.microsoft.com...
> > Hello,
> >
> > I have a report that is attempting to go out and grab images for certain
> > items off of a website. The URL is basically a guess and when it is
> > incorrect (404 File Not Found) RS returns an image of a red X. Is there
> > an
> > expression I can use to hide the visibilty of the red X if one should
> > appear?
> > I havn't been able to figure out how to do this.
> >
> > Thanks!
> > Paul
>
>

Exporting Stored Procedures

I have to retrieve all the stored procedures in a certain database via a aspx page. My problem is that I have basically no idea how to do it. I did manage to get all the data as XML and reconstruct the database (pain). All I need now (I hope) is the sprocs. I do not have any other access to the database. And I'm a novice with SQL Server. Any guidance is appreciated.

SteveSomething like:

select text
from syscomments
where text like 'create procedure%'

|||Thank you very much.

I also have to restore the sprocs. Should I select all the columns in syscomments and sysobjects for the relevent rows and insert them into the new db? Will that do it?

Thanks again,

Steve|||NO, I do not recommend this approach.. Why are you looking to perform these activities through an asp.net application?|||I have a client with a developer problem. They asked me to coordinate with the developer to facilitate moving the site. Despite the developer's statements that I could access the SQL Server, he only gave me FTP access to the site. Eventually, I find out that he is intentionally being difficult, refusing to assist unless the client paid him more money. Apparently a _lot_ more money. I found in his connection code where he recently changed the pw to the database, to something other than what he told me. I assume the developer is being unreasonable to the point where it is cheaper to have me do it the "hard" way. Oh yeah, this is a classic ASP app, which I have no experience with either.

So... I had to use an aspx page to get the data via XML. Easy enough, but restoring it was a bit more challenging. I finally have the tables restored properly; now all I need is the stored procedures (I hope).

I did get the procedures (thanks again), however I had to get all the rows because none of the sprocs begin with the string you suggested, and I'm wanderin' around in the dark.

I'm amazed I've accomplished what I have. It's a good thing .NET is so smart. :)

Steve|||Thanks to your help, I was able to completely reconstruct the database!

I had to manually re-add the stored procedures, views & triggers I got from your suggestion. In the end, it was the easiest way.

Thanks again,

Steve