converting multi-line ascii text to dbf

[joell165]

I need to convert an ascii file of multiple lines per record to a .dbf file. The problem I have is when I do the conversion information that should be all in one record becomes multiple records. Each record in the ascii file looks similar to this:

Smith, John 3462 (212)123-4567 456-67-5673
Male 23 Main Street Sep 2,3,4,5,6,9,15,21,22,24
Oct 1,3,6,24
Nov 2,3,4,9,17,

As you can see, not only is the information on multiple lines (between 3 and 7)data overlaps from line to line.

I am at a total loss. By the way, if there is a solution to this using Access, that would be OK.

Thanks.

Joel

 

[RRiley]

This is how I generally handle this situation. First of all the ASCII file must be in a regular repeating text that is always the same. Since we are going to rely on the repeating format we can set up a For i = 1 to x loop structure.

#Define t "C:\textfiles\ASCII.TXT" //define the file to open
f = new File()
if f.exists(t)
f.open(t, "R&quot
else
MsgBox("File does not exist", "File Error, 16)
Return
endif

d = New Database()//If using dBase 2000 if not Open Database
d.DatabaseName = "Employee"
d.active = True

e = New Query()
e.database = d
e.sql = "Select * from EMPLOYEE"
e.active = True
// For this example the employee table only has four fields
// Line1,line2,line3,line4
f.seek(0, 0)// Place the cursor at the top of File

Do while not f.eof() //begin at top go to end of File
e.rowset.beginappend()
i = 1
rEnd = 4

for i = 1 to rEnd
nRecord = f.gets(10000, Chr(0x0A))//Define end of record
// This is a hard linefeed return
e.rowset.fields.value - nRecord
endfor
e.rowset.save()
enddo

e.active = False
d.active = False

Return

To divide each line up into multiple fields you would have to write a routine to substring the nRecord variable. The substring would have to be intuitive enough to account for name lengths, variable ssno number lengths and so on. This can be done by looking for Spaces or CHR(32) in a string to denote where the different fields should start and stop.

If you need more help or coding for a different version of dbase send me a note.

Thanks
RRiley