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.

Exporting to Excel - renaming tabs

I have created an adhoc report in VS which I then export to Excel. The
way the report is set up is that I have a seperate tab(worksheet) for
each company that features on the report. However, I would like the
company name on each tab instead of the generic sheet1, sheet2 etc..
Any ideas welcome!I don't have an answer to your question, but how do you manage to get more
than one tab on the exported Excel file?
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Mark" <markbtmn@.gmail.com> wrote in message
news:2e8aa1cb-4970-43e9-852b-ac9a18551741@.v67g2000hse.googlegroups.com...
>I have created an adhoc report in VS which I then export to Excel. The
> way the report is set up is that I have a seperate tab(worksheet) for
> each company that features on the report. However, I would like the
> company name on each tab instead of the generic sheet1, sheet2 etc..
> Any ideas welcome!|||On Feb 8, 7:28=A0am, "Alain Quesnel" <alainsanss...@.logiquel.com> wrote:
> I don't have an answer to your question, but how do you manage to get more=
> than one tab on the exported Excel file?
> --
> Thank you,
> Alain Quesnel
> alainsanss...@.logiquel.com
> www.logiquel.com
> "Mark" <markb...@.gmail.com> wrote in message
> news:2e8aa1cb-4970-43e9-852b-ac9a18551741@.v67g2000hse.googlegroups.com...
>
> >I have created an adhoc report in VS which I then export to Excel. The
> > way the report is set up is that I have a seperate tab(worksheet) for
> > each company that features on the report. However, I would like the
> > company name on each tab instead of the generic sheet1, sheet2 etc..
> > Any ideas welcome!- Hide quoted text -
> - Show quoted text -
Mark,
Out of the box, RS does not have the functionality to rename the
tabs. Folks in my company end up doing some hefty gymnastics
with .NET and/or macros. This link claims to offer a solution but
I've not been able to get it to work. Let us know if you have better
results than me.
http://www.spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS=
-Excel-QnD-XSLT.aspx
Alain,
To get separate tabs by group, check the Page break at end check box
on the edit group dialog.
HTH you both|||On Feb 8, 6:14=A0pm, toolman <t...@.infocision.com> wrote:
> On Feb 8, 7:28=A0am, "Alain Quesnel" <alainsanss...@.logiquel.com> wrote:
>
>
> > I don't have an answer to your question, but how do you manage to get mo=re
> > than one tab on the exported Excel file?
> > --
> > Thank you,
> > Alain Quesnel
> > alainsanss...@.logiquel.com
> >www.logiquel.com
> > "Mark" <markb...@.gmail.com> wrote in message
> >news:2e8aa1cb-4970-43e9-852b-ac9a18551741@.v67g2000hse.googlegroups.com...=
> > >I have created an adhoc report in VS which I then export to Excel. The
> > > way the report is set up is that I have a seperate tab(worksheet) for
> > > each company that features on the report. However, I would like the
> > > company name on each tab instead of the generic sheet1, sheet2 etc..
> > > Any ideas welcome!- Hide quoted text -
> > - Show quoted text -
> Mark,
> Out of the box, RS does not have the functionality to rename the
> tabs. =A0Folks in my company end up doing some hefty gymnastics
> with .NET and/or macros. =A0This link claims to offer a solution but
> I've not been able to get it to work. =A0Let us know if you have better
> results than me.http://www.spacefold.com/lisa/post/Changing-the-Sheet-name=
s-in-SQL-Se...
> Alain,
> To get separate tabs by group, check the Page break at end check box
> on the edit group dialog.
> HTH you both- Hide quoted text -
> - Show quoted text -
Hey toolman,
Apologies in the slow response to your posting. Gave the link a try
but after spending some time on it I still couldn't get it to work.
Seems very long winded just to get some tab names working. We use
another BI reporting tool called Microstrategy which makes it simple
to do this as all you have to do is create a template with named tabs
and link it to your report - simple! Microsoft should take a leaf out
of these guys book! Doesn't help me but maybe i'll find something else
on the net.
Cheers

Exporting to Excel

I've created several reports in VS2005 and have deployed them to my RS.
I would like each one of these reports to be a seperate worksheet in an
Excel workbook.
Is it possible to Export multiple reports as seperate worksheets to one
Excel workbook'
Thanks
--
ChrisOn May 7, 11:45 am, Chris <C...@.discussions.microsoft.com> wrote:
> I've created several reports in VS2005 and have deployed them to my RS.
> I would like each one of these reports to be a seperate worksheet in an
> Excel workbook.
> Is it possible to Export multiple reports as seperate worksheets to one
> Excel workbook'
> Thanks
> --
> Chris
If you place all of the reports into one report this can work.
Create a new report. Place each report as a sub report in the new
report. Then make "page breaks" after each sub report. The page break
should create a new worksheet in the excel document.
D. Spohn|||Thank you so much!
--
Chris
"dspohn" wrote:
> On May 7, 11:45 am, Chris <C...@.discussions.microsoft.com> wrote:
> > I've created several reports in VS2005 and have deployed them to my RS.
> > I would like each one of these reports to be a seperate worksheet in an
> > Excel workbook.
> > Is it possible to Export multiple reports as seperate worksheets to one
> > Excel workbook'
> >
> > Thanks
> > --
> > Chris
> If you place all of the reports into one report this can work.
> Create a new report. Place each report as a sub report in the new
> report. Then make "page breaks" after each sub report. The page break
> should create a new worksheet in the excel document.
>
> D. Spohn
>|||I have a report with 3 sub reports where I placed page breaks after each one.
When I deploy to my RS and run the report it does come up with 4 pages, but
when I select Excel and then export only 3 worksheets are created instead of
4. Do you have any idea why this is happening'
Thanks in advance.
--
Chris
"Chris" wrote:
> Thank you so much!
> --
> Chris
>
> "dspohn" wrote:
> > On May 7, 11:45 am, Chris <C...@.discussions.microsoft.com> wrote:
> > > I've created several reports in VS2005 and have deployed them to my RS.
> > > I would like each one of these reports to be a seperate worksheet in an
> > > Excel workbook.
> > > Is it possible to Export multiple reports as seperate worksheets to one
> > > Excel workbook'
> > >
> > > Thanks
> > > --
> > > Chris
> >
> > If you place all of the reports into one report this can work.
> >
> > Create a new report. Place each report as a sub report in the new
> > report. Then make "page breaks" after each sub report. The page break
> > should create a new worksheet in the excel document.
> >
> >
> > D. Spohn
> >
> >