Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 9th, 2007, 06:42 PM
Registered User
 
Join Date: Apr 2005
Location: Boston, MA, USA.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Importing date field in a large txt file

Hello,
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.


Reply With Quote
  #2 (permalink)  
Old July 10th, 2007, 06:43 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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?


mmcdonal
Reply With Quote
  #3 (permalink)  
Old July 10th, 2007, 06:44 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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.



mmcdonal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:00 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.