| 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, PhoneFROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\SQLYoga.xls', [SQL$])
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, PhoneFROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\SQLYoga.xls', [SQL$]) That's it. |
December 3, 2009
SQL SERVER: How to Read Excel file by TSQL
12 comments:
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.
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
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?
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
Hi Tegas,
Can we use this OPENROWSET in 64 bit sql server.
Thanks & Regards,
Anuran
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
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
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
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)".
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)".
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
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
Post a Comment