Wiki Home

Text 2 Cursor


Namespace: VFP
First some terms. I don’t know if the terms were ever used correctly, but they currently are not consistent with the English language. For this page, we will use the following which seems to fit the current dialect and the VFP documentation.

Text file - variety of file formats. About the only thing common is that they all are somewhat human readable. They do not necessarily have CR's at the end of each record, but I don't think any of VFP's Append From options will digest that. If there are no CR's, then you will need to start with something like Str To File(). (Someone can add more on that latter.) So unless otherwise specified, records are terminated with a CR and maybe a LF (VFP doesn’t care about the LF).

FGETS() sees these combinations as end-of-line CR, LF, CR/LF. If FGETS() sees LF/CR it's going to return two lines. See the test code at the bottom of this article. -- ?df
In FPD 2.6 the FGETS() function doesn't recognize LF as the end of the record. -- Sergey Berezniker

Field - a piece of data. (could be in a text file, could be in a VFP cursor)

Delimiter - one of the symbols that surrounds a field.

Separator - the symbol that separates one field from another.

Comma Delimited file - Text file where the fields are separated by commas, and some data types are delimited with double quotes. This is what VFP reads/writes with Copy To Delimited and Append From Delimited. Major problem with this format is double quotes in the data; it basically breaks the file.
Append From Delimited can process files with or w/o quotes around the field. -- Sergey Berezniker
In VFP 7 file can be created w/o quotes using COPY TO ... DELIMITED WITH "" WITH CHARACTER ",". -- Sergey Berezniker

SDF - System Data Format - Fields are a fixed width, therefore records are a fixed length.

Tab Delimited - fields are separated by tabs, delimited with nothing.
For most text file processing, make a cursor that has a similar structure to the text file, and then do an APPEND FROM my.txt TYPE SDF

Using test1.txt from Copy To Delimited as an input file:
"sam",123.45,T,03/24/1966
create cursor test ( cFid c(5), nFid n(8,2), lFid l, dFid d )
append from test1.txt type delimited


Notice that "Sam" is 3 chars long, but the cursor has room for 5. Same issue with the number. This is an advantage of Comma delimited: the size of the fields don't have to match the cursor that you are appending into. (duh, the size of the fields normally varies record to record.)
It could be also a disadventage because you can lose data w/o even knowing it if the length of data in the column exceed the size of the field in the cursor. Sergey Berezniker

For an SDF, you need to make sure the structure corresponds with the field sizes.
sam 123.45T19660324
create cursor test ( cFid c(5), nFid n(10,2), lFid l, dFid d )
append from test1.txt type SDF


Notice that the date is in YYYYMMDD format.
Tricks:

If your SDF text file has slightly different formats, ALTER TABLE is your friend.

sam 123.45T03/24/1966
create table test ( cFid c(5), nFid n(10,2), lFid l, dFid c(10) )
append from test1.txt type SDF
alter table test alter column dFid d


This will read in the "03/24/1966" and then convert the field to a date type. The same will work for other data types too.
If you have a date field in the text file like: 01/01/2001, SDF only looks at 8 chars for a date, not 10. if your table is CREATE CURSOR txtFoo ( dFid1 d ), you get 1/1/1920. the solution:
CREATE CURSOR txtFoo ( dFid1 c(10) )
APPEND FROM my.txt TYPE SDF
ALTER table txtFoo alter column dFid1 d


That will give you a date filed with 1/1/2001 in it.

MS Text ODBC driver
I am guessing the TextOdbc driver is for people that don't have a good data processing language like VFP. I don't know much about it, other than I could never figure out how to get it to work either.

Anyone have any luck with this thingy? -- CFK
erase fgetstest.txt
fh = fcreate( "fgetstest.txt" )
? fh
fwrite( fh, "line1" + chr(10) )
fwrite( fh, "line2" + chr(13) )
fwrite( fh, "line3" + chr(13) + chr(10) )
fwrite( fh, "line4" + chr(10) + chr(13) )
fwrite( fh, "line5" + chr(13) + chr(10) )
fclose( fh )

create cursor fgetstest ( cLine c(50) )
append from fgetstest.txt type sdf

fh = fopen( "fgetstest.txt", 0 )
? fh
do while ( ! feof( fh ) )
   ? fgets( fh, 255 )
enddo
fclose( fh )

Contributors Carl Karsten df Sergey Berezniker
Category VFP Commands Category Code Samples Category Text Files
( Topic last updated: 2002.02.23 09:21:55 PM )