Beyond row 1048576, Can Powerpivot save me?

[slinky]

Hi all,


I'm using 32bit Excel 2010 in 2Gb Intel DuoCore Vista.


Question: I need to plot a linegraph of 5 million of datapoints with 2 variables, time(x-axis) and quantity(y-axis). I can import 1048576 rows of .txt in worksheet1, but failed to continue importing in worksheet2 the remaining data. I saved and closed the workbook and tried on a new workbook, but it failed too.


How do I do this with my computer capability?


I can import up to 3 million datapoints in Powerpivot, but the graph automatically 'Sum of Revenue' it up.


Please help and thank you all.

 

[Hui]

The answer is yes and No


Power pivot can handle a huge amount of data well in excess of your 5M points

The problem is that it is designed to process and summrise, not just store it.


Power pivot can import from Access, SQL or Text Files and so handling and summarising will be no problem, unless you end up with more than 1048576 summary points.


Lets take a step back


You say that you want to plot the data

an A4 page is 11.69" and at 300 dpi that only can handle a maximum of 3500 points.

So to plot your data you would have to use one point for every 1500 data points at 33 dpi.


on an A0 page you can plot about 12,000 data points at 300dpi, which is still one plot point per 416 data points.


On a screen with 1920 pixels wide, 1 pixel will represent every 2600 data points.


So you could use Power Pivot to retrieve a subset of the data as if you had zoomed in and then move through the data that way ?


Hope the above helps

 

[dan_l]

Access?


(seemingly my answer to everything)

 

[fred]

Exactly my thought too, Dan.

 

[slinky]

Thanks everyone!


I experimented PowerPivot again and took 5 minutes to import 7 million datapoints and additional 5 minutes to load the .txt file from hard disk. Hui was right on over 5 million data is not an issue. Well 'plot' means creating a graph on the pc not in a printout form, though the resolution advice is indeed a good one too.


Now I need to figure out how to ask PP to treat one datapoint as it is, and not wanting it to do auto sum.


Cheers all.