Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > Tips > Saving DataWindow Reports in an Interchangeable Fo...

Saving DataWindow Reports in an Interchangeable Format for Excel

This technical tip explains how to save PowerBuilder DataWindow reports in a interchangeable format for Excel.
 
RSS Feed
 
 
 

One of the challenges facing developers is the exporting of datawindow reports into a format that is interchangeable with other applications (such as MS Excel, Word, email etc). Currently PowerBuilder provides the ability to save data in many different formats using the SaveAs function. The more common format is Excel so the data can be used in spreadsheets.

The limitation in using the SaveAs for Excel is that the data saved IS NOT the data displayed but rather the data that's in the datawindow buffer, and groupings, crosstabs, computed fields, etc. are lost. The data in the primary buffer is saved and the column order is determined by the order of the columns when the DataWindow was first created in the SQL Painter. The output data may not be what was expected and may not be useable without modification.

To output crosstab, group, computed fields, etc. in Excel format would be impracticable since it is impossible to fully represent the visual data in a true Excel format.

A new DataWindow function has been added to PowerBuilder 6.5 (after build 444) that resolves the limitations of the SaveAs function, it's called the SaveAsAscii function. It allows the output of data to a text file in a format that is representative of the visual presentation of the Datawindow. Groups, crosstabs, computed columns, etc. are saved as part of the output data.

Description

Saves the contents of a DataWindow or DataStore into a standard ASCII text file.

Controls

DataWindow controls and DataStore objects

Syntax

dwcontrol.SaveAsAscii ( filename {, separatorcharacter {, quotecharacter {, lineending } } } )

dwcontrol The name of the DataWindow control or DataStore whose contents you want to save

filename A string whose value is the name of the file in which to save the contents

separatorcharacter (optional) A string whose value is the character to be used to delimit values. If you omit separatorcharacter, the default is a tab character

quotecharacter (optional) A string whose value is the character to be used to wrap values. If you omit quotecharacter, the default is no character

lineending (optional) A string whose value is placed at the end of each line. If you omit lineending, the default is a carriage return plus a newline character (~r~n)

 

An example that would save the visual contents of a DataWindow with a '|' character as a separator would be:
 dw_4.saveasascii("monthly.txt","|")

It is preferable to use a separator character since the default of tab would cause import problems in Excel. The separator character used can be specified when the data is imported into Excel.

To import the data into Excel, just open the text file from Excel. You will be presented with an import wizard. It is important to specify the correct separator and un-select the tab character as a separator.

The below is output from a cross tab DataWindow that was imported into Excel without modification. This would not have been possible with the SaveAs function.

	Dept Id				
Manager Id	100	200	300	400	500
501	        1090				
703					        868
902		        1039	        1576	        
1293		        902	1390		
1576				        1191	

Another example is the exporting of data from a DataWindow that utilizes group with headers and footers. The SaveAsAscii function will preserve the groupings, headers, and footers. As can be seen from the below example, the group headings, detail lines and group footers are present. Also present is a report summary. This output is not available with the SaveAs function. The below example was imported without modification into Excel. Notice that the group totals are correctly located, as is the report summary.

 
Employee		
ID"	Last Name	
Department	100	
1090	Smith	
582	Samuels	
529	Sullivan	
479	Siperstein	
266	Gowda	
604	Wang	
453	Rabkin	
445	Lull	
316	Pastor	
1250	Diaz	
102	Whitney	
1157	Soo	
247	Driscoll	
160	Breault	
501	Scott	
958	Sisson	
243	Shishov	
862	Sheffield	
105	Cobb	
249	Guevara	
839	Marshall	
278	Melkisetian	
22	Employees in Department	100
Department	500	
191	Bertrand	
1013	Barker	
921	Crowley	
868	Kuo	
1658	Lynch	
1615	Romero	
750	Braun	
1570	Rebeiro	
703	Martinez	
9	Employees in Department	500
Total Employees >>	75	

It may be necessary to specifically place certain columns from the report into certain columns in the Excel spreadsheet. To accomplish this, place an empty text object where you require a blank column. This will be read as an empty cell and imported into Excel as an empty cell, This method permits accurate placement of data into the correct columns in the Excel spreadsheet.

The use of SaveAsAscii is not limited to Excel. Since the data is in a text file with a specified delimiter, it also permits easy exchange of data with other programs or via email.


 

DOCUMENT ATTRIBUTES
Last Revised: Mar 05, 1999
Product: PowerBuilder
Technical Topics: Application Deployment
  
Business or Technical: Technical
Content Id: 1000943
Infotype: Tips
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics