import text file to table with dates

[bigeyespanda]

Hi!
I have a problem with importing text to the table. The following is the text file:
2002/10/04,12:58:38,12355,3,1,2,2
2002/05/04,13:01:06,12355,4,1,2,2
2004/10/04,13:01:06,12355,4,-1,2,2
1998/10/04,13:01:06,12355,5,-1,2,2

When I import this file into the table, all of the are correctly insert to the table fields, EXCEPT THE DATE!!! The date in the database appears to be 1899/12/30 for all of them.... any idea how to fix it??

Thanks!!!

 

[namliam]

Try changing the date format in the advanced option to YMD...

Regards

 

[Jacob Mathai]

I can show a workaround (will not be the best). Pls use it if there is no better way.

I will import this date (say, column date1) as text field.
Then I will create another column, date2 (text 10 char) on the same table.
Using a query I will update date2 to 10/04/2002 from date1 (2002/10/04).
Then I create a third column, date3 (date/time) and using another query,
update date3 =cdate(date2).

I will drop date1 and date2 from the table.

 

[bigeyespanda]

Hi Mailman,

I've tried this when I do the importing.... but it doesn't really work....

bigeyespanda

 

[bigeyespanda]

Thanks for all your reply.

But what I want to do is to create a button, and then it will import this text file into the table.... any other solutions?

Thanks very much!!!!!

bigeyespanda

 

[bjackson]

If you import the field as a text field and not a date field you will get exactly the same as the txt file
2002/10/04,12:58:38,12355,3,1,2,2

Do you want to convert ' 2002/10/04' to another date format ?If so What format ?

Regards
bjackson

 

[ecniv]

Using an update query to go from the first to last record...
Or if you use code to import, then convert as you grab and put in the table.

cdate(right(field,2) & mid(field,5,4) & left(field,4))

Use cdate if you want a date vaule or forget it if you want the string to convert later...


Vince

 

[bigeyespanda]

Thanks for the help, maybe I should clarify what I want to do.

I am doing a database program to control inventory. I will get the text file from a bar code reader, and what I want to do is to just click a button in the database program, and then it will import the text file into the table.... do I really have to make the field as a text field? Can I do it with VbScript?? If so, how can I do it?? I am still new with VB.... Also, if the date is changed to a text field, will it be sorted out probably?

regards

 

[bjackson]

Import as text then run an update query

You will get dates that can be sorted any way you wish
what Vince has sugested is a good answer

Regards
Bjackson