# UNION ALL sql statement



## natejlefebvre (Aug 2, 2013)

I am having trouble combining two different worksheets in one. See the below information:

_SELECT ['Chef Coats$'].*   FROM ['Chef Coats$']_
_UNION ALL_
_SELECT *_

_FROM 'C:\Users\nlefebvre\Documents\polos.xlsx'.[polos$]_

I keep getting the error message below, but I don't know what is wrong with the string.

"The SQL statement is not valid. There are no columns detected in the statement."


----------



## mikeTRON (Aug 2, 2013)

Did you create a data connection to Polos?


----------



## mikeTRON (Aug 2, 2013)

You also need to be VERY careful when copying and pasting text outside of the query editor as your apostrophes might not be exactly what it wants.
UNION ALL
SELECT * FROM *`*C:\SGP\Data\PowerPivotDATA\2009 S.xlsx*`*.[Normal$]


See the one in my code?  THAT has caused me about 30 minutes of headache before! The button you need to hit on the kleyboard is to the left of the 1 key, NOT to the left of the ENTER key.


----------



## natejlefebvre (Aug 2, 2013)

Thank you! That was way easier than I thought it would be!


----------



## mikeTRON (Aug 2, 2013)

Was that the problem?

I figured out the Union All method a few weeks ago and showed my boss. He had everything the same but still couldn't get it to work correctly. After a few hours of him messing around with it I walked over and traced it down to that one buton and that one character was of throughout.

Yeah and if you could now show me how to transpose crosstabular data sets IN that SQL statement we can be even 
Speaking of transposing data sets you should check out Data Explorer (aka Power Query) that is a free add-in that is pretty slick:
5 Cool Things You Can Do With Data Explorer » Bacon Bits
Transpose or Unpivot Entire Datasets with Data Explorer » Bacon Bits


----------



## bluesky63 (Oct 16, 2013)

Hi All

I am still having problem


SELECT [BINGO$].*   FROM [BINGO$]
UNION ALL
SELECT *  FROM `D:\Temp\TestMerge\PC11.xlsx`.[BINGO$]

Keep getting 'The SQL statement is not valid. There are no columns detected in the statement.'


----------



## S_Thomas (Jan 6, 2014)

Hello, Please can someone help? I have been looking on the web for answers but nobody has come up with a solution. All I am trying to do is to combine 2 wokrsheets from different files but I get the following error
"The SQL statement is not valid. There are no columns detected in the statement."
My SQL statement onthe PowerPivot is 
SELECT ['Book UPS$'].*   FROM ['Book UPS$']
WHERE [Date] IS NOT NULL
UNION ALL
SELECT * FROM ['C:\Test\New RFT Lower.xls'].['Book LPS$']
WHERE [Date] IS NOT NULL

Can anyone help... please !!

Thanks in advance
S


----------



## S_Thomas (Jan 6, 2014)

S_Thomas said:


> Hello, Please can someone help? I have been looking on the web for answers but nobody has come up with a solution. All I am trying to do is to combine 2 wokrsheets from different files but I get the following error
> "The SQL statement is not valid. There are no columns detected in the statement."
> My SQL statement onthe PowerPivot is
> SELECT ['Book UPS$'].*   FROM ['Book UPS$']
> ...




Resolved the issue with inserting the ` which is to the left of the 1 key (`C:\Test\test.xlx`and 
using ' key which is left of the # key inside the enclosed square brackets like ['Book LPS$']

But now I get the follwing error, please help
"OLE DB or ODBC error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done..
The current operation was cancelled because another operation in the transaction failed."

Thanks


----------



## S_Thomas (Jan 6, 2014)

Resolved the ODB error by doing the following
Problem:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
  Solution:
usually this happens when some wrong value is entered like a text value in Date or Number field.
Also this error happens when you trying to insert value into auto-increment field or some NOT NULL field left empty etc.


Double check everything I mentioned and if it doesn't help post your application to Demo Account (last tab in ASPRunnerPro).
Send me a URL where I can see this issue. Also I need a step-by-step instruction on reproducing this error.


----------



## S_Thomas (Jan 6, 2014)

Resolved the ODB error by doing the following
Problem:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Solution:
usually this happens when some wrong value is entered like a text value in Date or Number field.
Also this error happens when you trying to insert value into auto-increment field or some NOT NULL field left empty etc.


----------



## natejlefebvre (Aug 2, 2013)

I am having trouble combining two different worksheets in one. See the below information:

_SELECT ['Chef Coats$'].*   FROM ['Chef Coats$']_
_UNION ALL_
_SELECT *_

_FROM 'C:\Users\nlefebvre\Documents\polos.xlsx'.[polos$]_

I keep getting the error message below, but I don't know what is wrong with the string.

"The SQL statement is not valid. There are no columns detected in the statement."


----------



## mbizavin (Jun 19, 2015)

I would like to achieve a similar feat but instead of having data stored in my local drive, I would like to source data from a SharePoint location. I can connect the first file but I get an error when I try to connect subsequent files. The error reads _"The SQL statement is not valid. There are no columns detected in the statement."_

My folder link is: _https://ed-drive.dk.prime.<wbr>net/resear/sp2010demo/dk_<wbr>students_tool/Shared Documents

_I would like some assistance on how to approach this.


----------



## bluesky63 (Jun 21, 2015)

Hi mbizavin,  I cannot access to the folder link.


----------



## mbizavin (Jun 22, 2015)

bluesky63 said:


> Hi mbizavin,  I cannot access to the folder link.



Hello bluesky63, thanks for getting ack to me. Basically I wanted to give an idea on how my link looks like. I have 3 different workbooks stored in a SharePoint folder, each with a worksheet called _data_. All these worksheets have a similar structure. I'd like to aggregate the data from these worksheets using PowerPivot.


----------



## bluesky63 (Jul 3, 2015)

Hi,

Any solution for the error
OLE DB or ODBC error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.. The current operation was cancelled because another operation in the transaction failed.



SELECT * FROM `\\team.workplace.com@SSL\DavWWWRoot\subentity\ShSvc\Documents\Estate Management\Database\Occupancy database\Database_CD_Occupancy.xlsx`.['For Outlook(C1)$'] Where ([Userid] <> "")

UNION ALL

SELECT * FROM `\\ team.workplace.com @SSL\DavWWWRoot\subentity\ShSvc\Documents\Estate Management\Database\Occupancy database\Database_CD_Occupancy.xlsx`.['For Outlook(RO)$'] Where ([Userid] <> "")

UNION ALL
SELECT * FROM `\\ team.workplace.com @SSL\DavWWWRoot\subentity\ShSvc\Documents\Estate Management\Database\Occupancy database\Database_AB_Occupancy.xlsm`.['For Outlook$']  Where ([Userid] <> "" and [Userid] > "999" and [Userid] <> "Not assigned")


----------



## bluesky63 (Jul 3, 2015)

Hi,

Any solution for the error
OLE DB or ODBC error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.. The current operation was cancelled because another operation in the transaction failed.



SELECT * FROM `\\team.workplace.com@SSL\DavWWWRoot\subentity\ShSvc\Documents\Estate Management\Database\Occupancy database\Database_CD_Occupancy.xlsx`.['For Outlook(C1)$'] Where ([Userid] <> "")

UNION ALL

SELECT * FROM `\\ team.workplace.com @SSL\DavWWWRoot\subentity\ShSvc\Documents\Estate Management\Database\Occupancy database\Database_CD_Occupancy.xlsx`.['For Outlook(RO)$'] Where ([Userid] <> "")

UNION ALL
SELECT * FROM `\\ team.workplace.com @SSL\DavWWWRoot\subentity\ShSvc\Documents\Estate Management\Database\Occupancy database\Database_AB_Occupancy.xlsm`.['For Outlook$'] Where ([Userid] <> "" and [Userid] > "999" and [Userid] <> "Not assigned")


----------



## bluesky63 (Jul 6, 2015)

mbizavin said:


> Hello bluesky63, thanks for getting ack to me. Basically I wanted to give an idea on how my link looks like. I have 3 different workbooks stored in a SharePoint folder, each with a worksheet called _data_. All these worksheets have a similar structure. I'd like to aggregate the data from these worksheets using PowerPivot.



Hi mbizavin

Please reference to my mock data here

basically,  for my simulation,  I created 1 connection DA (1 Table),  1 connection ST(two tables) in EXCEL,  run one connection in PowerPivot then apply the following SQL

SELECT *   FROM `\\team.eworkplace.dsta.gov.sg\others\TestTeam\Documents\SK\Source\Test_Merge\ST.xlsx`.['For Outlook(ST)$']


UNION ALL


SELECT *   FROM `\\team.eworkplace.dsta.gov.sg\others\TestTeam\Documents\SK\Source\Test_Merge\ST.xlsx`.['For Outlook(TT)$']


UNION ALL


SELECT *   FROM `\\team.eworkplace.dsta.gov.sg\others\TestTeam\Documents\SK\Source\Test_Merge\DA.xlsx`.['For Outlook(DA)$']


----------



## bluesky63 (Jul 6, 2015)

Hi All,

by the way, for the "OLE DB or ODBC error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.. The current operation was cancelled because another operation in the transaction failed.",  instead of troubleshoot,  I extract directly from range cells instead of Pivot tables,  and it works.

Thanks all !


----------



## mbizavin (Jul 14, 2015)

bluesky63 said:


> Hi All,
> 
> by the way, for the "OLE DB or ODBC error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.. The current operation was cancelled because another operation in the transaction failed.",  instead of troubleshoot,  I extract directly from range cells instead of Pivot tables,  and it works.
> 
> Thanks all !




Hello bluesky63,

First of all thanks a lot for leading me up to this point. As mentioned also by you, the "OLE DB or ODBC error" prevents saving. However you found a turnaround. Would you kindly give me an example of how you put the ranges in the scripts?

Cheers and highly appreciated!


----------



## bluesky63 (Jul 15, 2015)

Hi mbizavin,   you can retrieve the mockup excel,  the SQL script is in the PowerPivot tables.  What I meant range is extract from worksheet instead of "Pivot table" in the worksheet,  e.g.  ST.xlsx worksheet "For Outlook(ST)",  DA.xlsx workshet "For Outlook(DA).   

Retrieve and down load my mockup data  to test in your system

https://onedrive.live.com/?id=D249FD932335CE0E%212379&cid=D249FD932335CE0E&group=0


----------



## mbizavin (Jul 15, 2015)

bluesky63 said:


> Hi mbizavin,   you can retrieve the mockup excel,  the SQL script is in the PowerPivot tables.  What I meant range is extract from worksheet instead of "Pivot table" in the worksheet,  e.g.  ST.xlsx worksheet "For Outlook(ST)",  DA.xlsx workshet "For Outlook(DA).
> 
> Retrieve and down load my mockup data  to test in your system
> 
> https://onedrive.live.com/?id=D249FD932335CE0E%212379&cid=D249FD932335CE0E&group=0




Thanks for all the assistance bluesky63!

Also found another way that one could aggregate the data using a Microsoft Query: How to Use Microsoft Excel to Query a SharePoint List | NothingButSharePoint.com


----------



## natejlefebvre (Aug 2, 2013)

I am having trouble combining two different worksheets in one. See the below information:

_SELECT ['Chef Coats$'].*   FROM ['Chef Coats$']_
_UNION ALL_
_SELECT *_

_FROM 'C:\Users\nlefebvre\Documents\polos.xlsx'.[polos$]_

I keep getting the error message below, but I don't know what is wrong with the string.

"The SQL statement is not valid. There are no columns detected in the statement."


----------



## bluesky63 (Jul 15, 2015)

Hi mbizavin,

Thanks for the tips

I was trying to export one of my SharePoint List to excel iqy.  According to the steps, we need to save as xlsx.  Open up an excel workbook, follow steps till form3 after select the workbook that I had saved previously and return to Form2,  I can't see any table in the drop-down to select

Any idea I miss out any steps ?


----------

