Correct mask to import date from .txt file?

[Guest]

I have seen some other posts on this subject, but none seem to quite match my
needs:
My .txt file holds dates in this format: yyyy-mm-dd hh:mm
I have tried building several masks but cannot get it right and keep getting
#NUM! errors in my Access table.
Even though I specify exactly what I want when creating the mask, Access
insist on putting slashes in there.
Can somebody please give me the exact correct mask format I need, to handle
this?
Many thanks!
CW

 

[Ken Snell \(MVP\)]

You want to import the date and time value from the text file, and you want
to store it in an ACCESS table, right? Ignore the format for how the data
are stored; ACCESS stores date/time values as a double precision number
(integer is number of days since December 30, 1899, and fraction is time
since midnight for that date).

Then you can set the Format property of the field (design view of the table)
to show the data the way you want.

Or, is the problem that ACCESS is not recognizing your "date/time" value
from the text file as a valid date/time value? I would expect that ACCESS
would correctly read the value as a valid date/time, and no input mask
should be needed for the import process.

 

[Guest]

Ken -
Thanks for your suggestions.
I have tried a further import, following your suggestion to ignore the
format - firstly I ran it without defining the field type in any way at all,
got the usual #NUM! error.
Then I tried again, this time saying in the Import Wizard that the field was
Date/Time, but no more than that. Same result.
So now I have tried it 3 ways - no definition at all, defined as a
date/time, and (originally) with input masks of varying structures.
I get the same result, no matter what.
I do appreciate any further light you can throw on the subject - especially
as it is now coming up for midnight in the UK and I wanted to have this
working for the morning!!!
Thanks again
CW

 

[Ken Snell \(MVP\)]

Hmmm.... can you post some sample records from the .txt file so that we
might try a bit of testing? Just post 8 or 10 records from the .txt file.

 

[Guest]

That's very kind of you Ken - here we are:

2006-05-31 00:00
2007-01-31 01:00
2006-10-31 01:00
2007-06-30 00:00
2007-06-30 00:00
2006-06-30 00:00
2006-12-31 01:00
2007-03-31 00:00
2007-04-30 00:00
2007-01-31 01:00
2006-11-30 01:00
2007-02-28 01:00
2006-05-31 00:00
2006-08-31 00:00

By the way I have absolutely no interest in the time, only the date. So if
there is some way of accepting this data that happens to involve losing the
time element, no problem!
Thanks so much for your help
CW

 

[Ken Snell \(MVP\)]

OK - I have done a bit of testing with your sample data, and have succeeded
in getting ACCESS to correctly import the data as Date/Time data type into a
new table..

This is how I did it:

1) Use File | Get External Data ... | Import menu to start the import
process.
2) Select ".txt;.csv" file type.
3) Select the .csv file that contains the data, and click Import button in
navigation window.
4) Select Advanced... button (bottom left) of import wizard window.
5) Change Date Order setting from "MDY" to "YMD", and leave all other
settings alone. Click OK button. (Note: you can save these settings as an
Import Specification at this point, prior to clicking OK button, if you
wanted to use these settings when doing the import via TransferText [macro
or VBA]. Click the Save As... button to do this.)
6) Continue through the rest of the import process, selecting "import into
new table" option.
7) At final screen of import wizard, click Import.

Access defaults to "MDY" date/time order setting for the import of a text
file. This setting will cause an error when importing your data because your
date/time format is not in month - day - year order.
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Ken, that's really great. Thank you SO much for persevering with it. It's
midnight here now and I shall wake up looking forward to going to the office
tomorrow and getting this to work at last!!!
Thanks a lot
CW

OK - I have done a bit of testing with your sample data, and have succeeded
in getting ACCESS to correctly import the data as Date/Time data type into a
new table..

This is how I did it:

1) Use File | Get External Data ... | Import menu to start the import
process.
2) Select ".txt;.csv" file type.
3) Select the .csv file that contains the data, and click Import button in
navigation window.
4) Select Advanced... button (bottom left) of import wizard window.
5) Change Date Order setting from "MDY" to "YMD", and leave all other
settings alone. Click OK button. (Note: you can save these settings as an
Import Specification at this point, prior to clicking OK button, if you
wanted to use these settings when doing the import via TransferText [macro
or VBA]. Click the Save As... button to do this.)
6) Continue through the rest of the import process, selecting "import into
new table" option.
7) At final screen of import wizard, click Import.

Access defaults to "MDY" date/time order setting for the import of a text
file. This setting will cause an error when importing your data because your
date/time format is not in month - day - year order.
--

Ken Snell
<MS ACCESS MVP>

That's very kind of you Ken - here we are:

2006-05-31 00:00
2007-01-31 01:00
2006-10-31 01:00
2007-06-30 00:00
2007-06-30 00:00
2006-06-30 00:00
2006-12-31 01:00
2007-03-31 00:00
2007-04-30 00:00
2007-01-31 01:00
2006-11-30 01:00
2007-02-28 01:00
2006-05-31 00:00
2006-08-31 00:00

By the way I have absolutely no interest in the time, only the date. So if
there is some way of accepting this data that happens to involve losing
the
time element, no problem!
Thanks so much for your help
CW