Inserting data from excel sheet

Question

Post reply

Inserting data from excel sheet

gsrikar33

SSC Journeyman

Points: 97
More actions
December 9, 2014 at 5:58 am

#312966

Hi Everyone,
I am trying to insert data into a table from an excel sheet using bulk insert statement.
This excel sheet has number of tabs.
How can I mention a specific tab in bulk insert statement.
Thanks in advance.

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

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

pietlinden SSC Guru Points: 63436 More actions · Answer 1

--Excel 2007-2010

SELECT * --INTO #productlist

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

'Excel 12.0 Xml;HDR=YES;Database=C:\temp\Products.xlsx',

'SELECT * FROM [ProductList$]');

gsrikar33 SSC Journeyman Points: 97 More actions · Answer 2

pietlinden (12/9/2014)
--Excel 2007-2010
SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\temp\Products.xlsx',
'SELECT * FROM [ProductList$]');

I have tried to use the query you have suggested but, I am getting an error like "This does'nt work for distributed queries."

Bujar_DBA SSC Veteran Points: 253 More actions · Answer 3

Hi,

You can use import (right click on database name you chose import) follow the steps chose as source excel file for import, then you have to select destination sql server database then the table name and map the columns in your excel sheet.

arminder.lubana Newbie Points: 5 More actions · Answer 4

In same approach, if there are some empty rows(mean all columns has null or empty value ) , how i can ignore them to read.

Thanks in advance,

Arminder

lalithab Newbie Points: 3 More actions · Answer 5

To execute OPENROWSET commands successfully we need to enable Ad Hoc Distributed queries since by default it will be turned off

To configure this follow the following:

sp_configure ‘show advance option’, 1

GO

RECONFIGURE

Afer executing the above, now configure Ad Hoc Distributed queries

sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

RECONFIGURE