{SIMPLE}Adding all my 20 excel worksheets (900,000 rows each) to ONE powerpivot table.

[tony0217]

This question is related to something so simple that there was never a video or anything posted about it. heres the problem.

I am using Excel 2010, with the powerpivot add-in and everything is up to date and state of the art.
I have 20 excel worksheets that i would like to basically connect as one large 20 million row spreadsheet.
Thats it.
ive tried everything that i could imagine and nothing is working.
As it stands i have to update each file separately on a daily basis and each file takes about 5 minutes to open and 5 minutes to save costing me about 200 minutes a day (3 hours 20 minutes) of sitting around.
i want to be able to do one update, on one file.
Boom! easy right? then how come i cant find it??

please help me i think ive covered everything.
all i keep hearing is "oh wow powerpivot is the greatest and some can hold up to 150 million rows of data!!"
yeah?? how??
please please help.

thanks in advance- anthony.

 

[tigeravatar]

For that much data, Excel is not the right tool. I recommend using Access or any other relational database software.

 

[tony0217]

For that much data, Excel is not the right tool. I recommend using Access or any other relational database software.

are you sure? ive seen this done before..
is there a way that i can have multi million row capability with a spreadsheet interface in some program out there??

 

[powerpivotpro]

Yes, save them all as CSV files. Then from cmd prompt, copy *.csv combined.csv

Then import the resulting combined.csv file into PowerPivot. Great question.

 

[pete234]

I'm curious as to what you mean by "it isn't working."
What did you try to do? It sounds like you want to import 20 sheets seperately into power pivot, perform some transformations (with some type of relationship) and output the data again?
If you combine into one big sheet, what will you do with the data?
Trying to filter (to output) on that many rows especially if there are complex calculations, I would guess you would get constant "out-of-memory" errors.
And then trying to select all and copy that much data...I don't think that will work.
I get this now on less than 200k records with only 2 pivot tables.
It could be easier to set up an import spec in Access for each sheet, write your queries, and then automatically output the results into csv or xslx.
Once setup, it would take minutes each.
But you might need more than one db as the the file size limit might be hit quickly depending on how many fields are in each sheet.

 

[tony0217]

I'm curious as to what you mean by "it isn't working."
What did you try to do? It sounds like you want to import 20 sheets seperately into power pivot, perform some transformations (with some type of relationship) and output the data again?
If you combine into one big sheet, what will you do with the data?
Trying to filter (to output) on that many rows especially if there are complex calculations, I would guess you would get constant "out-of-memory" errors.
And then trying to select all and copy that much data...I don't think that will work.
I get this now on less than 200k records with only 2 pivot tables.
It could be easier to set up an import spec in Access for each sheet, write your queries, and then automatically output the results into csv or xslx.
Once setup, it would take minutes each.
But you might need more than one db as the the file size limit might be hit quickly depending on how many fields are in each sheet.

once i get all 20 sheets combined into one all i need to do is a basic spreadsheet filter on each column. thats it. im not intending to do all of the fancy calculations and pivot tables. just this one filtering operation. i just wish i could find a spreadsheet with an unlimited amount of rows, thats what i need. if anybody knows where to find that please let me know.

I just want to make a large spreadsheet and filter..thats it!

thanks for all your help guys..

@powerpivotpro, im still trying your method, thanks

 

[Laurent C]

You can use Excel to query data from any RDBMS.

Important questions are: what "updates" do you perform on the data? Where do your original 20 millions of rows come from? How do you filter the data? What do you do with the filtered data?