Steve,
Tks for the detailed reply.
One of the features of this particular part of the application is that the end-use can choose which fields and in which order they will appear in the ASCII file. They can also insert literals such as 'HEADER','DETAIL', etc to identify to the records to the accounting app. Also, for non character fields, they can specify that they want the fields to be converted to a character field.
In other words, I am trying to write a generic type of extract and one that meets the needs of this specific client.
As a result, I build a SQL command to extract the data that is required, in the order necessary. As I am building this command, I use 'as' commands to convert numerics to characters, etc.
At this point in the code, I can use a 'copy to delimited' and the resulting ASCII file should be formatted exactly to the accounting apps requirements.
One of the other alternatives that we discussed was extracting the records and then building a string (in code) with the fields in the correct order with the required delimiter added by the program. We thought that for larger volumes the method that we chose was more efficient.
>Allan,
>
>I think it would be more efficient to manually create a delimited string for each PO record, and write that string to the export file (either with low level, or with strtofile() using the additive parameter).
>
>Your current method creates little files, then VFP has to open them back up to read them. I would expect there to be a performance hit there.
>
>Does the accounting package have any flexibility in how the file is delimited? Does it require a CSV file or can it be delimited with a character?
>
>The way you are going will work, but instead of busting out the memo into multiple records I would simply create a string that has a line for each portion of the note (with a quote at beginning and end of line).
>
>An issue to consider on notes field is how to split when there are embedded CR/LF within the memo. If each individual line is less than 50 characters, you may be able to simply break it along the existing line barriers. If this is acceptable, I would use alines() to create an array, create a string while stepping through the array, and break any array elements longer than 50 characters into multiple segments.
>
>Also, you probably should alltrim() each field (not the notes, of course) as it goes into the output string...
>
>
>* create a character delimited file...a comma/quote delimited file will require
>* slightly more effort
>* this is untested, hypothetical code to write one po to file
>* fields are hardcoded, non-string fields need to be converted to strings with transform()
>* of cource there a lot more fields than this
>lcSS = "|"
>lcCRLF = chr(13)+chr(10)
>select header
>lcHeader = header.poNum + lcSS + header.vendorID + lcSS
>lcHeader = lcHeader + transform(header.poDate) + lcCRLF
>
>* create lines string
>* you could do a seek in detail, then use scan while...I prefer a SELECT
>select * from detail where poNum = header.poNum into cursor tempLines
>* select tempLines
>lcLines = ""
>scan
> lcLines = lcLines + tempLines.PoNum + lcSS + tempLines.lineNum + lcCRLF
>endscan
>
>lcNotes = splitNotes(header.poNum)
>
>
>Fwrite(lnhandle,lcHeader + lcLines + lcNotes)
>
>
>
>>Steve,
>>
>>I think it would be a good idea for me to explain the scenario in real terms.
>>
>>Let's say that the app VFP is a purchase order system. There are headers, details and the header might have a free form note (MEMO).
>>When the data is imported into the existing accounting package (written in another language with no source) it must be in ASCII format.
>>The accounting app wants the data as follows:
>>Header
>>Detail
>>Detail
>>Note (in 50 characters segments, if there is one)
>>
>>The train of thought that we are working on at the moment is:
>>Extract all of the headers we need into header.dbf, details into detail.dbf and the 50 character segments into multiple records in notes.dbf.
>>
>>Then issue:
>>
>>select header
>>scan
>>Copy to header.txt next 1 delimited && header
>>select detail
>>copy to detail.txt for detail.ponum = header.ponum delimited && details
>>select notes
>>copy to notes.txt for notes.ponum = header.ponum delimited && notes
>>select curmemo && dummy file used for a memo
>>zap
>>append blank
>>Append Memo curmemo.memo1 From header.txt
>>Append Memo curmemo.memo1 From detail.txt
>>Append Memo curmemo.memo1 From notes.txt
>>Fwrite(lnhandle,curmemo.memo1) && add to ASCII File
>>select header
>>endscan
>>
>>I am wondering if the code is efficient?
>>Also, based upon all of this info... what is the best way to handle the memo field assuming that I want to retain spaces?
>>
>>TIA.
>>
>>
>>
>>>Allan,
>>>
>>>Are there other fields (non-memo) being exported into the same text file? If so, and if the memo field for a given record is longer than 50 characters, how are the other fields supposed to be represented in the export file?
>>>
>>>As an example, lets assume the user types 78 characters (not all spaces) in the memo field. Do you need 2 records (lines) in the export file?
>>>
>>>>Steve,
>>>>
>>>>The scenario is as follows:
>>>>We have developed an application that allows the user to enter free form notes (stored in VFP MEMO).
>>>>Our application has to be able to 'export' our data to the end user's accounting app in an ASCII format.
>>>>They would like this MEMO field to be segmented into 50 character records in the ASCII file.
>>>>Now, if the user (in our VFP app) enters 50 or more consecutive spaces, that
>>>>portion of the memo creates a record with "" in the ASCII file.
>>>>I would like to be able to retain these spaces.
>>>
>>>>>Why is the empty string a problem?
>>>>>
