By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Export Excel to SQL 2005 table

P: 13
Scenario :
SQL table has 2 columns SQ1, SQ2
Excel sheet has three columns : EX1, EX2, EX3

SQ1 matches with EX2
SQ2 matches with EX3

I would like to insert the rows where EX1='X'. EX1 is the left most column in th Excel sheet.

Here's what I am trying. Could you suggest any changes/suggestions?

-- Link server logic
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'Config_spreadsheet_load')EXEC master.dbo.sp_dropserver @server=N'Config_spreadsheet_load', @droplogins='droplogins'
GO

--This is the link logic to connect the spreadsheet to the database
sys.sp_addlinkedserver 'Config_spreadsheet_load',
Excel',
'Microsoft.Jet.OLEDB.4.0',
In : @ExcelfileName,
'excel 8.0;
IMEX=1;
GO

-- Insert Plan Codes, for the column (PLC_NEW_COL) marked as X
insert into Plan_code (SQ1,
SQ2)
-- **** How can I have the logic for checking EX1='X'
where PLC_NEW_COL like X
-- Once the data has been created, clean-up
Apr 23 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
try OPENROWSET

-- CK
Apr 24 '08 #2

100+
P: 293
Hi,
In stead of having such a complex query to fetch the xcel..i will suggest you to import the excel sheets data into the Temporary Table using import export vizard in sql server 2005 n then write a executable query to have a maych with your existing data or also can insert it to the destination table if it does not exist.

Thanks.
Apr 25 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.