Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Import Excel data sheet to SQL Table - How to ??

Author  Topic 

PS
Starting Member

9 Posts

Posted - 2006-03-18 : 13:34:08
I need to make a script in SQL 2005 to import data from an Excel sheet into a SQL table.
I am using the wizard to import now.
Import from Excel 2000. First row of the excel sheet has column names.
Excel file name is: EXL.xls, sheet name is: Sheet1
Destination sql database name is: NM, table name is: Sht1
I use SQL Server Authentication to access the database.
User name: ABC and password: DEF
Database name is: DB
I am using the following setting when importing now:
- Delete rows in destination table
- Enable identity insert

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-18 : 16:38:42
I'm using SQL 2000, but may be both are same.

U may easily do this by
1. Import Export Wizard
2. Using A DTS package
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-20 : 00:21:14
or
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-20 : 03:58:52
Odd - I've just done this. - pass in @filename
declare @sql nvarchar(1000)
select @sql = 'select * from openrowset(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;DATABASE=' + @FileName + ''',''select * from [Sheet1$]'')'
create table #a(s1 varchar(100), s2 varchar(100), s3 varchar(100), s4 varchar(100), s5 varchar(100), s6 varchar(100), s7 varchar(100), s8 varchar(100))
insert #a
exec sp_executesql @sql
select left(coalesce(s2, ''), 60), left(coalesce(s4, ''),11)+' ', left(coalesce(s6, ''), 20), left(coalesce(s7, ''), 20) from #a
go


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -