Import Data from Excel Sheets to SQL Server 2008 R2

ezkhan
ezkhan used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can try saving the Excel sheets as tab-delimited text files and then running BULK INSERT on the files:



BULK INSERT [MyTable]  
FROM '\\server\C$\folder\MyFile.csv'
WITH (  MAXERRORS=1
             ,FIELDTERMINATOR='      '   -- note: this is a tab, not a space
             ,ROWTERMINATOR='\n'
             ,FIRSTROW=2
             ,TABLOCK
          )

Author

Commented:
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.
Not from TSQL directly ... I usually use SSIS for that.

Author

Commented:
As I said in note above SSIS and DTS are not an option in this case. Any other ideas??????????????
Commented:
You can use linked servers to access the spreadsheets easily if you you to do this frequently (and have access to create linked servers on your SQL Server) or you can use OPENDATASOURCE or OPENROWSET T-SQL statements such as:

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
                        
as discussed here: http://support.microsoft.com/kb/321686

Author

Commented:
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?????
Top Expert 2012
Commented:
Any comments or suggestions please?????
If your SQL Server is 64-bit you should know there is no 64-bit JET provider.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial