Formatting Excel or CSV

[Ted Giles]

I have a header and detail record set. The Detail is in a browse which is marked as paid which I want to export.

Customer
..Invoice 1
..Invoice 2
..Invoice N

What is the best way to present this in a CSV or Excel format?

The CSV shows the Customer name against each invoice, and I need just the one line followed by the invoice details, viz;
Aardvark Enterprises Ltd
Invoice 1
Invoice 2
Invoice n

and not
Aardvark Enterprises Ltd, Invoice 1
Aardvark Enterprises Ltd, Invoice 2
Aardvark Enterprises Ltd, Invoice n

The Excel output won't show the data from the browse, or at least I cannot get it to do so.

Using a Form and exporting to Excel is a possibility but I am attempting to reduce the user effort and mouse clicks.

Preferred option is the CSV route.

Any ideas?

[Stan Mathews]

Re: Formatting Excel or CSV

For a set where the detail you want in the csv files consists of parent and a single child fields, current record visible on the form.

tbl_prnt = table.current(1)
tbl_chld = table.current(2)

filnam = ui_get_file("Name for export","*.csv","C:\temp\exported.csv","N")
if filnam <> ""
	fil = file.create(filnam,FILE_RW_EXCLUSIVE)
	fil.write_line(tbl_prnt.[COLOR="#FF0000"]customer_name_field[/COLOR])
	tbl_chld.fetch_first()
	while .not. tbl_chld.fetch_eof()
		fil.write_line(tbl_chld.character_field+" "+alltrim(str(tbl_chld.numeric_field1,10,2))+"  "+alltrim(str(tbl_chld.numeric_field2,10,2))) 'modify this line to construct child details desired
		tbl_chld.fetch_next()		
	end while
	fil.flush()
	fil.close()	
else
	msgbox("No filename supplied.")
end if

[Ted Giles]

Re: Formatting Excel or CSV

Thanks Stan.
I will try this later when I get on the Desktop.
Will this print only the child records visible in the browse?
The invoice records may not all be marked as paid, so I have a P for paid in the child table, and show only those with P in the browse when printing to CSV at present. Only those in the browse print.

[Stan Mathews]

Re: Formatting Excel or CSV

The code should copy only those records available to the form at the time it is executed. The current parent and linked child records. I am unclear on how you "show only those with P in the browse". If you have queried the form/set for P in the browse it will also limit the records available to the code.

[Ted Giles]

Re: Formatting Excel or CSV

Thanks, in front of the DT now so will get on and implement.
It works like a dream!

The Show only those with P is run from a button launching a coded query.

At the risk of trying your infinite patience, can I copy the field names as well, viz;

Customer
Invoice Number, Invoice Date, Invoice amount (or a text line under Customer?)
12345 01/01/1900 $1234.00
23456 02/03/1901 $ 234.00

or would I be better inserting a text line to match the content (as commented above)?

[Stan Mathews]

Re: Formatting Excel or CSV

I think you are better off just adding a line of text as you have to know the field name to use a method that returns the field name.
tbl_chld.fieldname.name_get() seems unnecessarily obscure.

Using a text line you can also format as you want. If the field is really "inv_num" and you want "Invoice Number" in the csv just adding the text is better.

fil = file.create(filnam,FILE_RW_EXCLUSIVE)
fil.write_line(tbl_prnt.customer_name_field)
fil.write_line("Invoice Number, Invoice Date, Invoice amount")
' rest of script

This illustrates a point which is not obvious and took me some time to fully understand.

When you obtain a table.current(#) pointer to the table(s) of an open form, you are by definition limited to the records visible on the form. If you are performing some processing on those records you will likely want to use the <tbl>.get_cursor() method instead but if you just want to gather information you can fetch, loop, whatever, without worrying about accidentally navigating to unrelated child records.

[Ted Giles]

Re: Formatting Excel or CSV

I am indebted Stan, many thanks for the leg up.

[Stan Mathews]

Re: Formatting Excel or CSV

All sorts of fun things you can do.

tbl_prnt = table.current(1)
tbl_chld = table.current(2)

filnam = ui_get_file("Name for export","*.csv","C:\temp\exported.csv","N")
if filnam <> ""
	ctr = 0
	ttl1 = 0
	fil = file.create(filnam,FILE_RW_EXCLUSIVE)
	fil.write_line(tbl_prnt.[COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]customer_name_field[/COLOR])
	fil.write_line("Invoice Number, Invoice Date, Invoice amount")
	tbl_chld.fetch_first()
	while .not. tbl_chld.fetch_eof()
		fil.write_line(tbl_chld.[COLOR="#FF0000"]invoice_number[/COLOR]+" "+dtoc(tbl_chld.[COLOR="#FF0000"]invoice_date[/COLOR],"1-")+"  "+alltrim(str(tbl_chld.[COLOR="#FF0000"]invoice_amount[/COLOR],10,2))) 'modify this line to construct child details desired
		ctr = ctr + 1
		ttl1 = ttl1+tbl_chld.[COLOR="#FF0000"]invoice_amount[/COLOR]
		tbl_chld.fetch_next()
	end while
	fil.write_line("") 'blank line for spacing
	fil.write_line("Processed "+alltrim(str(ctr)) +" records.")
	fil.write_line("Invoice amount total is "+alltrim(str(ttl1,10,2)))
	fil.flush()
	fil.close()
else
	msgbox("No filename supplied.")
end if

Adjust test field names to fit your actual names.

[Ted Giles]

Re: Formatting Excel or CSV

Thanks again.
As you know, I avoid code wherever possiible.
I try to adhere to the Alpha concept of no/low code.
Alpha is sooooo flexible, that it gets you imagination whirring, and before you know it, code is required cos just mouse clicks don't quite cut it.
I am my own worse enemy.

[MoGrace]

Re: Formatting Excel or CSV

I am my own worse enemy.

been there