Create a file for EDI 2

[DMSReed]

I have to create a file to be transferred into a customer's system for billing (EDI). A sample of their requirement is below, and I know where I will be pulling the data from (multiple FoxPro 6.0 DOS tables). Any suggestions how this can be done? I will have multiple records as the file will be run over a date range. I can create a form in VFP 5 to allow the user to pull the data based on an invoice date range and a customer number.

There is a total of 310 characters in the row, which many of the 'fields' I will need to stuff with spaces.

If more info is needed from me to help solve this please don't hesitate to ask! I've spent the last 4 hours searching the FAQ's and didn't find anything to help me get started except maybe using FCREATE(),FOPEN() and FWrite() functions. But I couldn't figure out how to create multiple records.

Thanks!

Requestor's sample:
1.) datatype (String) field(ACCOUNT) starting position(1) length(20)
2.) datatype(String) field(RECORD) starting position(21) length(7)
3.) datatype(datetime) field(VPPODT) starting position(28) length(8)
4.) datatype(String) field(VPISHP) starting position(36) length(4)
5.) datatype(String) field(POKEY) starting position(40) length(14)

 

[DMSReed]

Sorry, I meant FoxPro 2.6 for DOS

 

[DSummZZZ]

Take a look at SCAN...ENDSCAN, COPY TO, PADL(), PADR(), STRTOFILE(), TRANSFORM(), DTOS(), DTOC(), TTOC().
That should get you started.

You need to open the table, scan through it - creating the flat record, then write it out.
The above functions should take of all of that.

(FYI - I used to send billing records to EDI years ago!)


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[DMSReed]

Thanks for the response DSummZZZ. I'll be doing the coding using VFP 5 which apparently doesn't support STRTOFILE(). As for the other functions I will consider those for the various field types I'll be pulling. I'm still not clear on how to create the flat file, or should I just create an output table with 1 310 character long field name and then throw all the data into the 1 field? I should be able to use something like REPLACE SUBSTR(fieldname,1,20) with t_account,substr(fieldname,21,7) with t_record), etc?

 

[TheRambler]

Hi,

I don't know about EDI but it seems to me you could try the following:

1) create dbf toExport (account c(20),;
record c(7), ;
vppodt c(8), ;
vpishd c(4), ;
pokey c(14))

2) populate the table with the records you need to export.

3) copy to yourBill.txt sdf

I guess the SDF option of the COPY command can do most of the work for you. Try it.

 

[DSummZZZ]

Sorry, I overlooked the fact you are using VFP 5. You're right. STRTOFILE() came out later.

Anyway, the low-level file functions (FOPEN(), FWRITE(), FPUTS(),...) will do the job too, as will creating a table of character fields and replacing them with what data you need, then copying them SDF like TheRambler mentioned.

But, you can also do something like:

nHandle = FCREATE('textfile.txt')  &&... create & overwrite
IF nHandle < 0
   *... some sort of error stuff here
   RETURN
ENDIF
USE MyTable
SCAN   &&... skip through each record of .dbf
   STORE '' TO cNewRec
   *... if account is numeric:
   cNewRec = cNewRec + PADL(ALLTRIM(STR(ACCOUNT)), 20, '0')
   *... if record is numeric:
   cNewRec = cNewRec + PADL(ALLTRIM(STR(RECORD)),  7, '0')
   .
   .finish creating record
   .
   FPUTS(nHandle, cNewRec)  &&... write record with CR/LF
ENDSCAN
FCLOSE(nHandle)

Hope this helps a little.

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[DMSReed]

Got it going guys! Thanks for the helpful suggestions and multiple solutions. I appreciate it very much.