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]

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]

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


"""""

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

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]

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]

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

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

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]

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]

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]

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]

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