UNION ALL sql statement

[natejlefebvre]

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]

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]

Hi mbizavin, I cannot access to the folder link.

 

[mbizavin]

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]

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]

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]

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]

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]

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]

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]

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