Building a comma delimited file using a program 1

[TallGuyinIT]

Hello,
I need to create a comma delimited file from a FoxPro table to send to a customer for import into their system. I cannot use the COPY TO command because the records are of various lengths and the customer does not want blank fields - example

HEADER1 FIELD1 FIELD2 FIELD3 .... FIELD9
HT CUST# UPS PO#
PD SKU QTY Price .... UPC#
PD SKU QTY Price .... UPC#
TD Total

Is there a way to easily create a comma delimited file excluding the blank fields?

Thanks...TallGuyinIT

 

[Stella740pl]

What do you mean, excluding blank fields? Can you show a sample of what your outcome should be like?

For comma delimited file, I believe, if a field is blank, a place-holding comma will be there instead, not a blank space.

If you don't want totally blank records, you can look into FOR clause of COPY TO command.

In any case, please clarify.

 

[TallGuyinIT]

Stella,

The place-holding commas are what I am trying to eliminate. I want the final text to read

HT,CUST#,UPS,PO#
PD,SKU,QTY,PRICE,EXTPRICE,UOM,BOX#,SKID#,UPCCODE
PD,SKU,QTY,PRICE,EXTPRICE,UOM,BOX#,SKID#,UPCCODE
PD,SKU,QTY,PRICE,EXTPRICE,UOM,BOX#,SKID#,UPCCODE
PD,SKU,QTY,PRICE,EXTPRICE,UOM,BOX#,SKID#,UPCCODE
PD,SKU,QTY,PRICE,EXTPRICE,UOM,BOX#,SKID#,UPCCODE
TD,INVOICETOTAL

The records are all in one cursor. What I get when I use the COPY TO command is;

HT,CUST#,UPS,PO#,,,,,
PD,SKU,QTY,PRICE,EXTPRICE,UOM,BOX#,SKID#,UPCCODE
PD,SKU,QTY,PRICE,EXTPRICE,UOM,BOX#,SKID#,UPCCODE
PD,SKU,QTY,PRICE,EXTPRICE,UOM,BOX#,SKID#,UPCCODE
PD,SKU,QTY,PRICE,EXTPRICE,UOM,BOX#,SKID#,UPCCODE
PD,SKU,QTY,PRICE,EXTPRICE,UOM,BOX#,SKID#,UPCCODE
TD,INVOICETOTAL,,,,,,,

Thanks for any assistance.

 

[Stella740pl]

If you eliminate the place-holding commas, and they are not at the end of the record/line, how their system will know which value goes to which field? It's going to be a mess.

In any case, you can put data in the file any way you want by SCANning through the table and using STRTOFILE() function. This way, you can form your own line for the comma-delimited file.

Something like this may do it:

SELECT MyTable
SCAN
string=IIF(!EMPTY(Field1),Field1+",", "")+ ;
IIF(!EMPTY(Field2),Field2+",", "")+ ;
IIF(!EMPTY(Field3),Field3, "")+CHR(10)+CHR(13)
StrToFile(string, 'C:\MyDir\MyText.txt', .T.)
ENDSCAN

Convert numeric fields into strings. Check if they are not equal to 0, if you wish.