How can I use Excel from SQL Server 2019 with PolyBase?

[善博 河端]

I want to know How can I use Excel from SQL Server 2019 with PolyBase?
which ODBC driver string do I need to use for connecting Excel file with External Data Source?

and anyone can do it?

Reference: Configure PolyBase to access external data in SQL Server

[#2]

Hey..

I want to know too..

I have  a T-SQL code..

/**************************/

USE [PolyBaseMSSQL_MySQL_Excel]
GO

CREATE DATABASE SCOPED CREDENTIAL [ExcelCredential]
WITH IDENTITY = 'MyExcel', Secret = ''
GO

select *
from sys.database_scoped_credentials

--Using [Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) Ver16.00 x64]
CREATE EXTERNAL DATA SOURCE [ExcelInstance]
WITH (
LOCATION = 'odbc://<hostname>',
CONNECTION_OPTIONS ='DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DSN=MyExcel',
CREDENTIAL = [ExcelCredential])
GO

select *
from sys.external_data_sources

--Run this then Error!!!!
CREATE EXTERNAL TABLE [extExcel_Person](
[RowID] [int] NOT NULL,
[PersonID] [varchar](10) NOT NULL,
[Name] [varchar](100) NOT NULL,
[CorpCode] [varchar](2) NOT NULL,
[PostCode] [varchar](6) NOT NULL)
WITH
(LOCATION = 'Sheet1',DATA_SOURCE = [ExcelInstance])
GO

/**************************/

I got this error.

メッセージ 105082、レベル 16、状態 1、行 170
105082; 汎用 ODBC エラー: [Microsoft][ODBC Excel Driver]Optional feature not implemented 。

So, I try change「CONNECTION_OPTIONS」value.

/**************************/

USE [PolyBaseMSSQL_MySQL_Excel]
GO

DROP EXTERNAL DATA SOURCE [ExcelInstance]
GO

CREATE EXTERNAL DATA SOURCE [ExcelInstance]
WITH (
LOCATION = 'odbc://<hostname>',
CONNECTION_OPTIONS ='DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Excel File=C:\work\Person.xlsx',
CREDENTIAL = [ExcelCredential])
GO

/**************************/

Then, retry create external table.

/**************************/

CREATE EXTERNAL TABLE [extExcel_Person](
[RowID] [int] NOT NULL,
[PersonID] [varchar](10) NOT NULL,
[Name] [varchar](100) NOT NULL,
[CorpCode] [varchar](2) NOT NULL,
[PostCode] [varchar](6) NOT NULL)
WITH
(LOCATION = 'Sheet1',DATA_SOURCE = [ExcelInstance])
GO

/**************************/

 I got a different error content.

メッセージ 105082、レベル 16、状態 1、行 171
105082; 汎用 ODBC エラー: [Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x814 Thread 0x3e14 DBC 0xda7b6ad8 Excel'. Additional error <2>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute SERVER, SqlState: 01S00, NativeError: 8 Additional error <3>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute excel file, SqlState: 01S00, NativeError: 8 Additional error <4>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute SERVER, SqlState: 01S00, NativeError: 8 Additional error <5>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute excel file, SqlState: 01S00, NativeError: 8 。

Well. I check the error log using T-SQL

/**************************/

SELECT *
FROM [DWDiagnostics].[dbo].[pdw_errors]
ORDER BY [DateTimePublished] DESC

/**************************/

I got this error.

EngineInstrumentation:EngineExecuteQueryErrorEvent

Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException: 105082; 汎用 ODBC エラー: [Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x814 Thread 0x3e14 DBC 0xda7b6ad8 Excel'. Additional error <2>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute SERVER, SqlState: 01S00, NativeError: 8 Additional error <3>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute excel file, SqlState: 01S00, NativeError: 8 Additional error <4>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute SERVER, SqlState: 01S00, NativeError: 8 Additional error <5>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute excel file, SqlState: 01S00, NativeError: 8 。
場所 Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.Execute(IQueryContext queryCtx, Boolean isSubBatch, Boolean isBatchInProgress)
場所 Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.OnExecuteRequest(IClientInterface client, String query, Boolean isInBatch, StatementExecutionContext executionContext, IDictionary`2 replacementInfos, String database, Statement pregeneratedStatement, SqlFrontEndRequest request)

The error message is [Invalid connection string attribute excel file]..

So, I re-change the [CONNECTION_OPTIONS] value.

CONNECTION_OPTIONS ='DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DSN=MyExcel',

After all it was impossible.

Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException: 105082; 汎用 ODBC エラー: [Microsoft][ODBC Excel Driver]Optional feature not implemented 。
場所 Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.ThrowMppSqlException(String message, NativeOdbcConnection connection)
場所 Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.GetTableType(String connectionString, Int32 connectionLoginTimeout, String catalogNameArgument, String schemaNameArgument, String tableNameArgument, List`1& fieldNameList, Int16[]& dataTypeArray, Int32[]& columnSizeArray, Int32[]& bufferLengthArray, Int16[]& decimalDigitsArray, Int16[]& nullableArray, Int32[]& ordinalPositionArray, List`1& typeNameList)
場所 Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcMetadataProcessor.GetTableType(String tableName)
場所 Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcMetadataProcessor.get_TableType()
場所 Microsoft.SqlServer.DataWarehouse.Sql.Statements.ExternalGenericTable.ValidateExternalSchema()
場所 Microsoft.SqlServer.DataWarehouse.Sql.Statements.ExternalGenericTableFactory.GetExternalGenericTable(String sourceLocation, Object[] constructorArgs)
場所 Microsoft.SqlServer.DataWarehouse.Sql.Statements.ExternalGenericTable.ObtainTableStatsForExternalGenericTable(ExternalSourceIdentification externalDataSourceInfo, ExternalFileFormatIdentification externalFileFormatInfo, Int32 rowSizeEstimate, String databaseName, String schemaName, String name, RejectOptions rejectOptions, String externalLocation, IList`1 columns, Boolean createIfNotFound)
場所 Microsoft.SqlServer.DataWarehouse.Sql.Statements.CreateExternalTableStatement.ValidateForSQLServer(ExternalSourceIdentification externalDataSourceInfo, ExternalFileFormatIdentification externalFileFormatInfo)
場所 Microsoft.SqlServer.DataWarehouse.Sql.Statements.SqlFrontEnd.ValidateExternalDdlStatement.ValidateForSQLServer()
場所 Microsoft.SqlServer.DataWarehouse.Engine.Processors.SqlFrontEndWorkProcessor.ValidateExternalDdlHandler(ISessionContext sessionContext, SqlFrontEndWorkStatement statement, ICancelableExecutionUnit cancelUnit)
場所 Microsoft.SqlServer.DataWarehouse.Engine.Processors.SqlFrontEndWorkProcessor.OnExecuteRequest()
場所 Microsoft.SqlServer.DataWarehouse.Engine.Utils.EventUtils.PublishApplicationEventAndExecute(ApplicationEventTrigger beginTrigger, ApplicationEventTrigger endTrigger, ApplicationEventTrigger errorTrigger, ApplicationEventTrigger cancelTrigger, PublishedEventPayloadDelegate payload, Action callback)
場所 Microsoft.SqlServer.DataWarehouse.Engine.Processors.AbstractProcessor.OnProcess()
場所 Microsoft.SqlServer.DataWarehouse.Engine.Processors.AbstractProcessor.OnExecute()

[#3]

Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.GetTableType()

ok. I look this  dll code.

public static void GetTableType(string connectionString, int connectionLoginTimeout, string catalogNameArgument, string schemaNameArgument, string tableNameArgument, out List<string> fieldNameList, out short[] dataTypeArray, out int[] columnSizeArray, out int[] bufferLengthArray, out short[] decimalDigitsArray, out short[] nullableArray, out int[] ordinalPositionArray, out List<string> typeNameList)
{
    //IL_0151: Expected O, but got Unknown
    if (connectionString == null)
    {
        throw new ArgumentNullException("connectionString");
    }
    NativeOdbcConnection nativeOdbcConnection = new NativeOdbcConnection(connectionString, CreateOptionFlags.ReadExternal, string.Empty);
    nativeOdbcConnection.ConnectionLoginTimeout = connectionLoginTimeout;
    nativeOdbcConnection.EnableConnectionPooling = true;
    using (NativeOdbcConnection nativeOdbcConnection2 = nativeOdbcConnection)
    {
        IntPtr fieldNames = IntPtr.Zero;
        IntPtr dataType = IntPtr.Zero;
        IntPtr columnSize = IntPtr.Zero;
        IntPtr bufferLength = IntPtr.Zero;
        IntPtr decimalDigits = IntPtr.Zero;
        IntPtr nullable = IntPtr.Zero;
        IntPtr ordinalPosition = IntPtr.Zero;
        IntPtr typeNames = IntPtr.Zero;
        int numFields = 0;
        try
        {
            nativeOdbcConnection2.Open();
            if (!DmsNative.OdbcGetTableType(nativeOdbcConnection2.OdbcConnHandle, catalogNameArgument, schemaNameArgument, tableNameArgument, out fieldNames, out dataType, out columnSize, out bufferLength, out decimalDigits, out nullable, out ordinalPosition, out typeNames, out numFields))
            {
                ThrowMppSqlException("OdbcUtil.GetTableType, error in OdbcGetTableType", nativeOdbcConnection2);
            }
            fieldNameList = MarshalIntPtrToStringList(fieldNames, numFields);
            dataTypeArray = new short[numFields];
            Marshal.Copy(dataType, dataTypeArray, 0, numFields);
            columnSizeArray = new int[numFields];
            Marshal.Copy(columnSize, columnSizeArray, 0, numFields);
            bufferLengthArray = new int[numFields];
            Marshal.Copy(bufferLength, bufferLengthArray, 0, numFields);
            decimalDigitsArray = new short[numFields];
            Marshal.Copy(decimalDigits, decimalDigitsArray, 0, numFields);
            nullableArray = new short[numFields];
            Marshal.Copy(nullable, nullableArray, 0, numFields);
            ordinalPositionArray = new int[numFields];
            Marshal.Copy(ordinalPosition, ordinalPositionArray, 0, numFields);
            typeNameList = MarshalIntPtrToStringList(typeNames, numFields);
            if (numFields == 0)
            {
                ErrorUtil.GenerateUserVisibleException(SR.TableDoesNotExist(tableNameArgument));
            }
        }
        catch (DmsSqlNativeException val)
        {
            DmsSqlNativeException ex = val;
            throw GenerateUserVisibleException(ex);
        }
        finally
        {
            FreeIntPtrArray(fieldNames, numFields);
            Marshal.FreeCoTaskMem(dataType);
            Marshal.FreeCoTaskMem(columnSize);
            Marshal.FreeCoTaskMem(bufferLength);
            Marshal.FreeCoTaskMem(decimalDigits);
            Marshal.FreeCoTaskMem(nullable);
            Marshal.FreeCoTaskMem(ordinalPosition);
            FreeIntPtrArray(typeNames, numFields);
            nativeOdbcConnection2.Close();
        }
    }
}

[#4]

if (!DmsNative.OdbcGetTableType(nativeOdbcConnection2.OdbcConnHandle, catalogNameArgument, schemaNameArgument, tableNameArgument, out fieldNames, out dataType, out columnSize, out bufferLength, out decimalDigits, out nullable, out ordinalPosition, out typeNames, out numFields))
            {
                ThrowMppSqlException("OdbcUtil.GetTableType, error in OdbcGetTableType", nativeOdbcConnection2);
            }

The place where the error occurred is here.↑

ThrowMppSqlException()

SQL Server 2019 CTP2.0 is not support Excel ODBC x64?

[#5 · Accepted solution]

Okey. 

I am use CDATA EXCEL ODBC Driver x64..Fixed it.

https://www.cdata.com/drivers/excel/download/odbc/

Uninstall Microsoft Access Database Engine 2016 Redistributable..

Install CDATA EXCEL ODBC Driver Version: 18.0.6817.

Thanks!

[#6]

Nice CDATA Excel ODBC driver for connect from SQL Server 2019 to Excel file as External table.
and I hope the Microsoft ODBC driver for it, for share with friends/customers/partners.
because SQL Server 2019 build by Microsoft, Excel build by Microsoft, External table to ODBC is new feature of SQL Server 2019.