Import data from Excel 2007 (xlsx) to SQL Server 2008 64-bit
According to Microsoft, we may have these approaches to dump Excel data into SQL Server:
A. Use DTS or SSIS
B. Import vs. Append
SELECT...INTO...FROM syntax
INSERT INTO...SELECT...FROM syntax
Of course, we need to use to use the below method to actually let the excel file accessible by SQL server:
1. Use a Linked Server
e.g. SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
e.g. SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
'SELECT * FROM [Customers$]')
2. Use Distributed Queries
A: Use "64-Bit OLEDB Provider for ODBC (MSDASQL)"
Since SQL server is 64-bit, but the "Microsoft.Jet.OLEDB.4.0" driver is 32-bit. It is not usable. To use it, we need a native 64-bit dirver
- Go to Microsoft to download the "64-Bit OLEDB Provider for ODBC (MSDASQL)".
- Install the driver.
- You may run the query like this:
SELECT * FROM OPENROWSET('MSDASQL',
'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 12.0;DriverId=1046;DefaultDir=Y:\myfolder;DBQ=Y:\myfolder\myexcel.xlsx', 'SELECT * FROM [area_list$]') - For some SQL servers, you may receive the following error:
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 - The default security settings prevent direct access to the file system. In this case, we need to relax the setting:(Please run the below command at SQL one by one:)
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1reconfigure
You will receive the below message which indicates a successful change on the server option:
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.
Please re-run sp_configure to make sure the change is effective:
- TODO: The excel driver requires a installation of MS Excel.
B: Use Office 2010 64-bit + Office 2010 Database Engine
- Check if ODBC driver for Excel 2007 has been installed:
- Go to Control Panel - Administrative Tools - Data Sources (ODBC)
- Open "Drivers". Check if Excel 2007 driver has been installed.
Since the driver is 32 bit, if you are using a 64 bit system, you will need to open the 32-bit ODBC page using the below command:
c:\windows\sysWOW64\odbcad32.exe- If the driver is not found, you need to install ODBC driver for Excel 2007 file (ACE OLEDB Provider). Please download it from Microsoft (2007 Office System Driver: Data Connectivity Components):
ODBC for Office 2007 (32-bit engine only): http://www.microsoft.com/download/en/details.aspx?id=23734
ODBC for Office 2010 (both 32-bit and 64-bit engine): http://www.microsoft.com/download/en/details.aspx?id=13255
SP1 for Office 2010 Database Engine:
http://support.microsoft.com/kb/2460011
(You need to have ) - Then we may try to run the below command:
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=Y:\myfile.xlsx;
Extended Properties=Excel 12.0 xml;HDR=YES;')...[Sheet1$]
or
SELECT *
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Data Source=Y:\data\myExcel.xlsx;
Extended Properties=Excel 12.0 xml;HDR=YES','select * from [st_list$]') - For some SQL servers, you may receive the following error:
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 - The default security settings prevent direct access to the file system. In this case, we need to relax the setting:(Please run the below command at SQL one by one:)
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1reconfigure
You will receive the below message which indicates a successful change on the server option:
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.
Please re-run sp_configure to make sure the change is effective:
- In some SQL server, when retry with the original SQL statement, we will encounter the below error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
In this case, we might try to use the below command::
USE [master]
GO
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 - You should be able to access the Excel file.
Reference:
How to import data from Excel to SQL Server, http://support.microsoft.com/kb/321686
Connection strings for Excel 2007, http://www.connectionstrings.com/excel-2007
How to enable the use of 'Ad Hoc Distributed Queries' by using sp_configure http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied. http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb
64-Bit OLEDB Provider for ODBC (MSDASQL) http://www.microsoft.com/download/en/details.aspx?id=20065
Comments