?
Solved

Database to Excel

Posted on 2004-11-03
12
Medium Priority
?
220 Views
Last Modified: 2009-12-16
I have a table which contains about 150000 rows and i need to copy it to excel file ... I cannot copy it to one single excel file and I dont have a row element or id assosiated with it ....

Can any one let me know if there is a way to do it

-rohan
0
Comment
Question by:rohanbairat3
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12484784
Excel has a limit of 64K rows per sheet so you would have to split it into multiple sheets.
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12484817
use a query to split the table into groups of 64k rows based on primary key condition. Then export each one into a separate sheet in the excel file
0
 
LVL 7

Author Comment

by:rohanbairat3
ID: 12484950
there is no priimary keu on that table ...

-rohan
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 18

Expert Comment

by:ShogunWade
ID: 12484962
Split it on a different field then
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12485190
split on a date field or something like that
0
 
LVL 8

Expert Comment

by:sigmacon
ID: 12485445
There is most likely some combination of columns that will make each row unique. If you post your column definition and what is in each column, I am sure we can help more. There's also VBA stuff in the Excel forum that addresses the problem. You may want to ask an expert there.
0
 
LVL 7

Author Comment

by:rohanbairat3
ID: 12485560
yeah ... does anyone know about splitting a table depending on rows if there is no primary key ?

thanks for the help ... i know we can do that but thats not a generic way ... i want a way to split sql server tables ...

say top 10000 in first and then 100000 in other and so on
-rohan
0
 
LVL 7

Author Comment

by:rohanbairat3
ID: 12485615
I am going to add a seperate column and put a row id in it .

-thanks guys
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12486034
add an identity column which will automatically populate and then you can split on that.
0
 
LVL 8

Expert Comment

by:SashP
ID: 12487368
What are you trying to achive??  Displaying a report with that many rows is unweildly.  What are you using the excel data for?  You may be better served passing the data into a pivot cache which can handle the number of rows that you require.

You can still get access to portions using the pivot tables ability to drill down into the data that comprises the pivot.

Cheers Sash
0
 
LVL 7

Author Comment

by:rohanbairat3
ID: 12495580
We provide some kind of data to our customers and they want it in excel format ... :)

-rohan
0
 
LVL 8

Accepted Solution

by:
SashP earned 1000 total points
ID: 12499453
I am not suggesting that you do not provide it in excel format.

Have a look at Excel's Pivot Tables and you should find that there ability to summarise data through grouping and totals whilst still providing the underlying data at a doubleclick of the mouse all this while being able to hold more than the 15000 row limit of an excel spreadsheet.

1. Pivot Tables are Excel Format.
2. Pivot Tables enable you to summarise, group data more effectively.
3. Pivot Tables allow you to simply access more data than a spreadsheet will

Cheers Sash
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question