Importing Excel into SQL

Question

Importing Excel into SQL

Benjamin Kovac

Right there with Babe

Points: 718
More actions
June 13, 2007 at 7:55 am

#174678

Hi,
I am new so any help will be appreciated.
just would like to know what possibilities are in importing data from Excel into SQL 2005. The main task is to select ONLY part of the data from Excel sheet into a SQL table.I know and tried the linked server solution for this problem, however as I said I need to import only a selected part of the Excel data into SQL.Let's say there are 3 columns Firstnam,Lastname,Address in the excel sheet with many records and I need only a particular ones with Lastname = 'Johnson'.
tried to setup a linked server to the .xls file and using query
select * into Names from LS1...[Sheet1$]
here are: Names-table name,LS1-linked server name,Sheet1-excel sheet name.
So can I use something similar to :
select * into Names from LS1...[Sheet1$] where Lastname = 'Johnson'
or more complicated, like joins?
Is actually linked server something part of SSIS, or is this another functionality under SQL2005?
Btw:tried the import data from the Tasks menu option, but got an error after providing a script querying the source (excel) file for the above mentioned selection.
cheers in advance
Ben

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Recommend you write a macro to export the section of the spreadsheet as a tab or comma delimited text file and import the text file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Benjamin Kovac Right there with Babe Points: 718 More actions · Answer 2

Hi Jeff,

thanks for your comment,even if my excel file is only 1 table/sheet, your suggestion is interesting...

So what you are saying is, if I have more than one excel table/sheet I can use macro to separate them-am I right?Any sites info where I can find more details on use of mentioned Macro?

Ben