CSV Date import (Office 2000)

Topic

New Reply

I have a strange problem that I believe is due to incorrect Regional Settings at install. The problem:

When a user imports a CSV file, the system tries to import dates in US format (mm/dd/yy). Being British we use dates in the correct format as dd/mm/yy. So when the dates are imported all dates with the day from 1 to 12 are recognised as dates (but with days and months transposed) and those with days 13 to 31 are left as General format. This problem only manifests on one PC. The Regional settings on the PC in question are set to United Kingdom, but I know that they were at Default Windows (that is US) when Office was installed. Uninstalling and reinstalling seems not to work because Office seems to remember the original setting.

I cannot find any information on the internet on changing the Office regional settings other than simple instructions that state that changing the regional settings in Control Panel will change the Office Regional setting. I think I will need a Reg Hack to fix this. Does anyone know where I need to look in the Registry to find Office region settings?

Reply | Quote

Replies

Try changing the extension from .csv to .txt. You’ll have much more control over how data are imported. There have been threads about .csv files and UK-format dates before, and we didn’t find a solution that worked consistently for everyone.

Reply | Quote

Reggie

This seems to be the bane of my life at the moment as I have been doing a major extract project from legacy systems to SQL servers. Hans is correct for the date issue as sometimes putting date formats ina csv file can cause problems especially if sending to SQL servers. I had a problem that the SQL server had a field Date/Time that allowed null values, it bulked. Changing the csv to txt allows excel to see it as text string. when importing the data into excel, Excel sees the string and makes the correct assumption ( from set up) that the string is a date and converts to D41 format. I found this method was the best way, as for me, SQL allowed the import.

Also as Excel only allows 65536 rows of data txt allowed me more flexibility

Reply | Quote

Thank you for your replies. At least a consistant answer ).

This only re-emphasises my favouring of writing dates in 28-Jul-05 style, where there is no confusion. Though I’m actually getting to like SQLs standard yyyy-mm-dd hh:mm:ss as at least its a logical progression from large to small and appears to be consistent across platforms.

I don’t think my user will be so pleased. I’m sure to get the “but it works on so and sos PC”. Hey ho! the fun of life as an IT manager.
Thanks again – Reggie

Reply | Quote

Just a note: SQL (like VBA) is very US-centric, it requires mm/dd/yy or mm/dd/yyyy format.

Reply | Quote

The SQL databases I use are MySQL, Access and a Borland DB2. In my experience if the driver won’t translate a normalised date (e.g. 1-Jul-2003) then best bet is to use yyyy-mm-dd hh:mm:ss format. The SQL books I have indicate this is the standard format for most SQL databases. I’ll bow to your greater experience with MS SQL. In VBA and VBScript I tend to use month(), day() and year() a lot so I know exactly what I’m getting out.

0nly goes to further prove what a pain dates are.

Thanks again for the feedback

Reply | Quote