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



## tony0217 (Aug 14, 2013)

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 (Aug 14, 2013)

please! anybody?


----------



## powerpivotpro (Aug 14, 2013)

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


----------



## tony0217 (Aug 14, 2013)

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 (Aug 14, 2013)

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


----------



## powerpivotpro (Aug 14, 2013)

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 (Aug 14, 2013)

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 (Aug 14, 2013)

and im using a 64-bit os


----------



## powerpivotpro (Aug 14, 2013)

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 (Aug 15, 2013)

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


----------



## tony0217 (Aug 14, 2013)

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 (Aug 15, 2013)

I know deep down in my soul that this is indeed EXACTLY what i need! unfortunately i keep getting this " The SQL statement is not valid. there are no columns detected in the statement" the name of the file is 283.xlsx
can you tell me what i may be doing wrong? i wrote it like this:   """""    

SELECT [Sheet1$].*   FROM [Sheet1$]

Union ALL
SELECT * FROM `C:\Desktop\283.xlsx`.['sheet1$']


"""""


----------



## trebligbc (Aug 15, 2013)

tony0217 said:


> I know deep down in my soul that this is indeed EXACTLY what i need! unfortunately i keep getting this " The SQL statement is not valid. there are no columns detected in the statement" the name of the file is 283.xlsx
> can you tell me what i may be doing wrong? i wrote it like this:   """""
> 
> SELECT [Sheet1$].*   FROM [Sheet1$]
> ...


-----------
When I did this a couple of months ago (PowerPivot 2010), I first created a Data Connection in Excel.  Then, when I went into the PowerPivot Window, I went to the Existing Connections on the Design tab to point to the initial "workbook connection" (usually at the very bottom of the list... thanks to Microsoft) which allowed me to get the first link established inside of PowerPivot.

Then, after that imported the first portion of the data, I went to the Table Properties and switched over to the Query Editor to edit the auto-generated SQL statement with the additional 
     UNION ALL
     SELECT * FROM `C:\FolderName\WorkbookName.xlsx`.[WorksheetTabName]

lines.

Did/were you able to read in the first sheet of data before you modified the SQL statement that PowerPivot automatically generates?


----------



## tony0217 (Aug 15, 2013)

yes i got the first file to import but when i add the union all line i get that error message...."sql statement invalid...no columns detected" i know its what i need. i even watched the video. i seem to be doing the same steps exactly but somethings not right


----------



## trebligbc (Aug 15, 2013)

When I did this a couple of months ago (PowerPivot 2010), I first created a Data Connection in Excel.  Then, when I went into the PowerPivot Window, I went to the Existing Connections on the Design tab to point to the initial "workbook connection" (usually at the very bottom of the list... thanks to Microsoft) which allowed me to get the first link established inside of PowerPivot.

 Then, after that imported the first portion of the data, I went to the Table Properties and switched over to the Query Editor to edit the auto-generated SQL statement with the additional 
     UNION ALL
     SELECT * FROM `C:\FolderName\WorkbookName.xlsx`.[WorksheetTabName]

 lines.

 Did/were you able to read in the first sheet of data before you modified the SQL statement that PowerPivot automatically generates?


----------



## trebligbc (Aug 15, 2013)

trebligbc said:


> 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
> ...



----------Sorry for the delay in getting back...
I had to re-construct the thing myself, and my notes weren't as good as they should have been.

What I needed to do was create a {Data | Connection} for each worksheet (twelve in the case of bringing in 12 months where each month in contained on its own worksheet tab) BEFORE I imported the first file.
Then, modify the SQL statement under the Query Editor of the Table Properties for over on the PowerPivot side!

 
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$']  

It was a little quirky getting it going, and there may need to be a space at the end of each line to make it work.  (I was scrambling!)

Sorry..., I'm not really a SQL-guy...I just needed something that worked, and this is what I found to accomplish the task.


 Chris


----------



## tony0217 (Aug 15, 2013)

no problem with the delay. im just glad you were able to help. it looks like the part that i was missing was making the connection for EACH book. contextures didnt mention that part but it does seem to be working and i thank you for your responses. maybe i will be able to answer a question for you one day. 

thanks chris.

Sincerely, anthony.


----------



## RobertCotton (Aug 17, 2013)

I know this is an excel forum but this seems like a good opportunity to combine with the use of Access.  If you build a single table in Access using the same fields and properties as your Excel spreadsheets then append all of the data from each of the spreadsheets into the table you can then link powrepivot to the Access table and query your data that way.  Just a thought.


----------



## miguel.escobar (Aug 17, 2013)

you might want to use Power Query. Check out this video
Combine Multiple Files or Append All to one Table - Power Query a.k.a. Data Explorer for Excel - YouTube


----------



## sandracoen (Jul 17, 2014)

*can anyone help me with this query ?*

Hello,

Can anyone tell me what is wrong with my query?
SELECT [Mojn$].* FROM [Mojn$]
UNION ALL
SELECT * FROM `C:\Users\sandracoen\Dropbox\docs\Reports\All Client\Data\Delta.xlsx`.[Delta$]


I get the error message "The SQL statement is not valid. There are no columns detected in the statement."


I have tried everything you wrote but not working 

thanks
Sandra


----------



## XLBob (Jul 17, 2014)

*Re: can anyone help me with this query ?*

try power query. it's the new tool for doing this and a bunch of other data shaping tasks


----------

