想report SQL Server 2008的一个不足之处--output more than 1 million rows into EXCEL - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

想report SQL Server 2008的一个不足之处--output more than 1 million rows into EXCEL

想report SQL Server 2008的一个不足之处--output more than 1 million rows into EXCEL# Database - 数据库

f*n2011-12-07 08:12

1 楼

本人有机会给某个期刊推荐10+个Review Editor，也就是审稿人。期刊是Frontiers in
Materials （carbon－based materials section），是个open－access期刊，目前还
没有影响因子。该系列的其它期刊影响因子在2-5之间。大家可以自己Google。
这个期刊的Review Editor（审稿人）也在其Editorial board里面，每个Review
editor都可以在其网站上建立自己的Profile。
感兴趣的朋友请和本人联系，并附上简短的CV。注意：不能保证马上就有审稿机会。

m*y2011-12-07 08:12

2 楼

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?

f*e2011-12-07 08:12

3 楼

您好！我很希望能得到这样的机会，我的email是[email protected]/* */ 现在在
University of Minnesota 做博后。如果可以我可以寄给你我的CV。十分感谢！

i*a2011-12-07 08:12

4 楼

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*l2011-12-07 08:12

5 楼

EXCEL文件FORMAT是最烂的，宁可用CSV或者TAB DELIMITED TEXT FILE，也不用EXCEL

m*y2011-12-07 08:12

6 楼

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*g2011-12-07 08:12

7 楼

TAB DELIMITED FTW

【在 g***l 的大作中提到】

: EXCEL文件FORMAT是最烂的，宁可用CSV或者TAB DELIMITED TEXT FILE，也不用EXCEL

i*a2011-12-07 08:12

8 楼

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...