Polybase unable to place a WHERE condition on XLSX destination

Question

The WHERE condition in my T-SQL query is not returning any rows, from SQL 2019 polybase to .XLSX file

Here's my code that created Polybase:

create master key encryption by password = 'Polybase2CSV';
    create external data source myODBCxlsx with
    (
        LOCATION = 'odbc://localhost',
        CONNECTION_OPTIONS = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=F:\PolybaseSourceData\CustomerData.xlsx'
    );


create external table CustomerData(
CUSTOMERID FLOAT(53),
CUSTOMERNAME Nvarchar(255),
DEPARTMENT Nvarchar(255)
) with (
LOCATION='[sheet1$]',
DATA_SOURCE=myODBCxlsx
);

This query works:

select * from customerData

However this doesn't:

select * from customerData where customername = 'Steve'

The query doesn't return any rows, although there's a customer by name Steve.

Answer 1

PUSHDOWN is automatically enabled by default if you don't specify a setting when creating an external data source. Unfortunately, that particular driver doesn't work with PUSHDOWN enabled, so you will get errors with simple queries. Turning off PUSHDOWN will resolve that.

The external data source definition should look like this:

create external data source myODBCxlsx with
(
    LOCATION = 'odbc://localhost',
    CONNECTION_OPTIONS = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=F:\Files\CustomerData.xlsx',
    PUSHDOWN=OFF
);