2

I'm trying to see if it's possible to create a PolyBase connection to a .CSV file. I'm using the Microsoft Access Text Driver and have set up a system DSN to the folder where the csv file is located. I'm able to query the file using OPENROWSET and the DSN, but when I try creating the external table, I'm getting the following generic error. 

Msg 105082, Level 16, State 1, Line 108
105082;Generic ODBC error: [Microsoft][ODBC Text Driver]Optional feature not implemented

The file is just a simple one I created with three columns and two rows. Would it be possible to create a PolyBase connection to a .csv file?

CREATE DATABASE SCOPED CREDENTIAL CSVFile
WITH IDENTITY ='ident',SECRET='pass'

CREATE EXTERNAL DATA SOURCE CSV
WITH ( LOCATION = 'odbc://localhost'
       ,CONNECTION_OPTIONS = 'Driver={Microsoft Access Text Driver (*.txt; *.csv)};DSN=CSVs'
        ,CREDENTIAL = CSVFile
        );

CREATE EXTERNAL TABLE TestCSV
(
    Col1 VARCHAR(255) NULL
    ,Col2 VARCHAR(255) NULL
    ,Col3 VARCHAR(255) NULL
)
WITH
(
    LOCATION = N'Test.csv',
    DATA_SOURCE = CSV
)
GO
MrTCS
  • 689
  • 4
  • 12
  • I'm not super experienced with the Polybase stuff, but I'd start by trying the latest ODBC driver release. I'm *guessing* the Access one you're using is a bit old in the teeth. – LowlyDBA - John M Nov 14 '19 at 17:41
  • From what I've seen that driver is the latest and greatest 64 bit text file driver from Microsoft, unless I purchase one from a third party. – MrTCS Nov 14 '19 at 17:58
  • What version is it? – LowlyDBA - John M Nov 14 '19 at 18:07
  • Microsoft Access Text Driver 14.00.7010.1000 – MrTCS Nov 14 '19 at 18:14
  • The latest [ODBC version is 17](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15) I'd suggest trying that one. – LowlyDBA - John M Nov 14 '19 at 18:50
  • @MrTCS did you find a fix? I have a similar issue. – RaviLobo Apr 07 '20 at 16:57
  • @RaviLobo I did not but I found some 3rd party connectors from CDATA we are going to try. This wasn’t urgent so we have not tried them yet though. – MrTCS Apr 07 '20 at 17:04
  • @MrTCS Thank you. I too found the CDATA driver [link](https://www.cdata.com/kb/tech/csv-odbc-polybase.rst). However, it is not free. – RaviLobo Apr 08 '20 at 12:18
  • @MrTCS check this link [link] (https://stackoverflow.com/questions/61100707/polybase-to-connect-local-csv-file). This might help. – RaviLobo Apr 11 '20 at 16:22

0 Answers0