SQL Query (Powerpivot) – for Excel Headers

  • 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

  • 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