question

vsrini008 avatar image
vsrini008 asked

Export data from SQL Table to Excel

I have table with 8 lakh records, need to export to excel file. I have tried exporting data to excel sheet 2007 (.xlsx) but data is getting imported till 65000 records, both through sql and ssis. I'm using SQL Server 2008 r2 and MS Office 2007. Appreciate your help !!
sqlssissql server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hdavodi avatar image
hdavodi answered
Hello, If you can upgrade your Office to 2010 or later, then install and enable [PowerQuery][1] for Excel, then you easily connect to your database and load all the records from the table. [1]: https://www.microsoft.com/en-us/download/details.aspx?id=39379
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vsrini008 avatar image vsrini008 commented ·
@hdavodi. Thanks for the quick reply i'll check it. :)
0 Likes 0 ·
vsrini008 avatar image vsrini008 commented ·
@hdavodi. Its working fine but have an issue with integer values (1.36648e+009). Tried with format cell option in excel but not fixed. appreciate your concerns.
0 Likes 0 ·
hdavodi avatar image hdavodi commented ·
You're welcome. it should work, it does for me. If it is a Whole Number, then just select "Use 1000 Separator", and if it has decimal, increase the "Decimal Places" until covers your decimals.
0 Likes 0 ·
vsrini008 avatar image vsrini008 commented ·
It did not worked for me but fixed by changing the query..:)
0 Likes 0 ·
erlokeshsharma08 avatar image
erlokeshsharma08 answered
Here is what I can think of :- 1. add a row_number kind of column that would give you the serial number of any row 2. while exporting you have the option of saving the ssis package, so choose this option 3. try to modify the saved package. 4. you need to modify the query such that row_number < 65000 5. map it to sheet1 6. add another data flow and modify the query as row_number between 65000 and 65000+65000 7. map it to sheet2 This would litle bit of hands on with ssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.