How to aelect subsets of query - Not TOP

[awholtsIT]

I have a query set result of 135,000 records that I need to get into Excel. Excel is limited to about 65,000 rows.

I'm aware of the TOP function where I could select the TOP 65,000, leaving me with the remaining or bottom 70,000 records in the query results to get into a second Excel file.

What is the best practice strategy and / or function to use to
accomplish this task?

Thanks for your help in advance.

Andrew

 

[SkipVought]

hi,

What do you plan to do with the data in Excel?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[simian101]

What is your final goal? Is spreading it across 3 sheets going to be helpful? Or are you needing some kind of report?

Simi

 

[simian101]

Sorry Skip, you beat me to it.

Simi

 

[hmckillop]

You could use SSIS to output the file as a Comma Seperated file, (or even bcp or osql or sqlcmd) then you can open the file in excel which will automatically split the file into seperate sheets.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[awholtsIT]

The data will eventually be imported into an accounting application that requires that the import file be an Excel file format.

So the query results will have to be broken into 3 separate Excel files due to the limitation of rows in an Excel file.

I don't know how to programtically isolate the query results in groups of 50,000 or so, without having to go through the data set and creating a where clause based upon exact data values. I was hoping to avoid that.

Appreciate your reply.

Andrew

 

[SkipVought]

then you can open the file in excel which will automatically split the file into seperate sheets.

Not so with 2003 or earlier. I don't know about 2007.

But separate sheets may not help, depending on how the data is to be used. There are other options.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[hmckillop]

You are correct this is functionality within Excel 2007.

However if this is to be in "excel" format I assume it can accept a comma seperated file. If this is the case then you have many options available but SSIS would be my recommendation.


If this was my job, I would be clarifying the file specification to the accounting application and then use Sql integration Services to query and output the file.

Just my 2p

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SkipVought]

The data will eventually be imported into an accounting application that requires that the import file be an Excel file format.

Do you plan to aggregate the data before the workbook is sent to accounting?

Here's an option: From Excel, open the PivotTable Wizard and Get External Data. This assumes that you have an ODBC Driver configured for your SQL Server. Use a Query to grab the data (similar to the query you are now using). The SUMMARIZE the data, be dragging the appropriate fields intot the PivotTable Layout. Chances are very good that the resulting report will fit on one sheet.

Alternatively, link your SQL Server table(s) to an MS Access database, and use MS Query in Excel to access that linked table(s) vial a query as a CROSSTAB, to summarize your data in Excel.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[markros]

What is your SQL Server version? You can use Row_Number() function in SQL Server 2005 and up to provide paging capabilities.

See

http://www.code-magazine.com/article.aspx?quickid=0709031&page=5

and

http://www.code-magazine.com/Article.aspx?quickid=0703041