Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 3rd, 2006, 06:23 AM
Authorized User
 
Join Date: Nov 2005
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jvraman
Default Importing data from excel sheet to databaase

How to import data from an excel sheet to database. I do have the code for exporting to excel sheet.

But my requirement is importing from excel sheet to database. Some one help me out in this regards


Regards
Venkat
__________________
Regards
Venkat
 
Old May 3rd, 2006, 12:41 PM
Authorized User
 
Join Date: Mar 2006
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi venkat!

You can use DTS to perform this. You can take import and export from a Excel to SQL Server. For your help, I am telling you steps to do this.
(Assuming that you have little knowledge of DTS, But, if you know all these, this may be useful for any novice member of this forum.)
-------------------------------------------------------
[1] Open EM, open Data Transformation Services.
[2] In Local Packages client area, Right Click .
[3] Choose new package. A window with Toolbox in left and client area of design appears.

[4] Click and create a Excel icon from the connection section. A messagebox appears, specify Excel filename.

[5] Click SQL Ole DB from the connection section and select the database where you want to create this.

[6] Click Transform data task from the Task section.

[7] It chooses the source, click Excel and For destination, choose SQL Server icon which you specified before in step [4] and [5].

Double click the arrow which is drawn between Excel and SQL Server. A window appears. In Source Tab, you can select the sheet you want to copy. Click Destination menu. Here, you can choose a previosly created table or create a new one also. Move the next tab and select the appropriate columns. After Transformation Tab, you can also click OK.

[9] Click Package > Execute from the menu of the DTS Designer.

-------------------------------------------------------

I have written the above details with my memory. There may be some changes in steps. But, what I remember, I have told you in this message. DTS are really suitable to copy from one database system to another, even if SQL Server is not involved. You can even schedule your DTS Package.

DTS Designer is really easy to work, I hope any one could perform easily.

- Som Dutt


 
Old May 9th, 2006, 01:36 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Overkill...

There is a simpler method.
--------------------------
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Excel 5.0;HDR=No;IMEX=1;Database=\\sourceservername\c$\d ocuments and settings\username\desktop\test.xls', 'select * from [Sheet1$a1:q50]')
where f1 <> 'a'
--------------------------

With this code you import region A1:Q50 from Sheet1 from Excel file Test.xls on SourceServerName in directory "c$\documents and settings\username\desktop".
Also you filter out all rows containing "a" in first column of region.

 
Old May 12th, 2006, 12:00 AM
Authorized User
 
Join Date: Oct 2005
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Similar to what Peso answered, here's a few links that discusses how to import an Excel file to a table in SQL Server without using DTS:

http://www.sql-server-helper.com/tip...-file-p01.aspx
http://www.sql-server-helper.com/tip...-file-p02.aspx
http://www.sql-server-helper.com/tip...-file-p03.aspx

SQL Server Helper
http://www.sql-server-helper.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing Excel Data into a new Dataset Juan Patrick Visual Basic 2005 Basics 2 July 4th, 2007 09:03 PM
Importing Excel sheet into Access using ASP prasanta2expert ASP.NET 1.0 and 1.1 Basics 1 October 5th, 2006 02:36 PM
Exporting data to excel sheet x_ray VB.NET 2002/2003 Basics 0 January 6th, 2006 03:14 PM
reading data from excel sheet rajiv_software Classic ASP Basics 2 April 30th, 2005 01:03 AM
Importing from excel with missing data Lizu SQL Server DTS 4 September 21st, 2004 07:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.