Post reply

Importing Excel into SQL 2005

  • June 13, 2007 at 7:35 am

    #117126

    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

  • June 13, 2007 at 8:00 am

    #712622

    You can use opedatasource to read from your excel sheet and insert into your sql server.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • June 13, 2007 at 8:27 am

    #712630

    Thanks Sugesh,

    You are right the OPENDATASOURCE is another option to importing excel data into SQL, the use of them depends on how frequently you accessing the particular Excel data file.As I know linked server creates a persistent connection to the excel file, where OPENDATASOURCE defines a connection to that file,am I right?

    How is with opendatasource, can you use a SQL query on the excel file, meaning can I use any join or where clause or other scripts on it?

    Ben

  • June 13, 2007 at 10:38 am

    #712677

    The following sample script can be useful. Similar result is achievable, as when you using linked server, if you right click on the database and going to tasks/import data and following the wizard.Everything is clear and easy until the option that you can write your own query to actually selecting partial data from the excel file and saving that into the database rather than selecting the whole data from excel into SQL and then running query on it.

    select * from [Sheet1$] where Firstname='Bob'

    where Sheet1 is the excel sheet name followed by $ and surrounded by []

    and the where or other clause to filter the data you only need to populate in SQL.

    Did not try more complicated queries and don't know whether this is faster rather than to load the whole excel file into sql and then running a query.Probably depends on your need.

    hope this helps

    Ben

  • June 14, 2007 at 5:25 am

    #712876

    select * from opendatasource('Microsoft.Jet.OLEDB.4.0',DataSource="PATH OF EXCEL FILE";user id =Admin;password=;Extended properties='Excel 8.0')..[Sheet1$] where your conditio.

    Is this query enough for you.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • June 14, 2007 at 8:04 am

    #712942

    If there data is rather flat, using jet is an ideal way to go. if you have something a little more complex in Excel, have a look at Data Defractor (www.DataDefractor.com) for more info.

    It's a nice little Excel Source component for SSIS.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • June 14, 2007 at 3:33 pm

    #713064

    Thanks Sugesh, Crispin

    Sugesh - As I found out whatever method I use (creating linked server, using Tasks/Import Data or your code version) the solution is Miscrosoft's OLE DB provider and the syntax is very similar.

    Crispin - the data I need to transer into SQL is rather flat as there is just basic structure nothing complicated, but my problem was that I was asked to rather use a filter on data while transfering, than to load all the unnecessary data and then making selection.

    Thanks for your comments and help for my problem.This site is just great - because of great people!

    Cheers

    Ben

  • June 15, 2007 at 3:46 am

    #713184

    Not sure whether I have the worng end of the stick here , but would it not be easier to import the data using SSIS and the Excel Datasource rather than linked servers? You can modify the data through a series of steps and filter before it hits the tables you need to populate.

  • June 28, 2007 at 3:59 am

    #715789

    I'm sure it is. but Rookie please help. How do I use this SSIS? Where do i get it? I have SQL 2005 installed but I cant seem to see the functionalities of this SSIS..

  • June 30, 2007 at 12:36 pm

    #716378

    Tebogo,

    What SQL Server you have installed?I think under Express Edition there is no Import,Export service,so check your Edition of SQL.

    To get the SSIS services I guess you want to use the Export or Import options.

    Simply right click on the particular database, go to Tasks and choose Import Data or Export Data.

    The wizard will ask you for every details and you can setup these actions as a package(SSIS).I think you can setup this package to be executed a particular time of the day,etc.

  • July 2, 2007 at 3:25 am

    #716472

    From what I can remember of the top of my head, having the BIDS available is an option that you choose on the install. However, with the express edition, there SSIS is not an option as it is not included in the install package. 

    Anyways, if it is installed, you can get to the development studio by START->Programs-> Microsoft SQL Server 2005-> SQL Server Business Intelligence Development Studio. From there you need to create a new project, this being an Integration Services Project.

    From here :

    http://technet.microsoft.com/en-us/library/ms139836.aspx

    You can read up on the specifics to make your connection. Depending on your version of Excel (2007 requires a different connection string) it runs through the whole process of importing data quite nicely.

    Further transformations might be required depending on what you are going to do with your data, but most things are directly available in SSIS.

  • July 2, 2007 at 3:57 am

    #716475

    Oh yes I had the express edition installed. I uninstalled it and had the developer's edition.. It works like a charm. Thank you so much guys!!!

  • July 2, 2007 at 7:51 am

    #716541

    For importing Excel files, you might also have a look at

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=334859#bm334915

     

  • July 2, 2007 at 9:05 am

    #716561

    There are easier ways to importing Excel now with SSIS that having to use Excel Automation.

    simplest is to use the Excel connection component and pull the flat data through. if the format of the data is complex, i.e. tabular within the sheet, see Data Defractor for a far more flexible option.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Viewing 14 posts - 1 through 13 (of 13 total)

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