Simple way to read Excel or Access data in SQL Server

-- list of available providers
exec master..xp_enum_oledb_providers

-- Three ways to read in Access data (file: c:\db1.mdb, tabel: TEST)

1a) Using openrowset with older MS Access version:
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\db1.mdb;Uid=;pwd=;','SELECT * FROM TEST')

1b) For 2010 or more recent (*.accdb)
SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\db1.mdb;Uid=;pwd=;','SELECT * FROM MAANDEN')

2) Using opendatasource:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\db1.mdb;')...[TEST]

3) Using a linked server called TEST for 2010 or more recent:

EXEC sp_addlinkedserver
@server= N'TEST',
@srvproduct = N'MSDASQL',
@provider= N'MSDASQL',
@provstr= N'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\db1.mdb;Uid=;pwd=;'

-- Read in Excel (file: c:\employee.xls, worksheet: TEST)
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\employee.xls;Uid=;pwd=;','SELECT * FROM [TEST$]')

-- Insert into text file
insert openrowset( 'Microsoft.ACE.OLEDB.12.0', 'Text;HDR=yes;Database=c:\tmp\', 'select * from test.csv') (testname, testaddress) select testname, testaddress from vw_test;

Note:

• MS Access needs to be installed on the same server where SQL Server is installed
• Make sure that the use of ad hoc names is allowed:
  exec sp_configure 'show advanced options', 1;
  RECONFIGURE;
  exec sp_configure 'Ad Hoc Distributed Queries', 1;
  RECONFIGURE;

Possible error messages and solutions:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error"

Give read/write access to "everyone" on the server to the following folder:

C:\Users\<SQL Server Service Account>\AppData\Local\Temp

Make sure this is set on the server where the code is running, i.e. on the client PC where the code is executed within Management Studio.