CSV Dates Not Sorting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Thornburg - 03 Apr 2008 16:03 GMT

The CSV column that is in issue is filled with dates and nulls. It is an
exported list from a software program we use.
When you open the CSV file with Excel and it automatically converts, it sets
the date fields type as General. Even after you change the field to a date
from General, it still does not reconginize it as a date field. It gets
!value errors when using =Year() and it does not sort by date it sorts
alphanumerically.

Current Workaround:
If you change the filename extension to XLS so it doesn't auto convert the
CSV, you have the option to do the "text to columns" button. With this, you
can specify each column and mark the date columns as dates. When the
conversion completes you can use Year() and sort by date properly.

Is there any easier way to fix this problem so I don't have to teach people
how to use "text to columns"? Ideally, I want them to be able to just click
on the CSV file and not have them do anything technical.

Thanks,

Reply to this Message

Pete_UK - 04 Apr 2008 00:13 GMT

If the "dates" look like dates, then try this:

Click on an empty cell somewhere and click <copy>. Then highlight the
offending "date" cells and Edit | Paste Special | Values (check) | Add
(check) | OK then <Esc>. If you get numbers like 39456, then you will
need to format the cells as dates.

Hope this helps.

Pete

On Apr 3, 4:03 pm, Chris Thornburg
<ChrisThornb...@discussions.microsoft.com> wrote:

> The CSV column that is in issue is filled with dates and nulls. It is an
> exported list from a software program we use.
[quoted text clipped - 15 lines]
>
> Thanks,

Reply to this Message

Earl Kiosterud - 04 Apr 2008 03:30 GMT

Chris,

If the dates look like dates with General formatting, then they're not true dates, and
changing the formatting to date won't do any good. I suspect that the date format in the
CSV file isn't one Excel recognizes as a date, and so it's being put in the cells as text,
with General formatting. Give some examples, and we can probably write you a formula for an
another column that will change your text dates to real dates. Then they'll sort as dates.

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> The CSV column that is in issue is filled with dates and nulls. It is an
> exported list from a software program we use.
[quoted text clipped - 15 lines]
>
> Thanks,

Reply to this Message

Chris Thornburg - 04 Apr 2008 13:33 GMT

http://download.yousendit.com/0F79C8C04258E709

This is the example I am using. It seems like Excel would be able to figure
out this column is a Date column on the auto convert instead of making it a
General. I do know how to make a formula in the next column to correct this.
However this is not for me. This is for a simple non technical user. This
seems like something that shouldn't require effort on the end users part
besides possibly changing the column format. Thank you for replying and the
help.
PS: I am currently using Excel 2007 for those trying at home.

> The CSV column that is in issue is filled with dates and nulls. It is an
> exported list from a software program we use.
[quoted text clipped - 15 lines]
>
> Thanks,

Reply to this Message

Earl Kiosterud - 04 Apr 2008 16:05 GMT

Chris,

I've looked at your CSV file. It's the space before the date that's causing Excel to not
recognize it as a date and convert it. If you can get your application to leave out that
space, it'll work. If not, try this in a helper column:

=VALUE(D2).

Copy down by dragging the Fill Handle. You'll need to format the column for the date you
want (Format - Cells - Number - Date).

You can select and Copy this column, then select the column with the dates (D), and Edit -
Paste Special - Values) and then your original date column will be replaced with the fixed
dates. Then you can remove the helper column.

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> http://download.yousendit.com/0F79C8C04258E709
>
[quoted text clipped - 26 lines]
>>
>> Thanks,

Reply to this Message

Billy Liddel - 04 Apr 2008 23:37 GMT

Chris

Another way, if you don't mind macros. Copy the code into a VB Module - Alt
+ F8, Insert Module, paste this code

Sub RemoveSpace()
For Each c In Selection
c.Value = Trim(c)
Next c
End Sub

Return to file, select the data columns D & E then run the MAcro Alt + F8.

As you will probably be doing this often, it is a good idea to save the code
in your personal Excel file, then it will always be available.

Peter

> http://download.yousendit.com/0F79C8C04258E709
>
[quoted text clipped - 26 lines]
> >
> > Thanks,

Reply to this Message

Rate this thread: