OLEDB连接特定列 [英] OLEDB Connection Specific Columns

问题描述

我有一个OLEDB数据连接，我只想将A：O列导入表中。

I have an OLEDB data connection and I am only wanting to import columns A:O into a table.

我可以成功导入所有数据，但不仅限于这些列（A：O）

I can import all the data...successfully, but not limit it to just those columns (A:O)

我正在填充连接属性屏幕。

I am populating the Connection Properties screen..

预先感谢...

Provider = Microsoft.ACE.OLEDB.12.0; User ID = Admin; Data
Source = FILEPATH; Mode = ReadWrite; Extended
Properties = IMEX = 1; HDR = NO;字符串sql = select * from
['SHEETNAME'$ A：$ O]; Jet OLEDB：系统数据库=; Jet OLEDB：注册表
Path =; Jet OLEDB ：Engine Type = 35; Jet OLEDB：Database Locking Mode = 0; Jet
OLEDB：Global Partial Bulk Ops = 2; Jet OLEDB：Global Bulk
Transactions = 1; Jet OLEDB：New Database Password = ; Jet OLEDB：Create
系统数据库= False; Jet OLEDB：Encrypt Database = False; Jet OLEDB：Do n't
在Compact上复制区域设置= False; Jet OLEDB：紧凑而不复制
修复= False; Jet OLEDB：SFP = False; Jet OLEDB：Support Complex
Data = False; Jet OLEDB：Bypass UserInfo Validation = False; Jet
OLEDB：Limit ed DB Caching = False; Jet OLEDB：Bypass ChoiceField
Validation = True

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=FILEPATH;Mode=ReadWrite;Extended Properties="IMEX=1;HDR=NO;string sql=""select * from ['SHEETNAME'$A:$O]""";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=True

推荐答案

在['SHEETNAME'$ A：$ O]中，您不需要SHEETNAME周围的单引号和第二个$。如下所示使用它：

in the ['SHEETNAME'$A:$O] you don't need the single quotes around SHEETNAME and the second $. Use it like below:

select * from [SHEETNAME$A:O]

或

select * from [SHEETNAME$A1:O10000]

我已成功使用带范围的OleDB select语句，但未在连接中指定字符串，而是在OleDB命令中指定，例如：

I've successfully used OleDB select statement with range but it was not specified in the connection string, instead it was specified in the OleDB command like:

cmd = new OleDbCommand("SELECT * FROM [Template$A1:Q10000]", conn);

或
在SQL Server OPENROWSET调用中：

or in SQL Server OPENROWSET call:

SELECT ' + @columnList + ' 
    FROM OPENROWSET(' +
    '''Microsoft.ACE.OLEDB.12.0'', ' +
    '''Excel 12.0;Database=' + @TemplateFilePath + ';HDR=YES;IMEX=1'', ' +
    '''SELECT * FROM [Template$' + @TemplateColumnsRangeStart + ':' + @TemplateColumnsRangeEnd + ']'')

其中，例如@ TemplateColumnsRangeStart ='A5'和或让TemplateColumnsRangeEnd ='Z'。

where for example, @TemplateColumnsRangeStart='A5' and or TemplateColumnsRangeEnd='Z'.

让我知道是否有帮助。

PS。欢迎使用StackOverflow，请检查游览，以开始对文章进行提问，回答，投票等。

PS. Welcome to StackOverflow, please check the Tour to get started with asking, answering, voting for posts etc.

这篇关于OLEDB连接特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！