Post reply

Reading an Excel File from SQL Server

  • September 15, 2005 at 2:51 am

    #88987

    Hey guys!

    Please help me on this ! I have an Execel file on my local machine... I have to write code to read that Excel file and create a new table in SQL Server and then insert the rows in the XLS to the SQL Server table..

    I just tried to read from an Excel file... someone suggested using OPENROWSET ,  and using the linked server concept...

    Nothing seems to work.. Any ideas ????:

  • September 15, 2005 at 3:23 am

    #590046

    Why do not try to use DTS?

  • September 15, 2005 at 3:43 am

    #590051

    Well, I want to write it in T-SQL.. If it were DTS, that would have been very easy, but I want to write a procedure to go about it!!!!!!!!

  • September 15, 2005 at 4:09 am

    #590055

    I agree - DTS is the way to go.  Why make things harder for yourself??

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • September 15, 2005 at 5:17 am

    #590070

    Hmmm.. I know DTS is the easiest way.. but please the requirement for me is such that I have to write the code... There is a webpage which has a excel sheet and I have to download that sheet and without any manual interuptions, use code in an ASP page and move that sheet as a table...

     

    Help Someone 

  • September 15, 2005 at 9:36 am

    #590195

    Are you using ASP.NET, or "old" ASP ??     In ASP.NET, you can call the DTS, so that it's all part of the application. Write the DTS, then save it as a Structured Storage File (SSF). Then that SSF can be invoked from ASP.NET.

  • September 15, 2005 at 6:27 pm

    #590406

    Try this from BOL

    SELECT *

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

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

    Also note that everything inside the parenthesis can not be set to a variable so if it needs to change you will need to do dynamic sql. You may also have problems with the file location. If you can't see the file by using xp_cmdshell 'dir <filename>' then you won't be able to see it with OpenDataSource either.

    Gary Johnson
    Sr Database Engineer

  • September 15, 2005 at 9:46 pm

    #590422

    Well.. I have to use ASP and ASP.net and Gary, correct me if  i am wrong... the xl file need to be in the server for the above query to work right...!

    What if the file is in our local machine and also it is Excel 2000.

     

    Thanks Guys for all ur responses

     

  • September 15, 2005 at 10:23 pm

    #590425

    Yes, the xls file must in the server, if the xls location in the local machine, of course, you must create script to uploaded xls file to the web server.

Viewing 9 posts - 1 through 8 (of 8 total)

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