SQL 2000 & Excel 2007 format

Question

SQL 2000 & Excel 2007 format

Roust_m

SSCoach

Points: 17410
More actions
February 4, 2007 at 9:55 pm

#62346

Hi,
I need to export a report into an excel file from SQL 2000 which contains more than 65k records. Is there a way to make SQL 2000 server be able to do it in Excel 2007 format so to make use of 1Mln rows limit instead of 65k?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

I have not done this any time... but check the following thread..

http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/95f0f1a88146b161/4fac5cdcf6f93046?lnk=st&q=how+to+import+%3E+65000+rows+into+excel+from+sql&rnum=1#4fac5cdcf6f93046

MohammedU
Microsoft SQL Server MVP

Roust_m SSCoach Points: 17410 More actions · Answer 2

Thanks, but I already have a solution which breaks data into separate worksheets, which is a great CPU killer, so I want to find a way to dump everything into one Excel 2007 worksheet.

Lowell SSC Guru Points: 323518 More actions · Answer 3

can't you simply export as comma or tab delimited, but name the the file xls so that it will open in the expected program? I know I do that all the time and it works fine, as some of the datasets I work with (census tracts) are often more than 65535 rows of data.

For that matter, why not just name the files .txt so if you opent he file with a default program, data would not be hidden the way it is in Excel. You mentioned an excel Report...Are you opening a template and inserting data with an excel driver, and that is why you need to stick to using excel formats? I know if you write data to an HTML format, but make the extension .xls, Excel will open the file and maintain all fonttype/size/itealic/bold/borders/underline that is contained in basic 4.0 HTML, as well as keeping other styles if the css is available. that might be another option...write the report as html and give a diff extension.

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!

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

You could create a target spreadsheet in Excel 2007 and an ODBC DSN to it. It seems a bit flakey though.

You can also create an XML document and give it an xls file extension. Excel 2007 will figure this out.

If you want to be really tricky, the Excel 2007 (and all office products for that matter) are stored in a zip file as xml documents. Change an Excel 2007 document's extnsion to .zip to see what I mean. The format is easily reproduced and MS has published the details on the msdn website.