×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Importing a text file into an Access97 table

Importing a text file into an Access97 table

Importing a text file into an Access97 table

(OP)
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?
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:
 st  ot  dt        name         shift
360016000000tabSMITH, JOHN W.    1
 240008000000tabJONES, ARNOLD C.    2

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?

set echo off
set talk off
close databases && make sure dbf's are closed
use IBMTIME && open IBMTIME.dbf
DELETE ALL && Delete all records
PACK
txt_file = GETFILE('JCN', 'Open Source text file:') && open dialog window select file
fptr = FOPEN(txt_file) && load text file into fptr
IF fptr < 0 && test file to see if already
@ 10,0 say "Can't open or create output file" &&  open, if so cancel program
RETURN
ENDIF

DO WHILE !FEOF(fptr) && repeat routine until EOF
APPEND BLANK && put a blank record in dbf

in_string = FGETS(fptr) && load line of text

    ***************************************************************************
                **  detect and store perm number  **
     the_field = field(1) && perm
replace &the_field with substr(in_string,1,6) && detect text at column 1
&&  width of 6
    ***************************************************************************
                **  detect and store number of entries  **
the_field = field(2)      && no. of entries
replace &the_field with substr(in_string,7,7) && detect text at column 7
&&  width of 7
    ***************************************************************************
                **  detect date add slashes between month day year then store  **
the_field = field(3)      && date
  test_field = substr(in_string,14,6) && detect text at column 14
&&  width of 6
  test_field = STUFF(test_field,3,0,"/") && insert / between month and day
  test_field = STUFF(test_field,6,0,"/20") && insert / between day and year
replace &the_field with test_field && store text info into dbf
   ***************************************************************************
                **  detect and store cost center  **
the_field = field(4)      && cost center
replace &the_field with substr(in_string,20,2) && detect text at column 20
&&  width of 2
    ***************************************************************************
                **  detect and store plant number  **
the_field = field(5)      && plant
replace &the_field with substr(in_string,22,3) && detect text at column 22
&&  width of 3
    ***************************************************************************
                **  detect and store department number  **
the_field = field(6)      && department
replace &the_field with substr(in_string,25,4) && detect text at column 25
&&  width of 4
    ***************************************************************************
                **  detect and store project, jcn and sub job charge  **
y = 1 && start value for column
z = 3 && value of field width
for x = 7 to 9 && field number

  test_field = substr(in_string,29,12) && detect text at column 29
&&  width of 12
  test_field = chrtran(test_field,' ',' ') && replace tab with space
   && remove spaces to left
  test_field = substr(test_field,y,z) && detect field determined by y and z
   the_field = field(x) && proj(7) jcn(8) sub(9) fields
   replace &the_field with test_field && store text info into dbf field

do case
case x = 7 && if x = 7  jcn field
y = 4 && column = 4  29 + 4
z = 5 && width = 5
case x = 8 && if x = 8  sub field
y = 9 && column = 9  29 + 9
z = 3 && width = 3
endcase

next x
    ***************************************************************************
**  straight time, overtime and double time **
y = 1 && start value for column
for x = 11 to 13 && field number

  test_field = substr(in_string,40,20) && detect text at column 40
&&  width of 20
  test_field = ltrim(chrtran(test_field,' ',' ')) && replace tab with space
   && remove spaces to left
  test_field = substr(test_field,y,4) && detect first four characters
  test_field = val(test_field)/100 && detect number from text
  test_field = str(test_field,4,1) && convert number to text
   the_field = field(x) && st(11) ot(12) dt(13)
   replace &the_field with test_field && store text info into dbf field

y = y + 4
next x

    ***************************************************************************
the_field = field(10) && name
  test_field = substr(in_string,55,24) && detect text at column 55
&& width of 24
  test_field = chrtran(test_field,' ',' ') && replace tab with space
  test_field = strtran(test_field,'0','') && replace 0 with blank
  test_field = strtran(test_field,'1','') && replace 1 with blank
  test_field = strtran(test_field,'2','') && replace 2 with blank
  test_field = strtran(test_field,'3','') && replace 3 with blank
  test_field = alltrim(test_field) && remove remaining spaces
replace &the_field with test_field
    ***************************************************************************
     the_field = field(14) && shift
  test_field = substr(in_string,70,20) && detect text at column 70
&& width of 20
  test_field = chrtran(test_field,' ',' ') && replace tab with space
  test_field = chrtran(test_field,'A',' ') && replace A with space
  test_field = chrtran(test_field,'B',' ') && replace B with space
  test_field = chrtran(test_field,'C',' ') && replace C with space
  test_field = chrtran(test_field,'D',' ') && replace D with space
  test_field = chrtran(test_field,'E',' ') && replace E with space
  test_field = chrtran(test_field,'F',' ') && replace F with space
  test_field = chrtran(test_field,'G',' ') && replace G with space
  test_field = chrtran(test_field,'H',' ') && replace H with space
  test_field = chrtran(test_field,'I',' ') && replace I with space
  test_field = chrtran(test_field,'J',' ') && replace J with space
  test_field = chrtran(test_field,'K',' ') && replace K with space
  test_field = chrtran(test_field,'L',' ') && replace L with space
  test_field = chrtran(test_field,'M',' ') && replace M with space
  test_field = chrtran(test_field,'N',' ') && replace N with space
  test_field = chrtran(test_field,'O',' ') && replace O with space
  test_field = chrtran(test_field,'P',' ') && replace P with space
  test_field = chrtran(test_field,'Q',' ') && replace Q with space
  test_field = chrtran(test_field,'R',' ') && replace R with space
  test_field = chrtran(test_field,'S',' ') && replace S with space
  test_field = chrtran(test_field,'T',' ') && replace T with space
  test_field = chrtran(test_field,'U',' ') && replace U with space
  test_field = chrtran(test_field,'V',' ') && replace V with space
  test_field = chrtran(test_field,'W',' ') && replace W with space
  test_field = chrtran(test_field,'X',' ') && replace X with space
  test_field = chrtran(test_field,'Y',' ') && replace Y with space
  test_field = chrtran(test_field,'Z',' ') && replace Z with space
  test_field = chrtran(test_field,'.',' ') && replace . with space
  test_field = chrtran(test_field,',',' ') && replace , with space
      test_field = alltrim(test_field) && remove remaining space
  test_field = val(test_field) && detect number from text
  test_field = str(test_field,1,1) && convert number to text
     replace &the_field with test_field

enddo
=fclose(fptr)
******************************************************************************
*DELETE ALL FOR proj < "052" OR (proj > "052" AND proj < "380") OR proj > "399"  && Delete all records < dept 390
PACK

close all

*return

RE: Importing a text file into an Access97 table

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.

If you'd like further responses you might try posting this to the Access forum.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close