openrowset-如何从带有空格的文件名中选择? [英] openrowset - How to select from a filename with white spaces?

问题描述

这正在工作:

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 [...]

预先感谢您的提示

推荐答案

来自有关OPENROWSET的文档，尤其是有关query(重点是我的)的文档:

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

查询"

是发送给提供者并由提供者执行的字符串常量. SQL Server的本地实例不处理此查询，但处理提供程序返回的查询结果，即传递查询. [...]

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. [...]

换句话说，不是由于SQL Server导致此传递查询无法正常工作.

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

以下两个示例在您的提供程序字符串中使用DefaultDir属性，并使您的语句起作用:

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]');

或

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"');

这篇关于openrowset-如何从带有空格的文件名中选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！