If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple sheet querries
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 |
#2
|
|||
|
|||
Multiple sheet querries
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" wrote: 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 . |
#3
|
|||
|
|||
Multiple sheet querries
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 On Mar 19, 11:31*am, JLatham wrote: 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" wrote: 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 .- Hide quoted text - - Show quoted text - |
#4
|
|||
|
|||
Multiple sheet querries
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" wrote: 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 On Mar 19, 11:31 am, JLatham wrote: 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" wrote: 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 .- Hide quoted text - - Show quoted text - . |
#5
|
|||
|
|||
Multiple sheet querries
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 On Mar 19, 6:30*pm, JLatham wrote: 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 acrossmultiplesheets". *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 acrossmultiplesheets 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" wrote: 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 twoworksheetsfull 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 On Mar 19, 11:31 am, JLatham wrote: 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" wrote: 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 includesmultiple 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 mymultiplesheet range? Thanks Glen .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|