Solved

Linked Server table from Excel

Posted on 2004-10-22
553 Views
Last Modified: 2012-06-21
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
Question by:wordtool
    4 Comments
     

    Author Comment

    by:wordtool
    Please note that I am actually linking to a .csv file.
    0
     

    Author Comment

    by:wordtool
    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
     
    LVL 32

    Accepted Solution

    by:
    The last parameter (for a CSV file) is not Excel 5.0.  It should be 'Text', from what I know of it.
    0
     
    LVL 17

    Expert Comment

    by:BillAn1
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Course: MongoDB Object-Document Mapper for NodeJS

    NodeJS (JavaScript on the server) is awesome, but some developers get confused about NoSQL when it comes to working in Node with MongoDB (NoSQL database). Do you need a better explanation of how to use Node.js with MongoDB? The most popular choice is the Mongoose library.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now