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.