SQL Server Performance

Reading Excel sheet

Discussion in 'General Developer Questions' started by erajendar, Nov 10, 2003.

  1. erajendar New Member

    Dear ALL,

    Greetings!!!!!

    I've got one Excel file in C drive with name TEST(c: est.xls).
    This file has go two columns named no and name.
    Now, I wanted to access the data from this file through SQL query
    without using Linked Server.

    Any type of help will be appreciated.

    Rajendar

    ok
  2. satya Moderator

    None other than DTS you can access(import) the data to the SQL table.
    Make sure to copy the file to SQL server or to a location where SQL services have access.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. erajendar New Member

    there are sum built in function in sql server like open datasource and open rowset.
    But I'm unable to understand to pass the table to that function as file does not contain table names


    Rajendar

    ok
  4. satya Moderator

    As you said don't want to use linked server, defined to use DTS.<br />HEre is the example to query against Excel thru OLE DB provider for Jet:<br />SELECT * <br />FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',<br /> 'Data Source="c:Financeaccount.xls";User ID=Admin<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword=;Extended properties=Excel 5.0')...xactions<br /> <br />Take help from books online from the topic : OLE DB Provider for Jet.<br /><br />_________<br />Satya SKJ<br />Moderator<br />SQL-Server-Performance.Com<br />
  5. erajendar New Member

    Dear satya,
    thank u for ur support. I've done same thing like u mentioned in ur post by copying from BOL. But could not thru. U pl. created on excel file and test it.


    Rajendar

    ok
  6. satya Moderator

    What was the error?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. erajendar New Member

    <br />Dear satya.<br /><br />When I executed the following query <br /><br />SELECT * <br />FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',<br /> 'Data Source="c: est.xls";User ID=Admin<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword=;Extended properties=Excel 5.0')...xactions<br /><br />the error is <br /><br />OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'xactions'. The table either does not exist or the current user does not have permissions on that table.<br /><br />ok
  8. satya Moderator

    BOL specifies :
    The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked server name. Thus, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. When executing remote stored procedures, OPENDATASOURCE should refer to another SQL Server. OPENDATASOURCE does not accept variables for its arguments.

    Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources accessed infrequently. Define a linked server for any data sources accessed more than a few times. Neither OPENDATASOURCE, nor OPENROWSET provide all the functionality of linked server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided each time OPENDATASOURCE is called.

    So no alternative to use linked server and follow BOL for OLE DB Provider for Jet topic.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. erajendar New Member

    Dear Satya,

    Could u pl. send the script to create the linked server for excel sheet and retrieving the records from the excel sheet(c: est.xls).

    Rajendar

    ok
  10. satya Moderator

    Again BOL helps :
    To create a linked server against an Excel spreadsheet:

    The Microsoft OLE DB Provider for Jet 4.0 can be used to access Microsoft Excel spreadsheets.

    To create a linked server that accesses an Excel spreadsheet, use the format of this example.
    sp_addlinkedserver N'Excel', N'Jet 4.0',
    N'Microsoft.Jet.OLEDB.4.0',
    N'c:dataMySheet.xls', NULL, N'Excel 5.0'
    GO
    sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
    GO

    To access data from an Excel spreadsheet, associate a range of cells with a name. A named range can be accessed by using the name of the range as the table name. The following query can be used to access a named range called SalesData using the linked server set up in the previous example.
    SELECT *
    FROM EXCEL...SalesData
    GO

    When you insert a row into a named range of cells, the row will be added after the last row that is part of the named range of cells. Thus, if you want to insert row rA after the column heading, associate the column heading cells with a name and use that name as the table name. The range of cells will grow automatically as rows are inserted.



    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  11. erajendar New Member

    Dear Satya,
    I executed the statements.
    EXEC sp_addlinkedserver 'ExcelSource1',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'c: est.xls',
    NULL,
    'Excel 2000'
    GO
    EXEC sp_addlinkedsrvlogin 'ExcelSource1', 'false', 'sa', 'Admin', NULL

    The the following error is occuring:

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
    [OLE/DB provider returned message: Could not find installable ISAM.]


    ok
  12. satya Moderator

  13. FrankKalis Moderator

  14. satya Moderator

    Also check ..., 'sa', 'Admin', NULL the values supplied on the server are matched.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page