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