Formatting 900,000 rows

[StuJol]

This is really fustrating as i managed to do it last week, unfortunalety i
didnt write down a procedure and cannot remember how i did it.

i have a .txt file with approx 900,000 records exported from a 3rd party
system. i can import this into an access 2003 table without any errors. what
i need to do is for the date/time field, change the format from text to date.
unable to do this in access due to memory messages. i know i used excel,
think i exporting into excel, changed the colume format, then inserted back
into access but when i tried doing it today excel cant import more then
65,000 records.

i know its possible, just cant remember how.

can anyone help please???

 

[Douglas J. Steele]

Import your table.

Add a new date field to the table.

Write an Update query to populate the new date field from the older text one
using the CDate function.

 

[Jeff Boyce]

Are you referring to the storage, or the display? True date/time values are
stored in Access as, I believe, "double" values.

But you can choose to have those displayed in whatever format suits your
needs.

Are you trying to convert them to a different storage value, or to display
them in a particular format?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Daryl S]

Don't know what you did last time, but you could open the text file in Excel,
then correct the date problem there. Save as an Excel file, and import into
Access from Excel.

Otherwise, to adjust what you have, add the date field to the table in
Access first.
You can link an Excel file from within Access, so if you just want to update
the one field, you can run an update query with the 'Excel' table joined to
the 'Access' table on whatever uniquely defines each record.

Or if the text-date column in Access is nicely formatted, you can run an
update query using DateValue(textfield) into the date field, all within
Access.

 

[John W. Vinson]

This is really fustrating as i managed to do it last week, unfortunalety i
didnt write down a procedure and cannot remember how i did it.

i have a .txt file with approx 900,000 records exported from a 3rd party
system. i can import this into an access 2003 table without any errors. what
i need to do is for the date/time field, change the format from text to date.
unable to do this in access due to memory messages. i know i used excel,
think i exporting into excel, changed the colume format, then inserted back
into access but when i tried doing it today excel cant import more then
65,000 records.

i know its possible, just cant remember how.

can anyone help please???

If this is a recurring problem, I'd suggest creating an empty local table with
a Date/Time field (and all the other fields appropriately datatyped and
sized). Rather than importing the text file, Link to it (file... get external
data... link) and run an Append query into the local table.

 

[StuJol]

Douglas, never used an update query before, can you please give me an example
of such a sql script?

 

[Daryl S]

StuJol -
It will look like this, but use your actual table and field names:

UPDATE [yourTableName]
SET [yourNewDateFieldname] = CDate([yourTextDateFieldname]);