March 21, 2008 at 10:57 am
Hello,
I have one problem in SQL,
I want to Import excel data to SQL table using Formated excel file
or also export excel data to excel file using formated excel file using T-SQL.
Please help
Thanks
Dipak
March 21, 2008 at 12:45 pm
SSIS can do both of these things.
You can also do them with Open Rowset, if you open that up in the Surface Area Configuration Manager.
Books Online has good data on how to do both of these things.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2008 at 9:30 am
I tend to move data into Access before loading SQL instead of Excel. I have had problems loading large data elements from Excel, but putting them into an Access Memo field first has never resulted in truncation.
March 26, 2008 at 1:36 am
I regularly move data in both directions.
Excel into SQL Server - I firstly name the range of data I want to import including the headers [as I have other rows of information in the file] and then import into an Access table. This allows you to check the columns are correctly identified as Text, numeric, date etc as this can be problematic if you have null values in the first row/s of the data, especially with dates. I then export to SQL Server and then I also usually change the owner as it comes in with my login and not as dbo.TableName.
With exporting SQL into Excel I generally run a query in SQL Server Management Studio, and if not already done I set the -Tools-Options-Query Options to "results to Grid" and check the option of Include Column Headers if that is what I need.
Another option is to use the -Data-Import External Data directly from Excel - a wizard takes you the logon and you can import a table or optionally choose SQL from a drop-down box and write the query directly in a textbox. The benefit of this is the datetime data is correctly formatted in Excel, the downside is no SQL hints - but you can always copy & paste a complex query in
March 26, 2008 at 3:23 am
If you are looking to do this in T-SQL, you can create Linked Server to the Excel sheet or use OPENROWSET in your queries. Using both options, you will be able to access the Excel sheets using T-SQL. You may have to try out to see if the above options can read formatted Excel sheets and whether the formatting creates problems. Check out the link http://support.microsoft.com/kb/321686 for more info.
July 29, 2008 at 12:27 am
You can use the OPENROWSET query to bulk import data from excel sheet into SQL. If the server is SQL Server 2005 you have to enable the Ad hoc queries in the Start Menu --> SQ Server 2005 --> Configuration Tools --> Surface Area Configuration.
The OPENROWSET query is given below:
SELECT *
INTO db1.dbo.table1
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls',
'SELECT * FROM [sheet1$]')
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply