-
DTS Excel data exceeds 65536 rows
I have a sql 2005 table with around 135000 rows which need to be exported to a Excel file (office 2007).
Excel has limitation of 65536 rows.
Please suggest if we can split data and then export in two files or two tabs etc.
Also, if someone can suggest - we can write query
SELECT TOP 65536* FROM tbl_Name
What to write for the remaining rows?
Thanks
-
Can filter by column in where clause or add order by in select.
-
First,You can try add id(use row_number() function) for the recordes,then you can use id filter.
-
Else,you can choose use xp_cmdshall.
First, open xp_cmdshall, execute following clause
EXEC sp_configure 'show advanced options', 1
GO
-- RECONFIGURE
RECONFIGURE
GO
-- start xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
--RECONFIGURE
RECONFIGURE
GO
and then give a example
EXEC master..xp_cmdshell 'bcp "SELECT column1,column2,column3 from ( SELECT row_number() over(order by getdate()) as ''id'', column1,column2,column3 from databasename..tablename ) t where id between 1 and 65536" queryout d:\authors.xls -c -S"servername" -U"sa" -P"password"'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|