Results 1 to 13 of 13
  1. #1
    Join Date
    May 2006
    Posts
    6

    Red face Unanswered: export more than 65K rows to excel (was "Plese help....i am in difficulty")

    Hi...
    I am new to MS SQL 2000. I need to generate reports using the SQL2000 database and exporting the data to Excel. Currently i started using the DTS services and was fine..But i have constraints that Excel can hold only 65K records..My records can go up to 80K +.Is there a way i can split the sheet depending the number of records..or is there a better way to do this...I do not have reporting services as we r using SQL 2000...Could anyone please help...

    If there is any other way of doing this please guide me as to how to go about or any reference websites...

    Thanks in advance
    Banu

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Why don't you use the text file directly in the DTS to import into SQL server.

    http://www.sqldts.com/default.aspx?258 is a good resource to know more about DTS in addition to the Books online.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    May 2006
    Posts
    6
    Hi
    But the customer needs the report in Excel format and not in Text format...THe records are mroe than 65536 ...

    Banu

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your customer is an idiot. Get a new one.

    You could load the data into a temporary table, export the first TOP 65K to one file, and then the remainder to a second file.

    If you are looking for a way to autmatically split it into two files, no, I don't think so. You will have to write code to do this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2006
    Posts
    6
    Hi blindman...
    Wish i could call him the same..Anyway i need to solve the problem..If i ahve to write a code..how do i go about...I don't even know how to start..forget about writing...I am using right now Data Transformation Services to extract data..Is there a way as to how to get this done..I think we have to write an ActiveX script..But how..ANybody who have gfaced such kinds asituation..please help me...
    U can just let me know atleast where i can get help...atleast..may be a code...or anyway of help would be helpful for me...

    Thanks in advance

    Banu

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Why not use SQL 2000 Reporting Services? You'd need to download them, but that doesn't strike me as a huge problem. That opens up many possible answers for you.

    -PatP

  7. #7
    Join Date
    Jun 2004
    Posts
    50
    Quote Originally Posted by godhelp
    Is there a way i can split the sheet depending the number of records
    Do you uderstand the part where Blindman offered:
    You could load the data into a temporary table, export the first TOP 65K to one file, and then the remainder to a second file.
    If you do this percentage wise (SELECT TOP 50 PERCENT...). It seems to me that you could import one of the resulting files into the other using another sheet. Then you could send your ignorant customer one file (assuming the 65k record limit doesn't count between sheets).

    My two cents. I feel for ya!
    Monk
    The person who confesses ignorance shows it once; the person who conceals it shows it many times.

  8. #8
    Join Date
    May 2006
    Posts
    6
    Thanks everybody for that help...
    Note : Pat i can't use reporting services because i beleive we need a third party software to desing the a report that is Visual studio or so..which we do not have.

    Monk: i did understand wht blindman was trying to tell...But i don't know how to do it...in my query i need to check for the number of records extracted..if it is more than 64K then i need to copy the remaining data to another file ..if not then copy to smae cheet..i know the logic..but how to do it in SQL i don't know..how to go about getting the top 50 percent if the number of rows are more..etc...

    Hope one of u can help me
    Thanks
    Banu

  9. #9
    Join Date
    May 2006
    Posts
    6
    I am using Data Transformation SErvices in MS SQL 2000 right now... hope i can find a solution within this application(DTS) itself...

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Load the data into a temporary table.
    Select and export the top 65K (using the TOP clause) ordered by the primary key.
    Delete the top 65K ordered by the primary key.
    Repeat until there are no records left in the temporary table.

    If you are confident that you will not be approaching the 130K barrier anytime soon, you could hard-code to always export two files. That would simplify the coding. You could also split the data roughly evenly based upon some value in the data (gender, for instance), which would also make coding easier.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you logically break this 80K rows into pieces? In other words, if the 80,000 rows are for the known universe, can you split it into pieces for different solar systems? If you can, then you could split the DTS extract into different worksheets within your Excel file.

    The problem that lies underneath everything that you are complaining about is that Excel 2000 will only support N rows in a worksheet, and you want 1.5 N rows exported. The only way that I know to work around that is to export something less than N repeatedly until you get everything you need out. The easiest way to do this (from your client's perspective) is to split the feed based on some criteria that means something to the client (so they'll know which worksheet page to look on for a specific row).

    -PatP

  12. #12
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    If not get add-on to excel, such as the e.Spreadsheet Engine has the capacity to calculate up to 1 Billion rows and 32 thousand columns of data. If you are using the e.Spreadsheet Engine for Excel reporting, then you will need to stay within the Excel limitation of 65000 rows. If your data might surpass this limit, then you can add logic within the e.Spreadsheet Designer which allows you to burst data automatically across multiple worksheets.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  13. #13
    Join Date
    May 2006
    Posts
    6
    Thank you everybody who have responded and helped me..I am trying out ways u have suggested. Will get back to u guys if i cannot find a solution...
    Thanks
    Banu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •