Imorting comma separated text into a record...

[Legacy 98055]

Hi.
Here is an example of one of many textfiles that I need to import. No problem except I keep running into problems getting the date from a string format in the textfile into a date/time format in the record...

<pre>
24178,"JMAR ","7337 ","71599800 "," ","DROPLOT ","FF ","LOT 137 ","DOOR 64 ","55 ","AE4316","JBH077","1/1/02 5:58:29 PM","1/1/02 7:19:14 PM","1/1/02 7:32:08 PM",0,12,3
24179,"BWYW ","266555 "," "," ","FF ","DROPLOT ","DOOR 45 ","LOT 137 ","02 ","VNT20 ","JBH027","1/1/02 7:31:00 PM","1/1/02 7:42:56 PM","1/1/02 7:43:01 PM",0,2,12
24180,"HJBX ","204855 "," "," ","FF ","DROPLOT ","DOOR 77 ","LOT 158 ","02 ","VNT017","JBH027","1/1/02 7:35:58 PM","1/1/02 7:52:55 PM","1/1/02 7:52:57 PM",0,4,12
24181,"RVTI ","H6760 "," "," ","FF ","DROPLOT ","DOOR 44 ","LOT 178 ","02 ","VNT16 ","JBH027","1/1/02 7:51:06 PM","1/1/02 8:11:47 PM","1/1/02 8:11:49 PM",0,2,12
24182,"SNCS ","A426867 ","44712500 "," ","DROPLOT ","FF ","LOT 084 ","DOOR 77 ","02 ","AE4316","JBH027","1/1/02 7:57:32 PM","1/1/02 8:01:17 PM","1/1/02 8:03:08 PM",0,12,4
24183,"SWFT ","T63594 ","59625200 "," ","DROPLOT ","FF ","LOT 163 ","DOOR 14 ","55 ","AE4316","JBH077","1/1/02 7:57:55 PM","1/1/02 8:01:01 PM","1/1/02 8:08:29 PM",0,12,1</pre>

Thanks for any help...

The over all project reads about 400 or so of these text files into one record. All ""'s are imported as text except for the 3 date columns. All else is LongInteger. Am very ne to Access...

 

[Ifonly]

I'm using office 97 but it will probably be the same. Sorry if this is dumbed down too much but here goes.

File
Get external data
import
choose txt as the file type and find your file
Click on advanced

Here you get to describe each of your fields. You can see all the possible variables. Most of them are obvious but in your case it is important that you know what date/time variables you are using and make these settings accordingly. Then you make a list of your fields. Field1 may be text, field2 and integer etc. When you get to the date fields make sure they are listed as a date. The specification ("specs") can be saved and used over and over.

Hope this helps.

 

[Legacy 98055]

Thanks IfOnly.
That is precisely what I have done. Several times in fact... I always get an error record listing the errors. "Type conversion error". The errors are limited to my three fields which I chose "Date/Time" as the datatype. I was able to import the entire range of my files and then change the datatype in design mode. This did work and the problem is effectively solved, but, for my own edification, seeing that I want to familiarize myself with Access, what might I have done wrong?

Thanks again,
Tom

 

[Ifonly]

I went and tried it and it didn't work straight away......try clicking "leading zeros in dates" it worked for me. (find the leading zero in 1/1/02)

 

[Legacy 98055]

Thanks again IfOnly.
I tried the "Leading Zero" option without success... I edited the text file removing the "AM"s and "PM"s and was able to import successfully. This is puzzling that Access might be tripped up by this? But, I did learn and I appreciate all the help.

Tom