Importing a text file into an Access97 table 1

[sealed]

I am importing a text file that contains timekeeping job charges, hours, names and shifts. The first seven fields, using the TransferText routine, came in find. The remaining fields contain tabs and spaces that I want to remove. How do I do this?<br>Also I have one field that contains the hours, name and shift and I want to break them up into individual fields but the information came into the database unevenly, for example:<br>&nbsp;st&nbsp;&nbsp;ot&nbsp;&nbsp;dt&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;shift<br>360016000000tabSMITH, JOHN W.&nbsp;&nbsp;&nbsp;&nbsp;1<br>&nbsp;240008000000tabJONES, ARNOLD C.&nbsp;&nbsp;&nbsp;&nbsp;2<br><br>I have a FoxPro 2.5 program that works great for bringing in the data but we are going away from FoxPro. Here is the entire routine: Is there a way of modifying this program to work in Access97?<br><br>set echo off<br>set talk off<br>close databases && make sure dbf's are closed<br>use IBMTIME && open IBMTIME.dbf<br>DELETE ALL && Delete all records<br>PACK<br>txt_file = GETFILE('JCN', 'Open Source text file:') && open dialog window select file<br>fptr = FOPEN(txt_file) && load text file into fptr<br>IF fptr &lt; 0 && test file to see if already<br> @ 10,0 say &quot;Can't open or create output file&quot; &&&nbsp;&nbsp;open, if so cancel program<br> RETURN<br>ENDIF<br><br>DO WHILE !FEOF(fptr) && repeat routine until EOF<br> APPEND BLANK && put a blank record in dbf<br> <br> in_string = FGETS(fptr) && load line of text<br> <br>&nbsp;&nbsp;&nbsp;&nbsp;***************************************************************************<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**&nbsp;&nbsp;detect and store perm number&nbsp;&nbsp;**<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;the_field = field(1) && perm<br> replace &the_field with substr(in_string,1,6) && detect text at column 1<br> &&&nbsp;&nbsp;width of 6<br>&nbsp;&nbsp;&nbsp;&nbsp;***************************************************************************<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**&nbsp;&nbsp;detect and store number of entries&nbsp;&nbsp;**<br> the_field = field(2)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; && no. of entries<br> replace &the_field with substr(in_string,7,7) && detect text at column 7<br> &&&nbsp;&nbsp;width of 7<br>&nbsp;&nbsp;&nbsp;&nbsp;***************************************************************************<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**&nbsp;&nbsp;detect date add slashes between month day year then store&nbsp;&nbsp;**<br> the_field = field(3)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; && date<br> &nbsp;&nbsp;test_field = substr(in_string,14,6) && detect text at column 14<br> &&&nbsp;&nbsp;width of 6<br> &nbsp;&nbsp;test_field = STUFF(test_field,3,0,&quot;/&quot && insert / between month and day<br> &nbsp;&nbsp;test_field = STUFF(test_field,6,0,&quot;/20&quot && insert / between day and year<br> replace &the_field with test_field && store text info into dbf<br>&nbsp;&nbsp;&nbsp;*************************************************************************** <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**&nbsp;&nbsp;detect and store cost center&nbsp;&nbsp;**<br> the_field = field(4)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; && cost center<br> replace &the_field with substr(in_string,20,2) && detect text at column 20<br> &&&nbsp;&nbsp;width of 2<br>&nbsp;&nbsp;&nbsp;&nbsp;***************************************************************************<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**&nbsp;&nbsp;detect and store plant number&nbsp;&nbsp;**<br> the_field = field(5)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; && plant<br> replace &the_field with substr(in_string,22,3) && detect text at column 22<br> &&&nbsp;&nbsp;width of 3<br>&nbsp;&nbsp;&nbsp;&nbsp;*************************************************************************** <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**&nbsp;&nbsp;detect and store department number&nbsp;&nbsp;**<br> the_field = field(6)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; && department<br> replace &the_field with substr(in_string,25,4) && detect text at column 25<br> &&&nbsp;&nbsp;width of 4<br>&nbsp;&nbsp;&nbsp;&nbsp;***************************************************************************<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**&nbsp;&nbsp;detect and store project, jcn and sub job charge&nbsp;&nbsp;**<br> y = 1 && start value for column<br> z = 3 && value of field width<br> for x = 7 to 9 && field number<br> <br> &nbsp;&nbsp;test_field = substr(in_string,29,12) && detect text at column 29<br> &&&nbsp;&nbsp;width of 12<br> &nbsp;&nbsp;test_field = chrtran(test_field,' ',' ') && replace tab with space<br> &nbsp;&nbsp; && remove spaces to left<br> &nbsp;&nbsp;test_field = substr(test_field,y,z) && detect field determined by y and z<br> &nbsp;&nbsp;&nbsp;the_field = field(x) && proj(7) jcn(8) sub(9) fields<br> &nbsp;&nbsp;&nbsp;replace &the_field with test_field && store text info into dbf field<br><br> do case<br> case x = 7 && if x = 7&nbsp;&nbsp;jcn field<br> y = 4 && column = 4&nbsp;&nbsp;29 + 4<br> z = 5 && width = 5<br> case x = 8 && if x = 8&nbsp;&nbsp;sub field<br> y = 9 && column = 9&nbsp;&nbsp;29 + 9<br> z = 3 && width = 3<br> endcase<br> <br> next x<br>&nbsp;&nbsp;&nbsp;&nbsp;*************************************************************************** <br> **&nbsp;&nbsp;straight time, overtime and double time **<br> y = 1 && start value for column<br> for x = 11 to 13 && field number<br> <br> &nbsp;&nbsp;test_field = substr(in_string,40,20) && detect text at column 40<br> &&&nbsp;&nbsp;width of 20<br> &nbsp;&nbsp;test_field = ltrim(chrtran(test_field,' ',' ')) && replace tab with space<br> &nbsp;&nbsp; && remove spaces to left<br> &nbsp;&nbsp;test_field = substr(test_field,y,4) && detect first four characters<br> &nbsp;&nbsp;test_field = val(test_field)/100 && detect number from text<br> &nbsp;&nbsp;test_field = str(test_field,4,1) && convert number to text<br> &nbsp;&nbsp;&nbsp;the_field = field(x) && st(11) ot(12) dt(13)<br> &nbsp;&nbsp;&nbsp;replace &the_field with test_field && store text info into dbf field<br><br> y = y + 4<br> next x<br><br>&nbsp;&nbsp;&nbsp;&nbsp;*************************************************************************** <br> the_field = field(10) && name<br> &nbsp;&nbsp;test_field = substr(in_string,55,24) && detect text at column 55<br> && width of 24<br> &nbsp;&nbsp;test_field = chrtran(test_field,' ',' ') && replace tab with space<br> &nbsp;&nbsp;test_field = strtran(test_field,'0','') && replace 0 with blank<br> &nbsp;&nbsp;test_field = strtran(test_field,'1','') && replace 1 with blank<br> &nbsp;&nbsp;test_field = strtran(test_field,'2','') && replace 2 with blank<br> &nbsp;&nbsp;test_field = strtran(test_field,'3','') && replace 3 with blank<br> &nbsp;&nbsp;test_field = alltrim(test_field) && remove remaining spaces<br> replace &the_field with test_field<br>&nbsp;&nbsp;&nbsp;&nbsp;***************************************************************************<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;the_field = field(14) && shift<br> &nbsp;&nbsp;test_field = substr(in_string,70,20) && detect text at column 70<br> && width of 20<br> &nbsp;&nbsp;test_field = chrtran(test_field,' ',' ') && replace tab with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'A',' ') && replace A with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'B',' ') && replace B with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'C',' ') && replace C with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'D',' ') && replace D with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'E',' ') && replace E with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'F',' ') && replace F with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'G',' ') && replace G with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'H',' ') && replace H with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'I',' ') && replace I with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'J',' ') && replace J with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'K',' ') && replace K with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'L',' ') && replace L with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'M',' ') && replace M with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'N',' ') && replace N with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'O',' ') && replace O with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'P',' ') && replace P with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'Q',' ') && replace Q with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'R',' ') && replace R with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'S',' ') && replace S with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'T',' ') && replace T with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'U',' ') && replace U with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'V',' ') && replace V with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'W',' ') && replace W with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'X',' ') && replace X with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'Y',' ') && replace Y with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'Z',' ') && replace Z with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,'.',' ') && replace . with space<br> &nbsp;&nbsp;test_field = chrtran(test_field,',',' ') && replace , with space<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;test_field = alltrim(test_field) && remove remaining space<br> &nbsp;&nbsp;test_field = val(test_field) && detect number from text<br> &nbsp;&nbsp;test_field = str(test_field,1,1) && convert number to text<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;replace &the_field with test_field<br><br>enddo<br>=fclose(fptr)<br>******************************************************************************<br>*DELETE ALL FOR proj &lt; &quot;052&quot; OR (proj &gt; &quot;052&quot; AND proj &lt; &quot;380&quot OR proj &gt; &quot;399&quot;&nbsp;&nbsp;&& Delete all records &lt; dept 390<br>PACK<br><br>close all<br><br>*return<br>

 

[elizabeth]

Re: the field that is not atomic, from your example it looks like you could read this string backwards, and/or use the left, right, and mid functions to split out the substrings. For example, shift would be Right(Fieldname,1) and you can use the instr function to find the comma and the blank before it to split out the name.<br><br>If you'd like further responses you might try posting this to the Access forum.