Multiple sheet querries

[G Ray]

I want to bring a lot of data into Excel from a database. Using SQL I
can retreive much more data than one worksheet can hold.

I have found I can create and name a range that includes multiple
worksheets.

If I retreive 80,000 records from a database can I import them into my
multi-sheet range?
Does Excel recognize such a range as a contineous list of data?

I want to analyze this type of data with averages and statistical
functions and create graphs to show the results of my analysis.

Will Excel be able to graph across my multiple sheet range?

Thanks
Glen

 

[JLatham]

I'm going to touch on the last question and then move on:
Check Excel Help for charting specifications, and you'll find (both in Excel
2003 and 2007)
Data series in one chart: maximum 255
Data POINTS in a data series for 2-D charts: max 32,000
Data POINTS in a data series for 3-D charts: max 4,000
Data points for ALL data series in one chart: max 256,000

So see if there's even a possibility of charting the information. Even with
just 1 series in a 2-D chart, it appears you exceed Excel's data points
limit. The 'work around' is to graph different pieces of the data and
overlay the charts.

For the other questions, I'm sorry, I just don't have the answers, hopefully
someone else will.

 

[G Ray]

JLatham,
Thanks for your reply. I hadn't checked on the number of points that
could be charted. Thanks for that info.

My plan is to analyze the data and plot the results. The data I'll be
working with is quantitative and associated with a timestamp. I'll be
looking at one minute increments over a 90 day period. Thats very
close to two worksheets full of records. (129600 records vs 131072
capacity for two sheets).

From the numbers you provided it looks like I should be able to chart
five minute averages for up to nine series per chart. I don't think
my charts will be that ambitious.

I really appreciate your help. Thanks

Glen

 

[JLatham]

Without answering the rest of your questions yet again, I'm going to offer up
a couple of potential solutions for your consideration. And that is NOT to
say that you need even consider either one if you don't want.

First: if your database/data source device can output the data to a CSV type
of file, I have a tool available that can take a large CSV file consisting of
more rows of data than will fit on a single sheet and import it into an Excel
workbook, automatically creating new pages as needed to accomodate all of the
data. That could help with the question of "can a query import across
multiple sheets". From there you could then decide how to chart it to meet
your needs. If you want to explore this possibility, contact me via email
and ask for a copy of my "import excess rows of data from CVS file" workbook.
Email is (remove spaces)
Help From @JLatham Site. com

Second: And I offer this up with a recommendation to use a lot of caution in
your decision about it. Consider obtaining a trial copy of Excel/Office 2007
and seeing if it can meet your needs. A single sheet can hold over a million
rows of data. Now, having said that, I must relate an early similar
experience with Excel 2007: I made a similar recommendation and the
individual spent the $$ for Office 2007. We were able to import the
necessary data (88,000 rows) But we were stopped in our tracks at the
attempts to graph it on several fronts; even though we were breaking the 88K
rows into groups to eventually create 50+ graphs, we couldn't do it on a
single sheet as had been hoped - Excel lumped the total number of data points
for all charts as belonging to a single chart! We hit the 32000 data point
limit after just a few charts. That could happen again? Also at that time,
the time it took to do anything with a chart in 2007 took forever. Bottom
line, it took 10 minutes to do the job in 2007 that it took only 1.5 minutes
to do in 2003 and we had to go back to splitting data across multiple sheets
with 1 chart per sheet in 2007 just as we did it in 2003 to get the job done.

So if you want to try it with 2007: get a trial copy, install it on an
avaliable machine so that it doesn't interfere with your installation of
2003, or on a Virtual Machine. Try it out and see if it looks like a
solution before expending the $$ for a copy of 2007.

 

[G Ray]

Thanks again for your insights.
I was not aware 2007 expanded the worksheet size and your observations
of its performance are greatly appreciated.

When you brought 88000 records into 2003 did you create a named range
across the two worksheets?

I've been home sick for a few days and not been able to test any of
this out. I have been able to create a single named range across two
worksheets but I don't have data to fill it.

I expect to find that if I make a formula to reduce the data to five
minute averages and copy it down (<27k rows) it will work across the
two worksheets. I will have separate sheets for graphs and will
probably hide the data sheet(s).

Thanks again

Glen