Need to import data into Sql Server Table from an excel File (using SQL commands)

Question

Need to import data into Sql Server Table from an excel File (using SQL commands)

sandeep1188

Old Hand

Points: 389
More actions
May 3, 2011 at 9:22 am

#249702

I need to import data into Sql Server Table from an excel File without using SSIS OR Import and export wizard.Please suugest if there is a way to do this with a SQL Script.
Thanks,
Sandeep

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

yes you can do it, but there are a few rules: the sheet has to have column names in the first row, i think.

if your SQL2008 instance is 64 bit, you have to use ACE drivers that are 64 bit; the code below won't work, as the JET driver is 32 bit.

try doing it with a linked server, like this:

--#################################################################################################

--Linked server Syntax for Excel

--#################################################################################################

DECLARE @server sysname,

@srvproduct nvarchar(256),

@provider nvarchar(256),

@datasrc nvarchar(100),

@location nvarchar(100),

@provstr nvarchar(100),

@catalog sysname,

@sql varchar(1000)

--add an excel spreadsheet as a linked server.

SET @server = 'MyExcel'

SET @srvproduct = 'Jet 4.0'

SET @provider = 'Microsoft.Jet.OLEDB.4.0'

set @provstr = 'Excel 5.0;'

SET @datasrc ='C:\Clients\MyExcelDocument_APPROVAL.xls'

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL

--you MUST know the name of the spreadsheet;

--spreadsheet name has a dollar sign at the end of it!

--I've personally never gor a spreadsheet that has a space in it

--for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work

--to work, so I end up editing the spreadsheet to remove spaces if that happens.

--list all the spreadsheets and their names

EXEC sp_tables_ex 'MyExcel'

select * from MyExcel...Sheet1$;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ninja's_RGR'us

SSC Guru

Points: 294069

May 3, 2011 at 9:30 am

#1319170

openrowset

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Using OPENROWSET

The example below has this capablity

it reads data from Sheet1 starting at row 4 and only imports data form colums a thru c

If the Excel sheet does not have a header row then HDR should be set as HDR=No

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Temp\OpenRowSetTest2.xls;HDR=yes',

'SELECT * FROM [Sheet1$a4:c]')

If you are using EXCEL 2007 then

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=d:\dasdgigs.xls', 'SELECT * FROM [dasdgigs$]')

The above code can be modified as the first bit of code to read an Excel sheet with or without headers, and starting at a row other than 1 and reading from different columns.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

sandeep1188 Old Hand Points: 389 More actions · Answer 4

Thanks Everyone for the quick Response , Is there a way to import data into Sql Server Table from an excel File use BCP Utility, Bulk Copy or Bulk Insert. Please let me know on this.

Thanks,

Sandeep

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

sandeep1188 (5/3/2011)
Thanks Everyone for the quick Response , Is there a way to import data into Sql Server Table from an excel File use BCP Utility, Bulk Copy or Bulk Insert. Please let me know on this.
Thanks,
Sandeep

No...not if it's truly an Excel file. The utilities you mentioned are for importing text files (or SQL Serevr native files if you're nasty).

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato