December 3, 2009

SQL SERVER: How to Read Excel file by TSQL

Many times developers asked that, they want to import data from Excel file.

We can do this by many ways with SQL SERVER.

1. We can use SSIS package
2. Import/Export Wizard
3. T-SQL

Today, I am going to explain, How to import data from Excel file by TSQL.

To import Excel file by TSQL, we need to do following:

1. Put Excel file on server, means we need to put files on server, if we are accessing it from local.

2. Write following TSQL, to read data from excel file

SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLYoga.xls', [SQL$])

ExcelFile

NOTE: Here, Excel file is on "C:\" named "SQLYoga.xls", and I am reading sheet "SQL" from this excel file

If you want to insert excel data into table, 

INSERT INTO [Info]
SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\SQLYoga.xls', [SQL$])

That's it.

Posted by
Labels: , , , ,

12 comments:

  1. AnonymousDecember 24, 2009 at 1:09 PM

    while running query i gave error
    Msg 15281, Level 16, State 1, Line 1
    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

    ReplyDelete
  2. Tejas ShahDecember 24, 2009 at 3:54 PM

    Hi,

    It means you need to allow this from server.

    You need to enable "Ad Hoc Distributed Queries" configuration to run this query.

    You can enable this deature by:

    sp_config 'Ad Hoc Distributed Queries',1
    reconfigure

    Thanks,

    Tejas

    ReplyDelete
    Replies
    1. AnonymousAugust 2, 2012 at 9:17 PM

      Hi,
      I want to insert sql server 2000 data into table (dbf),

      insert into openrowset ( 'Microsoft.Jet.OLEDB.4.0',
      'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV;',
      'Select * from [item.DBF]')
      select * from itemfact

      and while running query i gave error

      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: No se pudo encontrar el archivo ISAM instalable.]

      I'm IvanFroilan from Argentine,thanks
      (http://social.msdn.microsoft.com/Forums/es-ES/sqlserveres/thread/4dffffb3-d3cb-4c30-9bd2-31b07b592246)

      greetings

      Delete
    2. Tejas ShahAugust 3, 2012 at 11:32 AM

      Hi,

      Can you please check it out following:

      1. Make a query with OPENROWSET and check it out that, you can read information from the table (DBF)"
      2. If you can, then you should able to INSERT too
      3. If you cannot, can you please check it out JET OLE DB driver is installed or not?

      Thanks,
      Tejas
      SQLYoga.com

      Delete
  3. SteveFebruary 18, 2012 at 2:18 AM

    Can you only read a file that is stored on the c: drive of the local SQL Seerver? Is it possible to access an excel file that is located elsewhere on a network? How?

    ReplyDelete
  4. Tejas ShahFebruary 18, 2012 at 11:35 AM

    Hi Steve,

    We can read file from any location where SQL SERVER instance is running. e.g. If system has D drive then it can read like D:\SQLYoga.xls.

    About to read from Network location, SQL can read file from network location to. For that, please make sure that SQL SERVER account has an access to that network location. If SQL server has an access to that location, we can write it like this:

    SELECT Name, Email, Phone
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=\\Server2\SharedFolder\SQLYoga.xls', [SQL$])

    Thanks,
    Tejas
    SQLYoga.com

    ReplyDelete
  5. AnuranJune 6, 2012 at 5:20 PM

    Hi Tegas,

    Can we use this OPENROWSET in 64 bit sql server.

    Thanks & Regards,
    Anuran

    ReplyDelete
  6. Tejas ShahAugust 3, 2012 at 11:28 AM

    Hi Dutta,

    Yes we can use OPENROWSET with 64 BIT SQL SERVER. Make sure to have Office is of 64 bit too.

    Thanks,
    Tejas
    SQLYoga.com

    ReplyDelete
  7. AnonymousOctober 15, 2012 at 5:56 PM

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
    Msg 7303, Level 16, State 1, Line 3
    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

    ReplyDelete
  8. AnonymousOctober 15, 2012 at 5:56 PM

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
    Msg 7303, Level 16, State 1, Line 3
    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

    ReplyDelete
  9. Tejas ShahOctober 15, 2012 at 6:00 PM

    Hi Sharma,

    It looks like, you are using 64-bit machine. You need to UNINSTALL all 32-bit Microsoft Office applications and instances (Access 2007 install, Office 10 32-bit, etc.). If you dont, you cannot install the new 64-bit Microsoft Access Database Engine 2010 Redistributable components.

    Then download and install:
    http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en

    Thanks,
    Tejas
    SQL Yoga

    ReplyDelete
    Replies
    1. AnonymousDecember 1, 2012 at 6:30 PM

      Hi All,

      My SQL server returns similar error as Sharma's when using OPENROWSET with "Microsoft.ACE.OLEDB.12.0".

      //quote
      OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
      Msg 7303, Level 16, State 1, Line 2
      Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
      //quote

      However, it works fine with "Microsoft.Jet.OLEDB.4.0" and both providers are registered under [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers] with below config:
      "AllowInProcess"=dword:00000001
      "DynamicParameters"=dword:00000001


      Could anyone help resolving?

      Platform details:
      - Windows Server 2008 Enterprise SP2 (32-bit)
      - SQL Server 2008 Standard (32-bit)
      - MS Office 2010 (32-bit)

      Thanks & Regards,
      Carol

      Delete

Subscribe to: Post Comments (Atom)