| Author |
Topic |
|
Richy1
Starting Member
27 Posts |
 Posted - 2007-05-09 : 11:36:29
|
| Hi all. I have a few Excel sheets with a large amount of data, and i wish to export them into the SQL database. I have already made some tables, but currently all i can do is copy and paste into one individual cell, and thats not the best way of doing it. Any ideas? thanks. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-09 : 11:42:59
|
| Use data import wizadr in sql server. |
 |
|
|
Richy1
Starting Member
27 Posts |
Posted - 2007-05-09 : 12:07:32
|
| thx.....cant quite find the data import wizadr in the SQL server, could u let me where it is please? thx. |
|
|
|
Richy1
Starting Member
27 Posts |
Posted - 2007-05-10 : 05:04:31
|
| any help here guys? cheers. |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-05-10 : 05:13:46
|
| What version of SQL SERVER you are using??In 2000, you can right click on the table through EM and click on the Import option, and then wizard will start.In 2005, check out the following linkshttp://www.databasejournal.com/features/mssql/article.php/3580216Chiraghttp://chirikworld.blogspot.com/ |
|
|
|
Richy1
Starting Member
27 Posts |
Posted - 2007-05-10 : 06:42:08
|
| Im on 2005. Im looking through the link and it suggests to use "SQL Server Business Intelligence Development Studio" this is a stand alone program away from "Microsoft SQL Server management Studio Express"? thx |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Richy1
Starting Member
27 Posts |
Posted - 2007-05-10 : 07:02:22
|
| Cheers, im a total newbie to this, but where does the select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')be placed? Assuming one creates a new table, but how do u manage to place the code? thx |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-05-10 : 07:23:42
|
quote:
Originally posted by Richy1
Im on 2005. Im looking through the link and it suggests to use "SQL Server Business Intelligence Development Studio" this is a stand alone program away from "Microsoft SQL Server management Studio Express"? thx
http://www.databasejournal.com/img/2006/01/gl_ssis_image003.jpgI can see this link in my SSMS??Chiraghttp://chirikworld.blogspot.com/ |
|
|
|
Richy1
Starting Member
27 Posts |
Posted - 2007-05-10 : 07:48:26
|
| Nope, just havent got that at all? All i have In Tasks is - DetachShrinkBack UpRestoreDeleteRefreshProperties??? |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-05-10 : 07:59:15
|
| Well I see the best way to do this copy paste.. From your first post i see that you are copying data in one cell, well there is little bit smater way then that .. 1) Open the Excel Sheet, and select all the records in the Excel sheet. Make sure that all the columns and rows are selected which has the data. 2) Through the object browser, map the table and right click on it and click on the open table. After press CTRL +3, and in the query pane type the query, Select Top 0 * From yourTable. Then again prese CTRL +3, after that Select the complete first row and do CTRL + V. All the records will start pasting in your SQL SERVER Express.. make sure that column order in the Excel and in SSMS is same.. Hope this helps you ..Chiraghttp://chirikworld.blogspot.com/ |
|
|
|
|
Exporting data from Excel to the SQL database?