Export All Fields

[ITALIA]

Hello everybody!

I need to export a database table to exel, without selecting the fields to export; it should just export ALL fields in the table, same as when from a browse view I can click on the "export to exel" button.

When I create an export operation, I have to specify which fields I want to export.
My problem is that the table I need to export to exel is created at runtime and the fields change everytime the table is created. Therefore I can't specify which fields I want to export because they change everytime.

The users are currently opening the table to export in browse view and click the "export to exel" button at the top of the page. I would like to create a button for them to export the table without opening it.

Is there a way to create an export operation that works exactly like the "export to exel" button and export all the fields in the table without specifying which fields to export?


Thanks in advance.

MAURO

[MikeC]

Re: Export All Fields

Mauro,
As Alpha needs to know what fields are present in order to export them, why not tell Alpha this as it wants......have a button that loads a browse but does not show it (hidden), does the quick export to Excel (action script choice) for the browse, then close the browse (without ever showing it).

This would be the first way I would attempt this but have NOT tried it--if it works please post back...myself, am going to try and get a bit of sleep now!! :)

[Stan Mathews]

Re: Export All Fields

The attached function accepts a table name (fully qualified) and a spreadsheet name (fully qualified) and saves the entire table contents as the named spreadsheet. Should do what you want.

[MikeC]

Re: Export All Fields

Stan,
A nice addition and, of course, the correct way to accomplish the task. :)

[Stan Mathews]

Re: Export All Fields

Mike,

This is more like what Alpha probably does internally but this way the file is actually exported via Alpha to excel v4 and has the same 16384 lines limitation.

FUNCTION all_exp AS L (tablename AS C )
	all_exp = .F.
	IF alltrim(tablename) = ""
		ui_msg_box("Sorry","No table selected.")
		exit function
	ELSE IF (.not. file.exists(a5.get_path()+chr(92)+tablename+".dbf"))
		ui_msg_box("Sorry","Table does not exist.")
		exit function
	END IF
	flds = TABLE.EXTERNAL_FIELD_NAME_GET(tablename,"n")
	numflds = w_count(flds,crlf())
	a_tbl = table.open(tablename)
	base_name = file.filename_parse(tablename,"N")
	
	export.type = 4
	export.names = .T.
	export.file = a5.Get_Path()+chr(92)+base_name+".xls"
	export.options = ""
	export.field_sep = ""
	export.record_sep = ""
	export.fields = numflds
	for i = 1 to numflds
		eval("export.field"+alltrim(str(i))) = word(flds,i,crlf())
	next i
	a_tbl.export()
	all_exp = .T.
END FUNCTION

Output file is saved in the database directory.

[ITALIA]

Re: Export All Fields

Thanks guys, I'll try your suggestions..... looks like it's what i need !

[neecy]

Re: Export All Fields

Hello-

This function is also what I need, however, I need to include a filter to export only nonblank fields. Otherwise, I will have 542 fields trying to export to Excel. Is there a script to include and where would I write the script to the current code?

Thanks-

Neecy

[Stan Mathews]

Re: Export All Fields

What defines a nonblank field? Some values in the fields of the first record, last record, all records?

[neecy]

Re: Export All Fields

Hi Stan-

Thank you for replying. My definition of a nonblank field is that there has to be a numeric value either positive or negative or 0. This is setup for all of the records in which will be a daily session for calculating the last 4 days of data. My setup crosstabs and lists the row field as the date and the columns are the category and shift. I only produce 4 records daily and there are 541 numeric fields and 1 Cdate field. The date field will never be blank of course, however, some of the other fields will be blank and won't need them exported into Excel.

Thanks-
Neecy

[Stan Mathews]

Re: Export All Fields

That presents a problem. A numeric field that appears blank still evaluates to zero even if you don't see the zero in a browse. Don't know how to distinguish empty fields from fields with a zero value.

[neecy]

Re: Export All Fields

If I make the blank fields equal to zero, then would it be possible to establish a filter to say if export.filter .not. 0? Then the columns to exclude will be if they equal 0. In this case if within the four records, any of the 541 fields in which their value doesn't have a positive or negative value, then don't export.

Neecy
Thanks-

[Stan Mathews]

Re: Export All Fields

It is not that the blanks don't equal zero, it is that the blanks are equivalent to zeroes. not exporting columns with zeroes or equivalent contradicts what you stated earlier.

My definition of a nonblank field is that there has to be a numeric value either positive or negative or 0.

[neecy]

Re: Export All Fields

Thanks Stan-
I guess I need for the export to apply where there is either a positive or negative value indicated for each column. If within the 4 records, there are columns that are either all blank or all zeros, is there a way to filter them from the export? That way only fields that have a non zero or nonblank value are exported.

Neecy

[Stan Mathews]

Re: Export All Fields

Sorry to be picky but I still have to ask questions to understand. Is it possible to have the records

date_field     fld1   fld2   fld3   fld4    fld5    fld6
01/01/2001     -1             12      3      19       0       
01/15/2001      0              9      6      11       1

so that what we really need to do is check for blanks or zeroes in a field for all records to decide whether to export the field or not? IOW if we just check the first record above it could be decided not to export fld6.

[neecy]

Re: Export All Fields

Hi Stan-

Yes. :) This is exactly how my records are. I have several fields like fld2 in which they are blank. Do you have a way of checking and exporting the nonblank fields?

Thanks- :)