Database to Excel

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
LVL 7
rohanbairat3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShogunWadeCommented:
Excel has a limit of 64K rows per sheet so you would have to split it into multiple sheets.
0
Partha MandayamTechnical DirectorCommented:
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
rohanbairat3Author Commented:
there is no priimary keu on that table ...

-rohan
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ShogunWadeCommented:
Split it on a different field then
0
Partha MandayamTechnical DirectorCommented:
split on a date field or something like that
0
sigmaconCommented:
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
rohanbairat3Author Commented:
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
rohanbairat3Author Commented:
I am going to add a seperate column and put a row id in it .

-thanks guys
0
Partha MandayamTechnical DirectorCommented:
add an identity column which will automatically populate and then you can split on that.
0
SashPCommented:
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
rohanbairat3Author Commented:
We provide some kind of data to our customers and they want it in excel format ... :)

-rohan
0
SashPCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.