m*********y
发帖数: 389 | 1 I was trying to use SQL Server 2008's Import and Export Wizard to report
more than 1 million rows directly into EXCEL. Every time when I choose from
a list of the Excel versions they support, I chose 'Excel 2003' -- obviously
, only Excel 2003 and above version allow more than 65k records. Then I
specify the output file name and location and click 'Finish'.. However, the
wizard would ignore the version I selected for Excel, and still went ahead
and exported into an earlier versions of Excel, like Excel 97... the final
Excel output file would contains about 65k.. You can imagine how pissed off
I am ...
Later I figured out the solution: create the Excel 2003 output file FIRST,
as opposed to specifying the output file name in the Wizard.. and then in
the Wizard, go to the path of the file I already created and select the file
, and then I got the output file that contains the 1 million (here 1 mil is
just a ballpark number) records..
The whole process took me couple of hours to figure out...
Now I am thinking of reporting this inefficienty to MS, how do I report a
bug to MS? | i****a
发帖数: 36252 | 2 it's not a limitation on SQL server. It's a well known limitation on
excel, I've ran into this a few times and have to custom program output
to different worksheets to get around it.
according to M$, excel 2003 limits to 65k rows, 2007+ is "over one
million rows"
http://msdn.microsoft.com/en-us/library/ff700514.aspx
report
from
obviously
I
However,
the
ahead
final
pissed
off
FIRST,
【在 m*********y 的大作中提到】
: I was trying to use SQL Server 2008's Import and Export Wizard to report
: more than 1 million rows directly into EXCEL. Every time when I choose from
: a list of the Excel versions they support, I chose 'Excel 2003' -- obviously
: , only Excel 2003 and above version allow more than 65k records. Then I
: specify the output file name and location and click 'Finish'.. However, the
: wizard would ignore the version I selected for Excel, and still went ahead
: and exported into an earlier versions of Excel, like Excel 97... the final
: Excel output file would contains about 65k.. You can imagine how pissed off
: I am ...
: Later I figured out the solution: create the Excel 2003 output file FIRST,
| g***l
发帖数: 18555 | 3 EXCEL文件FORMAT是最烂的，宁可用CSV或者TAB DELIMITED TEXT FILE，也不用EXCEL | m*********y
发帖数: 389 | 4 I know!! But client requested Excel, not that I have a choice.
At the end, I was able to export about 1.5 million records into an Excel
2010 workbook. But I was only able to do that by following this order: first
, create the Excel 2010 workbook, then in the Wizard destination, find the
path and location of the already created Excel 2010 workbook... the default
of the export Excel is Excel 97.. that is what I am complaining about...
Anyway, looks like users just take it as it is, can't do nothing about it??
We pay for the license... | H*******g
发帖数: 6997 | 5 TAB DELIMITED FTW
【在 g***l 的大作中提到】
: EXCEL文件FORMAT是最烂的，宁可用CSV或者TAB DELIMITED TEXT FILE，也不用EXCEL
| i****a
发帖数: 36252 | 6 did you read my reply?
use multiple sheets
or use multiple files if you don't want to deal with sheets
Excel
first
the
default
about...
it?
?
【在 m*********y 的大作中提到】
: I know!! But client requested Excel, not that I have a choice.
: At the end, I was able to export about 1.5 million records into an Excel
: 2010 workbook. But I was only able to do that by following this order: first
: , create the Excel 2010 workbook, then in the Wizard destination, find the
: path and location of the already created Excel 2010 workbook... the default
: of the export Excel is Excel 97.. that is what I am complaining about...
: Anyway, looks like users just take it as it is, can't do nothing about it??
: We pay for the license...
|
|
