Data sorting problem, SQL to Excel

[Hondy]

Hi

When running a view in SQL the data is sorted correctly. When running the view via Excel 2002 the data appears to ignore the SQL order by statement. Each time the View is run the data appears on the sheet in a seemingly random order each time - that is, no sorting or ordering at all.

Any ideas?

Cheers

 

[ajrimmer]

In Excel - on the imported data worksheet, if you click on 'Data' - 'Import External Data' - 'Data Range Properties' there is a tick box for 'Preserve Column Sort/filter/layout' - Have you tried ticking that?

 

[xlbo]

Please post the method you are using to get the data into Excel...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Hondy]

hi

yes ajrimmer, I have tried that, it didn't work unfortunately.

xlbo, same as described by ajrimmer - it just seems to lose the "order by" when running it.

Cheers!

 

[SkipVought]

In Data > Import External Data > Data range properties, UNCHECK Preserve column sort/filter/layout

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

What is the SQL for the view? does it have an explicit ORDER BY statement? If not you are probably just seeing the difference between how Excel does its default sorting and how SQL Server does it.

If it does have an explicit order by sequencem, can you give us an example of the data in the field(s) it is ordering by?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Hondy]

well, yes it has an "order by" statement which is why its a bit odd. When the view is executed the data has no sorting at all, the data appears in excel with absolutely no sorting whatsoever. And as the data is changing constantly in the database each time you execute it then you get a new random set of unsorted data.

I've tried the preserve sorting, on and off - without the data appearing in some sort of order then the data is useless.

I can't really post the view but is there any way the contents of a view would be the problem? I expected this to be an Excel issue in all honesty as the SQL side works perfectly within SQL Query Analyser.

Thanks for the assistance

 

[xlbo]

Can you post the ORDER BY statement and the kind of data those fields hold?

Never heard of Excel being uppitty about this kind of thing before and I have queried data into excel many many times in a variety of ways.

My 1st thought was to untick the "Preserve layout" stuff but you say that doesn't work?
When you do the Data>Get External Data, how are you referencing the view? what is the SQL that is getting the view data for you in Excel?

Select * FROM ViewName ?

You may need to change that to add your own order by clause:

Select * FROM ViewName ORDER BY Field1, Field2



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question