exporting - Has anyone been able to make this work?
Started by Peter Birney, PB Associates
You will need to Sign In to be able to add or comment on the forum!
exporting - Has anyone been able to make this work?
I require an output file in Excel format from my DQL procedure.
At the Print window I select Excel and enter a filename but when I try and open the resultant file with Excel (2 different versions) or the other piece of software that requires the Excel format file they all say that it is not in an Excel format.
If I then try and use the "Export To" command from within my DQL to try and produce a CSV file sometimes the DQL works and sometimes not.
It would be better if an Excel format file could be produced - Has anyone managed to produce one from the Print window using this version of DataEase?
Re:exporting - Has anyone been able to make this work?
It's a very good reason why all of this stuff has been removed from DE8. It is a little like WebPublisher... A lot of promise and a lot of disappointment after a lot of work.
1. It does work, but it is part of the same library as the PDF printer etc, and hence doesn't work in x64.
When it works, it try to create a Excel file that look like your "print report" i.e. it reverse convert it from a PRINT to a Excel Spreadsheet.
Needless to say this is a very bad idea and it is in practice completely useless.
Export To in DQL has always been dodgy and we did a test in DE8 (see end). Exporting via ExecDQL and DataExport() is fast and reliable, but exporting via Export TO in DQL is very badly implemented and the speed varied tremendously. A export for 30.000 records in ExecDQL took less that a second and with Export To it took from 2 minutes to 32 minutes... So stay away!
If you want to export stuff from a DQL use the DataExport() function in the DQL. It is the fastest and best way of generating export.
The process is as follows:
1. Build the DQL with the List Record of the file you want to export
ex:
for Addressbook ;
list records
Name ;
Address ;
Town ;
Postcode ;
4+5 ;
concat(Name,Address) .
I have included a couple of "derivations" here to show that it will work too.
Compile and run the DQL (Must print to screen as the DataExport() is a GUI function).
When you view the first page of the output, simply go to file/export and create an export file (see right).
You can format it as you like.
Now you have the export definition. DQLExport.DBE that export to file DQLExport.txt
To complete the "automated export simply go back to the DQL and add:
define "retval" number . -- need a variable to allocate return value from functions.
for Addressbook ;
list records
Name ;
Address ;
Town ;
Postcode ;
4+5 ;
concat(Name,Address) .
retval := DataExport("DQLExport.dbe") . -- this exports the entire Multiview (i.e. query) in one go, no single step stuff like Export To.
exit -- we simply now exit the DQL as it is no need to do all the boring single step stuff that the old team envisaged.
The DQL will now immediately export the entire content of the multi view and then exit the DQL. You will have to close the Output manually as this is a print to screen DQL. You don't have to retain any of the fields etc in the output, so you can simply have a nice little message and a close me button.
There is a sample app in 7.2 called DQL Export that will show this.
There is a blog entry about exporting in DE8 here.
http://www.dataease.com/DG3_BlogList/?ParentID=0000000112&field1=0000000112
Re:exporting - Has anyone been able to make this work?
Yes, I have in the past many times.
Exporting to CSV always works. What is that you are trying to do ?
Regards
Arul
Fish4Support, 335 Goldhawk Trail, Scarborough, ON M1V 4G2, Canada
Tel(Canada): 647-930-6497 | Tel(USA):203-726-8149 | Email: arulj@fish4support.com | Web: www.fish4support.com
Re:Re:exporting - Has anyone been able to make this work?
He is trying to use the Export to Excel functionality in 7.x not CSV export from Form.
Export from DQL has been a big problem as we all know throughout the DFW era. Something that was so natural and simple in DFD became a problem with no proper solution in DFW....
In DE8 there is already many solutions and the best one will be DE8 Reporter, but ExecDQL is not bad either. Have a look below:
http://www.dataease.com/DG3_BlogList/?ParentID=0000000112&field1=0000000112
Re:Re:Re:exporting - Has anyone been able to make this work?
My DQL is in this format:-
for Table A
for Table B
for Table C
list records
some fields from Table A
some fields from Table B
some fields from Table C
end
end
end
When I run the DQL the correct information appears on the screen, but when I use the DQLExport I firstly have to select Table C (No fields are shown for me to select from unless I do) and only Table C's fields appear correctly in the output file (Whether I select one of the text file options or Lotus format), most of the other fields being missing and those that are present being wrong.
I am suspecting that what I want to do is impossible in this version of DataEase.
Re:Re:Re:Re:exporting - Has anyone been able to make this work?
The problem with DFW and DQL is that the DQL in many ways is a "fraud".
It was implemented retrospectively and in contravention of how the new PRISM was designed.
A DQL with List records is an emulation of the real multiview and the relationship between the multiview and the DQL/Body etc. has always been edgy....
Part of the problem is that some of the DQL functionality has been implemented in the GUI and not in PRISM.
So to get around this in 7.x one have to use a little inside knowledge:
The simplest and cleanest way is simply to allocate the values from the outer for loops to temporary variables:
define temp "retval" number .
define temp "CustName" text 255 .
define temp "Adr" text 255 .
for Table 1 ;
CustName := CustomerName .
for Table 2;
Adr := Address .
for Table 3 ;
list records
CustomerNr ;
CustName ;
Adr;
Town .
retval := dataexport("ExportThreeTables.dbe") .
exit .
end
exit .
end
exit .
end
exit .
Re:Re:Re:Re:Re:exporting - Has anyone been able to make this work?
I managed to get it to work by writing my data to a flat file and exporting the contents of that single file.
Thanks for your help Mr D.
