I just want to combine these tables! This should be simple!!!!!!!!!!!!!!! Please help me!

[tony0217]

So i have been working at this for a week and decided to do away with the pride and just ask an expert.

im using excel 2010-windows 7- 8gb of ram-

so for some reason my powerpivot program only imports 21 million out of 50 million and says success( i really dont get that.) so if you can help with that please do so firstly.

the MAIN problem im having is:

I have these three tables 1st 20 million records 2nd 20 million records 3rd 10 million records.
all tables have IDENTICAL columns and headers. they all have the same format.
the trouble is that i dont want to have to do 3x three times the work.
Basically i would like to learn a way to combine these files as one and do a couple filters on them.

i have tried relationships but to no success. besides i dont even think thats what i want.
this should be simple.. i just want to combine these tables into one big table of 50 million records. thats what i got the program for in the first place.
please help. i may even send you $100 bucks if thats not illegal or against the rules of this website. thank you. i am eagerly awaiting your help.

 

[tony0217]

please! anybody?

 

[powerpivotpro]

Where did the data originally come from? How did it get into Power Pivot in the first place?

 

[tony0217]

all files were originally .xlsx files. i then converted them into csv files and imported them as such. 20 mill, 20 mill, and 10 mill. for some reason powerpivot wont let me get past 21 mill at once. (but it used to)

 

[tony0217]

ive never needed help as badly as i need it now.

 

[powerpivotpro]

Have you tried jamming them all together into one?

Combine Multiple Worksheets/Workbooks into a Single PowerPivot Table « PowerPivotPro

Sounds like you have, since you've hit a limit around 21M rows?

If so, was the error THIS one?

Workarounds for “Canceled Due to Memory Pressure” « PowerPivotPro

The other HUGE thing here is to switch to 64-bit Excel and Power Pivot:

http://www.mrexcel.com/forum/powerp...-32bit-vs-64bit-other-system-suggestions.html

 

[tony0217]

when i tried to import the 50 million, i did not recieve an error message. it said success. but it only imported 21/ 50 million rows

 

[tony0217]

and im using a 64-bit os

 

[powerpivotpro]

Ah then there probably must be some sort of "glitch" in the data, like a header row or a spacer row or maybe even a row that contains an illegal value or something.

It's hard to find those things since "viewers" for 21M row text files are rare. I don't know - maybe Notepad++ can handle it? But you may look at the original XLSX/CSV files - at the top and bottom of each one - to see if some "air" sneaked in somewhere.

PS - If you are using 64-bit OS, that doesn't mean you are running 64-bit Excel and Power Pivot.

 

[trebligbc]

I had the same issue! Separate spreadsheet tabs, each with a month's worth of transactions. I edited the Table Properties of the first imported table, to let SQL pull in the remaining data to be appended.

SELECT [' Jan Order1$'].* FROM [' Jan Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['Feb Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['Mar Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['Apr Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['May Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['Jun Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['Jul Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['Aug Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['Sep Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['Oct Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['Nov Order1$']
Union ALL
SELECT * FROM `C:\Analysis\Data2012.xlsx`.['Dec Order1$']

demonstrated in this online video.


http://www.contextures.com/PowerPivot-Identical-Excel-Files.html

The key to following the contextures example (what eluded me at first) was the use of the grave_accent (unshifted tilde "~" key) to surround your workbook name, before the ".[worksheet_name]" part.

Hope this helps.
Chris Gilbert
cgilbert@jerviswebb.com