Exporting to CSV

Question

Post reply

Exporting to CSV

Informer30

SSC-Addicted

Points: 436
More actions
March 24, 2014 at 8:57 am

#305576

Hi All,
I am exporting data from sql 2008 r2 db to csv, the maximum the csv is showing is 1,048,576 whilst the actual count in sql is over 2 million.
Is there an limit within SSMS db/tasks/export?
thanks in advance...

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

No. How are you viewing the CSV? Use a decent text editor, not Excel.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Informer30 SSC-Addicted Points: 436 More actions · Answer 2

I am opening the csv as I want to aggregate the data.

Is there is an row limit to export to csv ?

Lowell SSC Guru Points: 323518 More actions · Answer 3

csv is just a raw text format, so there is no limit;

however most peoples default viewer for csv files is Excel; that certainly is handicapped at just over a million rows.

why can you not aggregate your data in SQL and export THAT, instead of using 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!

Informer30 SSC-Addicted Points: 436 More actions · Answer 4

business requirement to have lowest grain of data.

How can I view the outputted csv file back in csv viewer without defaulting to Excel...

I must be missing something really basic...

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 5

Like Phil said, a real text editor; NotePad++,EditPlus,UltraEdit,GVim to name a few off the top of my head.

Wordpad if you don't have anything installed already would do in a pinch.

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!

Informer30 SSC-Addicted Points: 436 More actions · Answer 6

ok - I am understanding correctly -

1. output to csv or txt

2. then in excel import the csv or txt and I should see all of the 2million plus rows?

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

Informer30 (3/24/2014)
ok - I am understanding correctly -
1. output to csv or txt
2. then in excel import the csv or txt and I should see all of the 2million plus rows?

No, you won't be able to see over 1 048 576 rows in Excel. Excel is not designed to work with so many rows, you could store it in SQL Server and have aggregated 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

OCTom SSChampion Points: 11785 More actions · Answer 8

Informer30 (3/24/2014)
ok - I am understanding correctly -
1. output to csv or txt
2. then in excel import the csv or txt and I should see all of the 2million plus rows?

No. Excel can not be used for this. Excel can not see more than 1,048,576 rows. No matter how you bring the data into Excel, it will not see more than that. You need a different method to work with the data.

Tom

Informer30 SSC-Addicted Points: 436 More actions · Answer 9

OK thanks -

confused how csv files work then, they are flat files but unable to open in a csv viewer?

aaron.reese SSChampion Points: 13498 More actions · Answer 10

@informer:

CSV stands for Comma Separated Value. The structure of the file is just ANSII code. You could create the file in Word, Notepad, or DOS editor. There is no limit to the size of the file (Disk size I suppose)

Excel is a spreadsheet. Each Excel workbook is made up of worksheets and each worksheet has cells. Cells are identified by their row and column. Excel is a versatile tool and has build in wizards to take CSV files and store the data into cells. Although from Excel2007 onwards there is no absolute limit to the number of cells you can have, the size of the workbook is limited by the amount of memory on your machine.

If you are bringing in 2M rows of data to create a pivot table then the business needs to look at a better reporting and analysis solution (an I appreciate that this is probably beyond your paygrade...) But SSRS (reporting services) and SSAS (analysis services) would be worth a look. If you know what you are doing, you could use Excel VBA code to return an aggregated dataset (i.e. the results of the pivot table) and then drill down data sets from SQL depending on the options the user selected.

There are also tools like PowerPivot which will allow you to do these real-time data analysis within Excel.

If you could tell us the end user requirements we may be able to suggest a more robust and performant solution than the specific issue you are struggling with.

Obiron

Phil Parkin SSC Guru Points: 246985 More actions · Answer 11

Informer30 (3/24/2014)
OK thanks -
confused how csv files work then, they are flat files but unable to open in a csv viewer?

A CSV file is just a structured text file. Nothing more. They can be a convenient way of exchanging data, between databases. They don't 'work' any more than that 🙂

Please leave Excel out of the equation - not only does it have the max rows limitation you have encountered, it will also try to guess the format of the data (and often guess wrong), mangling it along the way.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel