Post reply

Stored procedure to loadExcel file into SQLServer table

  • November 19, 2006 at 11:56 pm

    #114747

    Hai to all,

      I need to import an Excel file in to  SQLServer database table using Stored procedure.

    I didn't get any suitable help from net.

    If anyone had worked on this, pls help me .

    Thanks

    Nirmala

     

     

     

  • November 20, 2006 at 1:15 am

    #672864

    Is OPENROWSET what are you looking for?

    Or you are asking about something more specific?

    _____________
    Code for TallyGenerator

  • November 20, 2006 at 2:56 am

    #672882

    Hai

               Thank you very much for your reply.

    I mean the OPENROWSET method to import and i have used the query,

    Insert

    into [TESTDB].[dbo].[GetExcelData] Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel8.0;Database =C:\testexcel.xls;HDR=YES','Select * from [Sheet1$]')

    But it is giving the error as,

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

    Then i replaced OPENROWSET with OPENDATASOURCE.Now the error is,

    Incorrect syntax near ','.

    i.e near .....HDR=YES','Select ........

    Pls reply me with correct qry .

    Thanks

    Nirmala

  • November 20, 2006 at 3:06 am

    #672887

    Example from BOL:

    SELECT *

    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

    'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

    "xactions" here is the sheet name, [Sheet1$] in your case.

    _____________
    Code for TallyGenerator

  • November 20, 2006 at 4:08 am

    #672892

    Thanks for your early reply.I tried on that, and it is working correctly.

    Meanwhile i also tried with OpenRowSet and iam able to run the query correctly.

    Once again thank you

    Nirmala

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply