locked
Import Data from Excel to SQL Server RRS feed

Answers

All replies

  • Will You please try this

    http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
    Happy Programming!

    • Marked as answer by ryguy72 Tuesday, July 3, 2012 9:02 AM
    Monday, July 2, 2012 4:25 AM
  • Ryan

    Is that possible to use SSIS to import the data from EXCEL?


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by ryguy72 Tuesday, July 3, 2012 9:02 AM
    Monday, July 2, 2012 4:55 AM
    Answerer
  •  

    @Sambath-

    That link doesn't work.

     

    @Uri-

    Great suggestion.  Well, for one thing, I wanted to try to do it with pure SQL.  Also, I've had a heck of a time getting SSIS to run on my SQL Server Enterprise Evaluation Edition.  I used to have BIDS for SQL Server 2008 Evaluation.  I just recently upgraded to SQL Server 2012, and I was informed that SSDT replaced BBIDS.  I thought, ok, great, I'll get SSDT, and start learning that (I used SSIS quite a bit in the past).  Now, it seems like SSDT is deeply integrated into Visual Studio.  I can't seem to get SSDT to do anything useful/practical.  I finally got it installed (and that took some effort).  I still can't get it to run; I can't do anything with it at all.  I spent some time yesterday afternoon trying to learn the interface, and figure out how to interact with this tool.  I haven't made any progress towards that goal.

    Here is one of my threads from last week:

    http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/267bd310-d7f7-4876-b464-3f4eb6116774

     

    So, in short, I'd like to get the SQL working, per my original post.  Also, I'd like to get SSIS to do the same thing. 

    I'd really appreciate any help with either thing.


    Ryan Shuell

    Monday, July 2, 2012 2:43 PM
  • Hey Ryan,

           Sambath's link worked for me, not sure if it is a firewall issue for you or not.  But the first suggestion from the link provided was to check and make sure you have the Office 2007 System Drivers: Data Connectivity Components installed on your server.   

    http://www.microsoft.com/en-us/download/details.aspx?id=23734

    It seemed like a very good post that had 3 different trouble shooting options for the error you received, I'd try it on your phone or on your home PC to see if you can get it to load.



    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    • Marked as answer by ryguy72 Tuesday, July 3, 2012 9:02 AM
    Monday, July 2, 2012 8:32 PM
  • I downloaded and installed the AccessDatabaseEngine.exe from here:

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    Now that I think about this, I don't believe it will work.  I have a 64-bit version of SQL Server and 32-bit MS Office.

    Select @@ Version:

    Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
        Feb 10 2012 19:39:15
        Copyright (c) Microsoft Corporation
        Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    I changed the 'AdHocRemoteQueriesEnabled' setting in my SQL Server 2012, like recommended in this link:

    http://colinmackay.co.uk/2011/08/12/running-queries-on-excel-spreadsheets-using-sql-server-2008/

    I followed a few more samples here:

    http://go4answers.webhost4life.com/Example/import-excel-file-sql-server-64-bit-157244.aspx

    With this T-SQL, I still get errors:

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    GO
    --TSQL
    SELECT *  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
        'Excel 12.0;HDR=YES;IMEX=1;Database=C:\Users\Excel\Desktop\OptionTradingWorkbook.xls;HDR=YES',
        'SELECT * FROM [OptionPage$]');

    Msg 7438, Level 16, State 1, Line 2
    The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

    Does anyone have any ideas on this, or should I just give up on the whole undertaking???


    Ryan Shuell

    Monday, July 2, 2012 10:57 PM
  • You need to download 64 bit ACE provider (AccessDatabaseEngine_X64.exe) Microsoft Access Database Engine 2010 Redistributable from URL below

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    Here is the blog post that helped me when I had similar issue

    http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx

    • Marked as answer by ryguy72 Tuesday, July 3, 2012 9:02 AM
    Tuesday, July 3, 2012 1:31 AM
  • Thanks, but I think the problem is that I have MS Office Pro 2010, which is 32-bit, not 64-bit. 

    At the same time, my SQL Server Enterprise Evaluation Edition, is 64-bit.  I think that's the whole problem.  Please correct me if I am wrong.  I downloaded both files from the first link that you posted; only the first actually installed (because I have Office 2010 Pro, 32-bit).  I couldn't install the second file. 

    GREAT IMAGE!  AM I RIGHT!!???  LOL!!!!!  I can see this perfectly fine on my laptop; it looks like total s*** on the web.  Now a days, if I load images through Mozilla, I get a 'ghost' image, and if I load images through Explorer, I get a 95% black image (if your image is 95% black, there is no point in loading the image).

    I don't know how people do this anymore.  This used to be so easy.  Now, with 2012, it seems like nothing works anymore.  Maybe ETL is gone for good.  Not sure.  I really wish we could go back to the way things were in 2008.  The good old days!!  Things actually worked like they were supposed to!!

     


    Ryan Shuell


    • Edited by ryguy72 Tuesday, July 3, 2012 3:57 AM
    Tuesday, July 3, 2012 3:55 AM
  • When you say "cannot get working",what does it mean? Do you get errors?

    http://www.youtube.com/watch?v=79mi31caAag


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by ryguy72 Tuesday, July 3, 2012 9:02 AM
    Tuesday, July 3, 2012 5:53 AM
    Answerer
  • Oh.  Sorry for not being more clear.  When I said, I "cannot get working", I meant, I couldn't turn it on.  When I saw your video, it gave me some ideas on how this works, and I just figured it out!! 

    I think what happened is that it used to be BIDS, and that came with SQL Server (as I know).  But, I guess BIDS (back in 2008) was really an extension of Visual Studio.  Is that right?  Now, in 2012, BIDS is gone, and SSDT is tightly integrated with Visual Studio.  Is that right?

    I don't think that TSQL option will work for me.  Anyway, SSIS is far superior to that SQL-Excel-Import method.

    Thanks so much!!


    Ryan Shuell

    Tuesday, July 3, 2012 9:02 AM