Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 9th, 2007, 06:42 PM
Registered User
Join Date: Apr 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Importing date field in a large txt file

I typically import a text file of 3.5 million records using a pre-existing import spec file. However, I have several pending project for which I will have to query on date criteria. Currently data fields import as text.

If I try to change the datatype of the field, all the data is erased. If I try to change the import spec for this field from text to date, all the data is erased. I am very discouraged, stressed out, and devoid of ideas.

1) Will I be able to set criteria and return dates between two value if this field remains text? I doubt it.
2) Why can't I change the datatype from text to date in an existing field?
3) When I change the import spec (only change is the single field datatype from text to date) why isn't the data imported?
4) the text file looks like ...,"19520701","next field data", and yes, even though the text file is cvs the existing import spec is creates field deliminated format, and yes, I created a short file and deleted the " from the txt file with the same result.

Any insight, wisdom, suggestions, folk sayings, etc. would be most gratefully received.

Old July 10th, 2007, 06:43 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

There are a couple possible ways around this. You can either use a Scripting.FileSystemObject to import the file a line at a time, and use CDate() on your date fields, or you can create additional fields in the table once the data is imported, and then do an update query from the table to itself using CDate() or some other formula to move the date data to the date field. I used this syntax in an Update Query to move your OldDate text field to a NewDate date/time field in a table called "Dates":

UPDATE Dates SET Dates.NewDate = Mid([OldDate],5,2) & "/" & Right([OldDate],2) & "/" & Left([OldDate],4);

This returned this:

OldDate NewDate
19520701 7/1/1952

Does that help?

Old July 10th, 2007, 06:44 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Actually, you would have to use the same regular expressions if you were using the Scritping.FileSystemObject since CDate() doesn't work in this format.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error - Loading date field from flat file to sql carumuga SQL Server 2005 0 August 12th, 2008 09:55 AM
Importing Fixed Width .txt Document and Then Some! Diretemus Access VBA 8 March 26th, 2008 07:27 AM
Date Conversion from txt field darrenb Access VBA 2 April 6th, 2007 07:04 AM
Importing txt file sorting and graph Torbma Beginning VB 6 0 May 17th, 2006 06:07 AM
DATE within txt box alannoble Excel VBA 1 December 22nd, 2004 12:36 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.