Creating a position delimited text file from Fox Pro 8

[hallm]

I'm working on a database where I have to create an export file monthly from some of my queries.

The file format is like:
Pos.01-01 ----- Field1
Pos.02-11 ----- Field2
Pos.12-35 ----- Field3

I'm not wanting anyone to write code for me, but maybe give me some ideas on the functions I would use to create and write the files or if anyone has written a tutorial or has sameple code.

Thanks,

Marion Hall

 

[SGLong]

Marion,

If you've got your information in a table or cursor, with the fields sized the way you want them in the export file try COPY TO myfile.txt TYPE SDF and it will generate what you need.

Steve

 

[hallm]

That's going to work fine.

I'm probably going to use a select query and save the results to a cursor, so that I can store the info in separate tables.

All of my lines have to start with a D in the first position to signify a detail record. What would be the best way to grab that into my select query?

Also, on the last line of my file I have to create a total line. Once I use 'copy to' to create the initial file, what command would I use to append information. Using copy to just tries to replace the file.

Thanks for your help.

Marion

 

[alvechurchdata]

All of my lines have to start with a D in the first position to signify a detail record. What would be the best way to grab that into my select query

Does the "D" have to be a separate field or do you want "D" as the prefix to another field? Use the same "As" technique for both:

Select "D" As Marker, Field1, Field2, etc

or

Select "D" + Field1 As Field1, Field2, etc

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[hallm]

Either one should work fine, it is just to generate the sdf text file.

What about appending data after using the 'copy to' command to create a text file?

Thanks

Marion

 

[rgbean]

Marion,
Here is another approach - older style, but more control than the COPY ... SDF.

lcFileName = ThisForm.r_exportdir+Thisform.txtPrefix.value+"MED.TXT"
l_nHandle = FCREATE(lcFileName, 0)
IF l_nHandle < 0
	*l_cOpenerror = LUFE(FERROR())
	l_cOpenerror = "Please Try Something Else"
 	messagebox("Error Creating MEDCERT file."+;
    CHR(13)+l_cOpenerror, d_nOkbutton+d_nStopsign, "Export Data")
   RETURN
ENDIF && l_nHandle < 0

SELECT SCORES
SET ORDER TO LASTFIRST
lnMyRecCnt = 0
SCAN
   lcBuffer = ""
   * kills leading '0's for candidate ID's
   lcBuffer = lcBuffer + STR(VAL(SCORES.CANDIDATE_ID), 6) &&  1-6
   lcBuffer = lcBuffer + SCORES.ELIGIBILITY_CODE         &&  7
   lcBuffer = lcBuffer + SCORES.EXAM_DAY                 &&  8
   lcBuffer = lcBuffer + SCORES.EXAM_SESSION             &&  9
   lcBuffer = lcBuffer + " "                             && 10
   lcBuffer = lcBuffer + LEFT(SCORES.LASTNAME, 10)       && 11-20
   lcBuffer = lcBuffer + LEFT(SCORES.FIRSTNAME, 10)      && 21-30
   lcBuffer = lcBuffer + " "                             && 31
   lcBuffer = lcBuffer + STR(SCORES.MEAN_RATING, 4,2)    && 32-35
   lcBuffer = lcBuffer + " "                             && 36
   lcBuffer = lcBuffer + STR(SCORES.BELOW_BEFORE, 1)     && 37
   lcBuffer = lcBuffer + " "                             && 38
   lcBuffer = lcBuffer + STR(SCORES.BELOW_AFTER, 1)      && 39
   lcBuffer = lcBuffer + " "                             && 40
   * show pass/fail based on two criteria
   lcBuffer = lcBuffer + IIF(SCORES.PF_CRIT_1 OR SCORES.PF_CRIT_2, "*", " ") && 41
   lcBuffer = TRIM(lcBuffer) && kill 2 trailing spaces if they Fail
   =FPUTS(l_nHandle, lcBuffer)    && Write with CRLF
   lnMyRecCnt = lnMyRecCnt + 1

ENDSCAN &&*

= FCLOSE(l_nHandle)

Rick

 

[hallm]

Both approaches are working great, I've just got to play with it and see which one to use.

Thanks,

Marion