April 27, 2005 at 10:50 pm
Hi,
i need to export data from excel to sql server database,
using TSQL statement
Any one can help me??
April 29, 2005 at 2:55 am
It is possible - two was spring to mind, using creating and OLE instance of the Excel object using sp_OACreate, etc., which is messy and heavy going or by linking to the Excel file as a linked server using the Jet 4 OLEDB drivers, which is suitable for a fixed file location.
If you are using SQL 2005 have you considered using a CLR routine, e.g. C# or VB.NET to take to Excel. In my experience this would be more suitable for your solution.
An other option would be to use DTS/Integration Services.
April 29, 2005 at 4:28 am
Import from Excel xls files really depends on how the data got there. If a savy user starts formatting columns you are in too deep, SQL Server does not like a field with 3 numbers like Excel uses for the Phone or Social Security Number "format". I find that I have to save the Excel file as a Tab delimited text file to get what I see in the XLS. Even then, import in to a stage table with all columns varchar or nvarchar, then clean up the mistakes (Excel allows N/A in numeric columns, etc.), and finally import into production tables.
If the data got there from an ODBC or OLEDB import, life is good, your direct import should work without a hitch as the import treats the XLS as a table and defines a datatype for each column. Instead of the default in Excel of general, which should mean anything anwhere and it will change the datatype per cell.
Good luck...
Andy
April 29, 2005 at 7:33 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply