dates corrupted on import (2000 SP3)

Topic

New Reply

Have recently upgraded to Office 2k from 97, and possibly wish I hadn’t.
I have an application which exports as a csv a load of data about people, dates, and money.
To analyse it and do useful things with it, I import it monthly into an ambitious (my style) workbook, replacing the previous month’s data.
This is done with a ‘point and shoot’ macro, slightly tidied and the usual screenupdating = false trick applied.
It seems a ‘feature’ of Excel 2000 that much of the dates part of the data is corrupted in this process (didn’t happen with ’97!) Days and months get inverted.
I’ve tried emptying the columns in the target workbook and formatting them as dd/mm/yy ‘in advance’ but that doesn’t stop bad things happening.
Crazily enough, if the dates in the csv are copied and pasted ‘by hand’ into the target, there’s no problem. But I’d rather fix it (with help from here.)
The attached file shows the unwanted behaviour.
The macro code (don’t laugh!) is in the text file.
The danger of this, for which I won’t thank Mr Gates, is that I could get sent to prison for alleging that someone’s reneging on a loan, when they’re not!

Reply | Quote

Replies

Sorry, here’s the code

Reply | Quote

John, I suspect the translation failure is because the import is assuming the US mm/dd/yy system, when the incoming data is dd/mm/yy. Hence dates which would be invalid in the US, with any day higher than 12, are treated as text, and in the conversions of ‘apparently’ valid dates, the month and day are transposed.. What do you have set as the OS default short date under Control Panel, Regional settings?

Reply | Quote

Yes, have checked this.
‘True Brit’. dd/mm/yyyy as short date format and dddd d mmmm yyyy for the real one.
I notice Andrew is online but in prolonged blink mode.

Reply | Quote

John, according to post 206918 and post 196619, 2000 doesn’t play well with non-US dates. You may have to tweak your code.

Reply | Quote

John, according to post 206918 and post 196619, 2000 doesn’t play well with non-US dates. You may have to tweak your code.

Reply | Quote

John, try this code to convert your dates; test it carefully. You will have to modify it to fit your actual workbook and worksheet names, etc.

Sub mmddyy2ddmmyydates()
Dim rngDates As Range, rngCell As Range
Dim varDate As Variant
Set rngDates = Intersect(ActiveSheet.UsedRange, Columns(“I:J”))
If Not rngDates Is Nothing Then
For Each rngCell In rngDates
If InStr(rngCell.Value, “/”) Then
varDate = Split(rngCell.Value, “/”, , vbTextCompare)
rngCell.Value2 = DateSerial(varDate(2), varDate(1), varDate(0))
End If
Next rngCell
rngDates.NumberFormat = “dd/mm/yy”
End If
End Sub

Reply | Quote

John, try this code to convert your dates; test it carefully. You will have to modify it to fit your actual workbook and worksheet names, etc.

Sub mmddyy2ddmmyydates()
Dim rngDates As Range, rngCell As Range
Dim varDate As Variant
Set rngDates = Intersect(ActiveSheet.UsedRange, Columns(“I:J”))
If Not rngDates Is Nothing Then
For Each rngCell In rngDates
If InStr(rngCell.Value, “/”) Then
varDate = Split(rngCell.Value, “/”, , vbTextCompare)
rngCell.Value2 = DateSerial(varDate(2), varDate(1), varDate(0))
End If
Next rngCell
rngDates.NumberFormat = “dd/mm/yy”
End If
End Sub

Reply | Quote

Yes, have checked this.
‘True Brit’. dd/mm/yyyy as short date format and dddd d mmmm yyyy for the real one.
I notice Andrew is online but in prolonged blink mode.

Reply | Quote

John, I suspect the translation failure is because the import is assuming the US mm/dd/yy system, when the incoming data is dd/mm/yy. Hence dates which would be invalid in the US, with any day higher than 12, are treated as text, and in the conversions of ‘apparently’ valid dates, the month and day are transposed.. What do you have set as the OS default short date under Control Panel, Regional settings?

Reply | Quote

Sorry, here’s the code

Reply | Quote

John

Another take on the same thing. (As a New Zealander I have the same issues you do plus some (Dictionaries))

If I have control of writing the file – its easy. Write the dates with a format that includes alphas e.g. 01Jan04 or any sensible variants.
Excel then cannot screw up as the dates are
a) recognised, and
unambiguous.

Another trick that works is to use Word to pre-alter the text as above before import (Replace all “/01/” with “Jan” …)

HTH

Reply | Quote

John, Andrew,
Thanks very much for your trouble, knowledge and imagination. I’ll try John’s solution first, as that was the first to show (and will also be a better ‘learning experience’: I think I can figure (pun) what it’s doing, the ‘datevalue’ is the giveaway.)
For a really clunky solution, I was thinking about using LEFT, RIGHT and MID formulas to split the dates into three elements (3 new columns for each column of dates), letting neo-colonialist Excel do its stuff on the import, then when safely there, concatenate the d, m and y bits and seeing if that was recognised as a date.
I have got control of the file, so there’s no problem about Andrew’s method if I go that way.
In the meantime, life’s too short, so I’ll go for a prebuilt solution.
Thanks again.

Reply | Quote

John
One more thought – although this is untested. My experience with VBA CSV imports suggests that the following occurs for each date

a) if the date can be interpreted as US it will be – e.g. 01/03/2004 will be stored in Excel’s internal date form as 3rd Jan 2004
if it cannot be interpreted as US it is left as text e.g. 25/03/2004

Your problem is therefore twofold

a) decode the string ones (easy-peasy) , converting to a date format
recode the date ones to their correct month

I think John’s code achieves the first, but not the second – I’d expect an Else on the IF statement.

Reply | Quote

Hi Guys,

The very best way around this problem is to NOT do a csv import.

The steps I’d suggest:

– temporary rename the file to another extension (txt)
– switch on the macro recorder
– open the txt file in Excel and choose the proper format.
– on the final step, make sure to select the column with the dates and choose the proper date format
– stop recording the macro, and check out the WOrkbooks.OpenText method. Adapt it to accept the normal csv filename.

Of course this is not tested, so maybe it still fails, since VBA speaks American…

Reply | Quote

Thanks for all the great ideas, chaps. As there’s no great rush on this (it’s no big deal to do the copy + paste by hand once a month) I’m going to have some fun with it (and maybe learn a bit.)
Am trying turning the dates which are dates (ie not text) into serial number dates and defying xl, or vba, to ‘Americanise’ them, before applying a date format the way round I want.
I figure that what we need is a ‘Copy Special’ a la WYSIWYG just as we have 10 kinds of Paste Special *grin*.
Am curious about the New Zealand dictionaries: have the real ones got all the words upside down?

Reply | Quote

Since this is a once-a-month thing, consider using Data, Get External data, new Text query to import your data into a sheet directly.

Next month, you select the sheet with the data and hit refresh data. You’ll be prompted for a filename.

You might have to rename the file to txt first though.

Reply | Quote

Yes, Jan Karel, thought of this too.
A potential difficulty is that the monthly csv goes to sundry other people too, to refresh the big application which they need to interrogate, too.
That’s why the macro is set up to read from a floppy disc: that’s how it goes to two of the users, the others get it by email and they all know how to detach an email attachment to a floppy.
I’m not too confident about their being able to detach it confidently to a specified folder with a specified path on their hard drives.
Doesn’t the ‘get external data’ route depend on access to the external data every time the analysis file is opened?
I’m going to play with this in a number of ways over the next few days. Meanwhile, a domestic crisis intervenes….
The odd thing is, I don’t ever recall having this difficulty when the analysis tool was in xl 97. So apparently xl VBA comes in flavours?

Reply | Quote

It doesn’t matter if the file is on floppy, once refresh is hit, let them select the file on the FDD.

Reply | Quote

It doesn’t matter if the file is on floppy, once refresh is hit, let them select the file on the FDD.

Reply | Quote

Yes, Jan Karel, thought of this too.
A potential difficulty is that the monthly csv goes to sundry other people too, to refresh the big application which they need to interrogate, too.
That’s why the macro is set up to read from a floppy disc: that’s how it goes to two of the users, the others get it by email and they all know how to detach an email attachment to a floppy.
I’m not too confident about their being able to detach it confidently to a specified folder with a specified path on their hard drives.
Doesn’t the ‘get external data’ route depend on access to the external data every time the analysis file is opened?
I’m going to play with this in a number of ways over the next few days. Meanwhile, a domestic crisis intervenes….
The odd thing is, I don’t ever recall having this difficulty when the analysis tool was in xl 97. So apparently xl VBA comes in flavours?

Reply | Quote

Since this is a once-a-month thing, consider using Data, Get External data, new Text query to import your data into a sheet directly.

Next month, you select the sheet with the data and hit refresh data. You’ll be prompted for a filename.

You might have to rename the file to txt first though.

Reply | Quote

Thanks for all the great ideas, chaps. As there’s no great rush on this (it’s no big deal to do the copy + paste by hand once a month) I’m going to have some fun with it (and maybe learn a bit.)
Am trying turning the dates which are dates (ie not text) into serial number dates and defying xl, or vba, to ‘Americanise’ them, before applying a date format the way round I want.
I figure that what we need is a ‘Copy Special’ a la WYSIWYG just as we have 10 kinds of Paste Special *grin*.
Am curious about the New Zealand dictionaries: have the real ones got all the words upside down?

Reply | Quote

John
One more thought – although this is untested. My experience with VBA CSV imports suggests that the following occurs for each date

a) if the date can be interpreted as US it will be – e.g. 01/03/2004 will be stored in Excel’s internal date form as 3rd Jan 2004
if it cannot be interpreted as US it is left as text e.g. 25/03/2004

Your problem is therefore twofold

a) decode the string ones (easy-peasy) , converting to a date format
recode the date ones to their correct month

I think John’s code achieves the first, but not the second – I’d expect an Else on the IF statement.

Reply | Quote

John, Andrew,
Thanks very much for your trouble, knowledge and imagination. I’ll try John’s solution first, as that was the first to show (and will also be a better ‘learning experience’: I think I can figure (pun) what it’s doing, the ‘datevalue’ is the giveaway.)
For a really clunky solution, I was thinking about using LEFT, RIGHT and MID formulas to split the dates into three elements (3 new columns for each column of dates), letting neo-colonialist Excel do its stuff on the import, then when safely there, concatenate the d, m and y bits and seeing if that was recognised as a date.
I have got control of the file, so there’s no problem about Andrew’s method if I go that way.
In the meantime, life’s too short, so I’ll go for a prebuilt solution.
Thanks again.

Reply | Quote

John

Another take on the same thing. (As a New Zealander I have the same issues you do plus some (Dictionaries))

If I have control of writing the file – its easy. Write the dates with a format that includes alphas e.g. 01Jan04 or any sensible variants.
Excel then cannot screw up as the dates are
a) recognised, and
unambiguous.

Another trick that works is to use Word to pre-alter the text as above before import (Replace all “/01/” with “Jan” …)

HTH

Reply | Quote