Search code examples
sqlsql-serveropenrowset

openrowset - How to select from a filename with white spaces?

This is working :

SELECT * 
FROM OPENROWSET('MSDASQL','Driver={Microsoft Access Text Driver (*.txt, *.csv)}; Extended Properties="text; HDR=YES; FMT=Delimited"','SELECT * FROM E:\folder\subfolder\myfile.txt')

This is not working because of the white spaces:

SELECT * 
FROM OPENROWSET('MSDASQL','Driver={Microsoft Access Text Driver (*.txt, *.csv)}; Extended Properties="text; HDR=YES; FMT=Delimited"','SELECT * FROM E:\folder\sub folder\my file.txt')

I tried with doubles quotes ("...") and with [...]

Thank you in advance for your tips

Solution

  • From the documentation on OPENROWSET specifically on the query (emphasis mine):

    'query'

    Is a string constant sent to and executed by the provider. The local instance of SQL Server does not process this query, but processes query results returned by the provider, a pass-through query. [...]

    In other words, it's not due to SQL Server that this pass-through query is not working.

    The following two examples use the DefaultDir property in your provider string and should get your statement to work:

    SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Access Text Driver (*.txt, *.csv)}; Extended Properties="text; HDR=YES; FMT=Delimited"; DefaultDir=E:\folder\sub folder;','SELECT * FROM [my file#txt]');

    Or

    SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Access Text Driver (*.txt, *.csv)}; Extended Properties="text; HDR=YES; FMT=Delimited"; DefaultDir=E:\folder\sub folder;','SELECT * FROM "my file.txt"');