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

 

[mikeTRON]

Did you create a data connection to Polos?

 

[mikeTRON]

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]

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

 

[mikeTRON]

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]

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]

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]

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

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]

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]

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.