RE: [ACFUG Discuss] Off Topic: MS Excel Sort Corrupts Mass Data.

Robert Reil Fri, 16 Jun 2006 12:03:20 -0700

Well we do that when appropriate but sometimes we have so much organization to do that we sort this column this way and make appropriate mods by populating a cell and copying by dragging the little plus sign down till the appropriate items are populated.

Then we resort in another way and do the same. Some times we need to have a numerical autonumber, then resort again. Etc.

The problem is that when data arrives as new products we have it as a csv, and have to populate approximately 35 more columns before we can push it to the table in the DB.

If we have a thousand products to do this to it take a while and the most efficient way is to keep sorting it as appropriate.

I understand if my SQL coding skills were better (I have none and just read a quick tutorial on it) I may be able to do things better. But for now Excel seems like the only solution and it does not work as data keeps getting corrupted.

ARGHHHHH!!!!!!!!!!!

Robert P. Reil

Managing Director,

Motorcyclecarbs.com, Inc.

4292 Country Garden Walk NW

Kennesaw, Ga. 30152

Office 770-974-8851

Fax 770-974-8852

www.motorcyclecarbs.com

From: Teddy Payne [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 15, 2006 5:30 PM
To: [email protected]
Subject: Re: [ACFUG Discuss] Off Topic: MS Excel Sort Corrupts Mass Data.

If your are exporting, why not sort it prior to the export? Let the database do the sorting for you.

I would store the data for the export into a transition table that is truncated prior to every export. You insert the data into in the sort order that you need and then export the data into excel from there.

Have the database keep integrity and not depend on the spreadsheet. The data may not be stored as unicode and leading data on certain columns may be omitted, especially numbers with leading 0s.

Teddy

On 6/15/06, Robert Reil <[EMAIL PROTECTED]> wrote:

Any one there have skills or work with someone who has heavy Excel skills that can answer this? I have searched google for 2 hours for a solution. Can find none.

I have to do a csv export of our database table which equates to 9000 rows by abou 40 columns.
Some of these columns host full html data.

The problem is that while managing this data while integrating a large amount of rows into the csv we sometimes have to sort by column. We have done this both by using the A/Z sort button short cut, and by clicking the upper left box where all cells get highlighted, clicking Data, then Sort, and choosing appropriate columns to sort by. Sometimes we hide columns in order to manage 40 columns of data. However from time to time in a manner we can not yet duplicate or determine (though we have a suspision that it is hidden data) we find that data that was was not sorted even though we choose to have all fields sorted becomes disassociated with it's respective row and the data ends up being corrupted. Though this is not to say that we are sure that hidding data is even relevent.

Has anyone seen this behavior?
Is there a fix, or switch, or different tool we should be using to do mass sorting, and resorting without fear of loosing data integrity?