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.
| 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: Sheet1Destination sql database name is: NM, table name is: Sht1I use SQL Server Authentication to access the database.User name: ABC and password: DEFDatabase name is: DBI 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 by1. Import Export Wizard2. Using A DTS package |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-20 : 03:58:52
|
| Odd - I've just done this. - pass in @filenamedeclare @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 #ago==========================================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. |
|
|
|
|
|
|
|
|
Import Excel data sheet to SQL Table - How to ??