Exporting Data directly into Excel from SQL makes it too big to handle

Question

Exporting Data directly into Excel from SQL makes it too big to handle

pujain

SSCrazy

Points: 2903
More actions
January 1, 2014 at 11:29 pm

#286685

Hi,
I have a situation where I have to pull 2.2 million records with 302 columns.
I cannot run query and copy paste it to excel in one go as it causes memory issue and my computer hangs every time.
so I tried to directly copy it from SSMS to Excel but this makes the excel file in GB.
is it something which control the size of excel as when copying it directly to excel from SSMS.
I tried to copy the data per column and paste it to excel (I have to repeat it 302 times), by this the file size was in MB.
so why there is so big difference in file size when pasting the data manually and when importing it directly to excel.
please suggest.
August 21, 2023 at 2:21 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

SSMS has an option to save a query results directly to file, without displaying it(Control + Shift + F, then execute the query);

that's an option you might want to try instead, and just save the file to .csv format.

BCP.EXE out or sqlcmd is a better option to manipulating large amounts of data to disk.

Excel's not the right tool to be using for massive numbers of rows of data. large files like that should be handled with text editors, like NotePad++ or EditPlus instead of Excel.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rahul Bhosale SSC Eights! Points: 931 More actions · Answer 2

One more thing can be done is to use the DTS Wizard.

1. Right click on Database in SSMS.

2. Select Tasks-> Export data

3. Choose Datasource as SQL and give server name with authentication.

4. Choose Destination as Microsoft Excel and give the excel file path.

5. Select "Write a query to specify the data to transfer".

6.Parse it click on next till you get the Finish button.

7. You can also save the package to re-run.

For SQL server resources please click...

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Have you consider that you can't paste 2.2 million rows in a single spreadsheet?

Excel will limit the rows to 1,048,576.

You're going to be missing over 50% of your data in Excel.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Rahul Bhosale SSC Eights! Points: 931 More actions · Answer 4

Thats true. Also DTS has problem with temp tables i suggesr using CTE if there is any temp tables used.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 5

Excel has its own limitations as pointed out but why you need to retrieve millions of rows from the database. Is it going to be used for some reporting? Fetching this much is going to hit the disk hard, possibly flushing the important cache and making you wait for long.

If the requester is planning to use filters in the excel itself for 3 different cases, it will be better to give him 3 different files for all those 3 cases:-)

Thanks

Chandan

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 6

Gosta Munktell

SSCertifiable

Points: 6004

January 3, 2014 at 3:38 am

#1677181

Take a look at PowerPivot .

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 7

My question would be why do you need to export so much data? It would make more sense to display or use it directly in a database.

2.2 million rows is unmanageable for a reason. Who can read that much data?

pujain SSCrazy Points: 2903 More actions · Answer 8

Thanks for your replies.

why I need this much of data : This is the requirement by Higher management for some analysis purpose, I know this much of data is of no use for them but if they need it they need it.

let me rewrite my question in a better way:

my question is: when I try copy pasting the data manually from SSMS to Excel/CSV the file size is less but when I copy it directly from SSMS to EXCEL "Result to Text" the file size is much bigger.

why is it ?

Lowell SSC Guru Points: 323518 More actions · Answer 9

pujain (1/3/2014)
Thanks for your replies.
why I need this much of data : This is the requirement by Higher management for some analysis purpose, I know this much of data is of no use for them but if they need it they need it.
let me rewrite my question in a better way:
my question is: when I try copy pasting the data manually from SSMS to Excel/CSV the file size is less but when I copy it directly from SSMS to EXCEL "Result to Text" the file size is much bigger.
why is it ?

probably the clipboard.

when you copy and paste, it's probably grabbing font size, font type, grid width and other non-obvious properties from SSMS, so when you paste them into excel, it's pasting an object, that happens to have rows and columns, so you get those in addition to the obvious data.

when you paste to a simple CSV file, you get only the text from the clipboard, and not the other attributes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 10

pujain (1/3/2014)
Thanks for your replies.
why I need this much of data : This is the requirement by Higher management for some analysis purpose, I know this much of data is of no use for them but if they need it they need it.
let me rewrite my question in a better way:
my question is: when I try copy pasting the data manually from SSMS to Excel/CSV the file size is less but when I copy it directly from SSMS to EXCEL "Result to Text" the file size is much bigger.
why is it ?

This many rows and columns in native Excel format is not possible.

PowerPivot would handle it.

Or split into multiple files by something like date might be an option.

But then trying to summarize or otherwise do analysis becomes harder.

If you know that much of the data is of no use to them, try talking to them about what they are trying to see.

Maybe a standalone cube, with ability to drill to detail, is an answer.