different values depending on how text file is opened

[notMe]

Sorry for the lengthy post...

1- explanation

With File Explorer double-clicking on a tab-delimited text file is not the
same as drag'n'dropping that file into an occurrence of Excel.
All dates are changed to another format with double-clicking, they are kept
'as is' with drag'n'drop.

With double-clicking :
Dates are standard ISO (dd/mm/yyyy ) but Excel *transforms* the actual data
into mm/dd/yyyy.
The swapping of dd and mm only occurs when dd < 13. Otherwise the field is
kept left-aligned like a text filed.

day(<myCell>) gives different results depending on how the file was opened
so it is not a mere formatting problem.
The data is altered by Excel and it does not seem right. At least it should
always be done consistently.

2- example :

Text file : foo.gs
A <tab> 16/09/2006
B <tab> 08/09/2006

Displayed in Excel after double-clicking :
A.........B..........DAY(B)
A 16/09/2006 16
B 09/08/2006 09 <= wrong value : why swap dd and mm at all ?

Displayed in Excel after dragging :
A.........B..........DAY(B)
A 16/09/2006 16
B 08/09/2006 8


Any way to fix this ?

This is on a French Windows XP system, set to French(France) and the
formatting for date is dd/mm/yyyy.
I can't change this because other apps need this setting. Not sure Excel
uses it either!

Files have a .gs extension and are associated with Excel in File Explorer
options menu.

 

[Guest]

Hi,

Excel by default uses format as selected in Windows - Control Panel -
Regional and language options.
I can namely see from your example that when excel displays date correct it
does not display it as date (if the date is aligned left), but as text.
Try to change this date options to dd/mm/yyyy and then open this txt file.
If dates are displayed correctly, save the file as excel (dates will be saved
as dates) and then change date format in control panel.
If you change it for example to dd.mm.yyyy, excel should display dates as
dd.mm.yyyy.

Regards,
Nika Lampe

"notMe" je napisal:

 

[notMe]

Hi Nika,
Thanks for suggesting the settings for Windows but the Regional and language
options are already set to French(France) and date format = dd/mm/yyyy.
I have no idea how to fix this behaviour in Excel...

Another idea maybe ?

 

[Dave Peterson]

How about just dropping the doubleclicking and the drag/drop and do a File|Open.

Then you'll see a text import wizard appear when you open your .txt file. You
can specify the order of the date fields the way you need.

 

[notMe]

Hi Dave,

Thanks for telling me about the assistant etc. I know it well but I have
just too many files to deal with and also for drag'n'drop I can't trust my
users to not double-click on files (often sent by email anyway so they'd
need to save first etc.).
I can't believe Excel does not work well if you double-click on text file
that contain dates...

So I would like Excel to behave consistently regardless of the opening mode.
I mean, I've never heard of a database that *transforms* the data depending
on how you open a file... Why does drag'n'drop check my system's settings
and double-click would'nt ?
I wish all our business partners sent us files in a more reliable form but
we can't ask them to change their ways for us.

It is so bad that I recently started writing SQL code to download records
from our mainframe with, instead of the classic '01/09/2006' dates,
something like '=date(2006, 09, 01)' and then apply a macro to make the
formulas effective in each cell under Excel.
Looks so bizarre I know there must be a simple yet effective solution out
there... drag'n'drop does it, maybe something in file types association ?
some DDE wizardry ? I guess I could invest in a VB thing that would
drag'n'drop for me if I double-click...

I know there are workarounds but one day or another someone will make wrong
assumptions on our business because their file was not "opened the right
way". I would rather find a definitive solution before this happens, really.

Any idea ?

 

[Dave Peterson]

Just the one I suggested.

Hi Dave,

Thanks for telling me about the assistant etc. I know it well but I have
just too many files to deal with and also for drag'n'drop I can't trust my
users to not double-click on files (often sent by email anyway so they'd
need to save first etc.).
I can't believe Excel does not work well if you double-click on text file
that contain dates...

So I would like Excel to behave consistently regardless of the opening mode.
I mean, I've never heard of a database that *transforms* the data depending
on how you open a file... Why does drag'n'drop check my system's settings
and double-click would'nt ?
I wish all our business partners sent us files in a more reliable form but
we can't ask them to change their ways for us.

It is so bad that I recently started writing SQL code to download records
from our mainframe with, instead of the classic '01/09/2006' dates,
something like '=date(2006, 09, 01)' and then apply a macro to make the
formulas effective in each cell under Excel.
Looks so bizarre I know there must be a simple yet effective solution out
there... drag'n'drop does it, maybe something in file types association ?
some DDE wizardry ? I guess I could invest in a VB thing that would
drag'n'drop for me if I double-click...

I know there are workarounds but one day or another someone will make wrong
assumptions on our business because their file was not "opened the right
way". I would rather find a definitive solution before this happens, really.

Any idea ?

 

[notMe]

Thank you everybody for sharing your thoughts.