OLEDB Connection Specific Columns

Question

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

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

I am populating the Connection Properties screen..

Thanks in advance...

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

Answer 1

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]

or

select * from [SHEETNAME$A1:O10000]

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

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

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

Let me know if this has helped.

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