July 26, 2011 at 12:08 am
Hi Everybody,
Recently I've started to do an assignment with excel workbook. Here am facing an critical issue, is, exporting data from DB to excel sheet, in DB it has laks of records, but when exporting completed and when trying to open the file it throws error when the records exceeded 65536 rows.
I started to look into forums on this issue, everyone was faced the same.
In below url,
http://www.sqlservercentral.com/Forums/Topic962051-148-1.aspx
I gotta some points, like if sheet 1 exceeds max count then remaining data fills in sheet 2, so on...
For this, in the above url, I took a sample to do it, but I don't know in foreach loop container where to give the expression like (LoopingCount = TotalRecordsCount/65536)??
Someone as suggested, use Excel-2007 format instead of 2003. My question is even for 2007 also will have some limits of rows, in 2003 the limit of rows per sheet is in thousands, for 2007 too will definitely have some row limit, that time the remaining records must fill in sheet 2 or not?
Can anyone suggest an idea for this issue, it will helps more for who are all i need;-):crying:
July 26, 2011 at 1:37 am
I'll state my position on this, and I know I am not alone.
If you need to store and analyse hundreds of thousands of data rows, use a database.
If you want to pass data from one db to another and there are lots of rows, do not use Excel, use a CSV file.
If your users need Excel as a front-end data analysis tool that's fine - but allow them to get their data from a database
Bottom line: Excel should not be used for large-scale data storage.,
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 26, 2011 at 1:54 am
Hi Phil,
am using .csv flat file destiantion and can able to export all the datas from db to .csv file, even some laks of datas i can export. There is no problem in exporting, when opening the file directly I got the error like exceeds 65536 rows.........., now I can use either wordpad or notepad to view the data.
Am I right?
July 26, 2011 at 2:07 am
Yes - or maybe a better editor like Notepad++. Even better, MS Access.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 26, 2011 at 12:21 pm
The newer versions of excel can display more than 65K rows, the older versions won't truncate the data they just won't display it. I know this because some people here have excel sheets in the multiple GB size range....
July 26, 2011 at 12:52 pm
+1 for everything Phil said.
When I first read the topic title I was under the impression of SSRS export to excel.
The good news is that Denali "should" be compatible with Excel 2007 export which will whack that silly limit.
July 27, 2011 at 1:54 am
+1 for Phil.
Do not double click on the .csv file, it will open up Excel as the editor (ugh. Excel even sometimes screws up the CSV format).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 28, 2011 at 6:16 am
Here are the column, row and cell limits of Excel:
Excel 2003:
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
Excel 2007:
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx
July 28, 2011 at 11:49 am
The optional Power Pivot addin (reuquires Excel 2010) can analyze millions of rows. You don't need to import millions of rows into Excel to analyze the data using Excel. Rather than going from CSV to Excel, instead connect directly from SQL Server to Excel.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply