September 16, 2013 at 3:30 pm
Hi,
Today I’m using a connection to an excel file using PowerPivot and I’m using a sql query to return just the number of rows with info of that excel file , it is working perfectly however I need to do the same for multiple files and opening a connection is not really straight forward because it will add an extra tab in the data model , so my only option is to use a sql query to join the results of all the files .
I’ve used union before, to consolidate excel files with the same number of columns and same headers so I know that what I’m trying to do is possible , here is a couple of things that I’ve tried .
With an open connection to the excel file , this query is working
SELECT 'AMID_MAPG' AS WORKBOOK
, COUNT(*) AS TOTAL_ROWS
FROM [AMIDMapping$]
I'm getting the error "The SQL statement is not valid. There are no columns detected in the statement." with the next query :
SELECT 'AMID_MAPG' AS WORKBOOK
, COUNT(*) AS TOTAL_ROWS
FROM [AMIDMapping$]
UNION ALL
SELECT 'CTO SKU Template' AS WORKBOOK
, COUNT(*) AS TOTAL_ROWS
FROM 'C:\Users\josh\Desktop\Projects\IT\CTO SKU Table.xlsx'.[CTO SKU Template$]
Any help would be much appreciated
September 17, 2013 at 8:37 am
Turns out that it was just a typo , here is the correct code
SELECT 'AMID_MAPG' AS WORKBOOK
, COUNT(*) AS TOTAL_ROWS
FROM [AMIDMapping$]
UNION ALL
SELECT 'BUS_AREA_RTS' AS WORKBOOK
, COUNT(*) AS TOTAL_ROWS
FROM `\\VPN ADDRESS\EG_AMS_CUBE_FS\DEV\ISS_Templates\Channel Conversion Rates Table.xlsx`.[PL Rates$]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy