Importing Dates from Excel to Access

[rosenpop]

I have a large file with over 150 columns, 30 of which are date fields and not every cell is populated. The date format in my Excel fields are dd/mm/yyy. When I import into Access some fields are imported as date type and others as text - which I understand has to do with whether or not the first row is populated.

Access flips the date to be mm/dd/yyyy, which doesn't appear to be a problem with the fields that import as a date type (at least from a random check)

To convert the 20 or so date fields that come in as text, I have gone into the stucture and changed the type to date. Again, Access flips the date to be mm/dd/yyyy but that's ok, I can work with that as long as the integrity of the data is ok.

Since I do this weekly and it is very time consuming, I copied the structure to a shell table and adjusted the data types I need to be dates, and I now import into that shell - and I think it is working properly.

So my question - do I need to be concerned with Access flipping my dates and reformatting the data type from txt to date. I have thousands of records and certainly don't have the time to confirm all of the data - which can't be wrong! I do get an error saying that it is deleting data - but I believe that is referring to the cells where the date information is not populated.

Thanks
Rosemary

 

[gemma-the-husky]

not sure what a canadian date looks like

by default access tries to use us dates mm/dd/yyyy

in uk we use dd/mm/yyyy

for some operations you need to be careful as ambiguous dates will/may be used wrongly

iw 19/3/09 will ALWAYS be 19th March but 12/3/09 might be March 12th, but equally could be Dec 3rd. - Hence you need ot be aware of this

----------
the import errors will relate to cells that do not follow the correct date formatting rules, but also watch for this US dates thing if it may be an issue for you.

----------
if this is important i recommend saving the excel sheet as a csv, then importing the csv - you actually get more control over the csv.

 

[rosenpop]

I thought of that since the file originally comes to me in a .csv format, however, every time I have tried to import it into Access - using tab, comma or space delimited the data doesn't line up. Not sure why?

 

[Brianwarnock]

A crude, but if I remember correctly, successful approach I adopted when I had a similar problem was to establish a spreadsheet with all the columns and 1 row with all the correctly formatted data. I then used to insert this row as row 2, the first row was headings, into the required spreadsheet, import and then delete that record from the db, of course if this is an ongoing thing you probably want something with less manual intervention.

brian

 

[rosenpop]

I like it! I'll try it. Sounds like a reasonable fix in the short term.

Rosemary

 

[rosenpop]

Update - It did not work with one dummy row. I remember reading somewhere that Access looks at the first 14 rows to determine data type, so I just copied the same row and it worked with 14 dummy rows.

My question still stands - can I reasonably assume that Access brought all of the dates in correctly and didn't mess with them. The date I used in the dummy row was 31/12/2009 (which is the format of the spreadsheet dd/mm/yyyy). I know that access changes that to mm/dd/yyyy - so as long as it changes every record and every cell the exact same why then this will work - hopefully!

I have experienced what Dave referred to. I have a calculated date difference field and for some records Access interpreted the date as mm/dd/yyyy for some records and in others as dd/mm/yyyy. Is this still a concern for me?

Rosemary

 

[Brianwarnock]

Ok so I rememered the technique but not the detail, it was a long time ago.

I have just tried an import of dates and it did not reformat any of mine, what are your local settings, are they dd/mm/yyyy ?
I would worry that some are being changed can you not check any samples?
Perhaps the safe option is to change the formats to dd/mmm/yyyy or similar but what a pain.

Brian

 

[rosenpop]

My computer's regional settings are mm/dd/yyyy.
My .xls data is formatted as dd/mm/yyyy

I believe access uses the regional settings as a default. I called our IT department last week to change this (I don't have administrator rights!) but they have advised some of the other programs I use require these regional settings, so they can not be changed.

I did run a check on several records that were messed up before and they are ok using the dummy row approach. I'm feeling more confident with this approach rather than converting the text field to a date field. I also find it quicker to copy and paste dummy rows rather than scrolling through 180 fields in design view to find the 20 or so text/date types that I need to change.

Rosemary