Hello Everyone,
How is the day going? Hope it's better then myne :)
I've recently started using MS SQL Server Express 2012 (migrated from MS Access) and bumped into huge problem:
When I was using access I used to import excel files by using saved imports (file name is always the same. When I get new source file - I override and run saved import) - pretty easy.
Now when using MS SQL Server Express 2012 I noticed that it's impossible to have such an easy import from xlsx files due to some incompatibilities... I spend several hours trying various solutions and tips from this forum and a lot of other forums with no success....
So - I am puttin my all hopes in your guys - this is my last resort....
Setup:
Windows 7 Enterprice 32bit
CPU i5, 8GB RAM
SQL Server Express 2012 32bit + AccessDatabaseEngine 32bit + Ad Hoc queries = enabled.
I have the following code for impot:
EXEC
Test.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
SELECT
*
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Users\mkrusa\Desktop\NonCat Ongoing.xlsx;',
'SELECT * FROM [data$]')
Errors I get:
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
Besides - when I run the import through menu and do everything manually - it works.... The reason for this is that I will have to import same file (updated daily) again and again and doing all manually would be back to medieval times
Huge THANK YOU to everyone!
SQL Server Express 2012 import from xlsx (stored procedure)
