asked on

Import Data from Excel Sheets to SQL Server 2008 R2

Hi,

I have a situation where I have to import data from excel sheets from different files into SQL Server 2008 R2 tables. Can you please suggest different options to import data into SQL Server from excel files usiing TSQL or powershell or some other methodology.

Note: As per the requirement of the project I can not use DTS or SSIS packages or application logic using ADO.NET etc. to it.

SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ezkhan

ASKER

In current requirement I do not have the flexibility to save it as .csv and have to import it .xls or xlsx file. Any example for these formats?

Thanks.

knightEknight

Not from TSQL directly ... I usually use SSIS for that.

ezkhan

ASKER

As I said in note above SSIS and DTS are not an option in this case. Any other ideas??????????????

SOLUTION

Brent Challis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ezkhan

ASKER

I am using OPENROWSET to import data from excel sheet but getting the error given below;

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

Any comments or suggestions please?????

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial