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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-04-15 : 08:00:18
|
| jyotiranjan writes "Hi Sir,i want to know how to read data from Excel sheet to sql server through sql stored procedure by using DTS.please suggest.Regards,Jyoti Ranjan" |
|
|
vravo
Starting Member
13 Posts |
Posted - 2005-04-19 : 11:40:39
|
| The best way for you to do it is to use the wizard... click on the database or the DTS folder, then on the menu bar select action then under the sub menu of All Tasks select Import Data... then select your source as an Excel file the it will give you the option to select the specific file... Then follow the screens and select the Destination Database and it will create either a database table to import the data into based on the name of the Excel file name unless you select a table to import into.. the last screen will ask you if you what to run it, but before for you run it put a check in the Save package checkbox... then click finish... it will ask you to give the package a name... it will tell you if it ran successfully... then in the future you can call it from a stored procedure like this....exec master..XP_CMDSHELL 'dtsrun /S SQLServer1 /N DTS_Package_Name /E'ThanksVin |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-20 : 07:40:16
|
| Note that using DTS to import from Excel XLS files is very fragile. DTS will make assumptions based on the first few rows of the spreadhseet - e.g. what the maximum length of text columns is, and what datatype columns contain.if you have the option to use a different type of source file you might want to consider it.Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-04-20 : 07:43:09
|
| so is it safe to assume that a csv or txt source is better than xls?--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-20 : 08:08:50
|
| Yes, provided there are no embedded line breaks within a column (and you have a seperator that is unique in the data stream, or a properly formatted CSV [fields containing comma are surrounded by quotes, quotes within such a field are doubled-up])But that clearly isn't an ideal situation either!Increasingly I prefer XML for this job. It produces massively bloated transfer files, but the "delimited data" part works rather well!Edit: One possible caveat; importing an Excel file into SQL as a NEW table will retain datatype from the column definitions within Excel. This can be handy (rather than just getting a bunch of varchar(8000) columns!). Where I have to use XLS I put a dummy data row in the first [non heading] row of the XLS with "big data cells" where they might occur later in the datastream.Kristen |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-04-20 : 23:41:34
|
| There is a registry key you can change to fix this problem in Excel.http://support.microsoft.com/kb/281517/EN-US/If I'm having problems with a spreadsheet I dump it into access first, access will import excel really well. Then it's easy to get into SQL Server.DamianIta erat quando hic adveni. |
|
|
|
|
|
|
how to read data from Excel sheet to sql server