• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

Linked Server table from Excel

If I don't have a range in my Excel spreadsheet I can't select that table (range) once I'm linked to the sql.  How can I name the range during the process of linking to the file from sql utilizing sp_addlinkedserver?
0
wordtool
Asked:
wordtool
  • 2
1 Solution
 
wordtoolAuthor Commented:
Please note that I am actually linking to a .csv file.
0
 
wordtoolAuthor Commented:
I'm using the following:

EXEC master..sp_addlinkedserver 'Excel',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
      '\\myserver\shared\Data_In\1371.xls',
   NULL,
   'Excel 5.0'
GO

Is there a different syntax I should be using for .csv files?
0
 
Brendt HessSenior DBACommented:
The last parameter (for a CSV file) is not Excel 5.0.  It should be 'Text', from what I know of it.
0
 
BillAn1Commented:
If you've saved the excel as a csv file, then there is no such thing as named ranges etc. a csv is simply a text file.

If you have saved it as an xls file, as you indicate in your linked server connection details, then, if you want to use named ranges, you need to create these within the Excel spreadsheet. This is not something that can be done easily from within SQLServer (I say easily because, ultimatley anything can be done, with extended stored procs, and command shell, you can virtually anything)

However, you don't necessarily need to use named ranges - you can use sheet references, as in

SELECT * FROM EXCEL...SHEET1$
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now