Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Problem creating a delimited file

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Problem creating a delimited file
 Login/Join
 
Anish
Member
posted
Hello everyone

I am having serious problems trying to create a delimited file from WebFocus. The requirement is to define each column at a certain width and pad that column with spaces if the column is shorter than that length.

I tried Excel, HTML and PDF versions and it looks like the spaces are getting lost.

I even went to the extent of padding each of the columns using rpad function of Oracle sql, but the spaces disappear when run in HTML.

Here is the example of sql I used

select distinct rpad(a.spriden_id, 18, ' ') || 'Y' ||
rpad(nvl(a.spriden_last_name, ' '), 30,' ') ||

I can replace the padding character from space to any other character and I can see that character being retained when converted to HTML, but not the space. Is it because the inconsistency between the db os and the windows client?

Please consider this an emergency.


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Leah
Virtuoso
posted Hide Post
HTLM will not show your spaces due to the browser more than anything, have you tried one of the comma formats. What is the purpose of the 'fixed lengths' if I may ask?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
smiths
Platinum Member
posted Hide Post
Anish,

To maintain spaces in HTML, you will need to replace each blank space with &|nbsp;

If this is in a fex, you will need to put a vertical bar (|) after the &, ie &|nbsp;

Regards,
Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
Your signature says Output: Excel, so I'm assuming you want to create an Excel 2000 spreadsheet (WebFOCUS format EXL2K). You can control the width of an Excel 2000 column by using WebFOCUS Style Sheet commands like WRAP.

Here's an example:

TABLE FILE CAR
SUM
SALES
BY COUNTRY

HEADING
"WEBFOCUS REPORT"

ON TABLE PCHOLD FORMAT EXL2K

ON TABLE SET STYLESHEET *
TYPE=REPORT, FONT='ARIAL', SIZE=8, $
TYPE=HEADING, SIZE=10, $
TYPE=TITLE, STYLE=BOLD, $
TYPE=REPORT, COLUMN=SALES, WRAP=2, COLOR=BLUE, $
TYPE=REPORT, COLUMN=COUNTRY, WRAP=5, COLOR=GREEN, $

END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
susannah
Expert
posted Hide Post
The output format you want is ALPHA
ON TABLE HOLD FORMAT ALPHA
will make a fixed format file
each field will be as wide as you have specified it.
You filedef the output file and park it somewhere. *Any* app you open it with other than Notepad and the like, will squoosh spaces.
Spaces *will* disappear when you display in html... by definition. but you say you are trying to make a 'delimited' file, which suggests text.
If you want BOTH, say a fixed format with a specified delimiter, say a pipe,
then
DEFINE FILE CAR
PIPE/A1 WITH CAR = '|';
END
TABLE FILE CAR
PRINT COUNTRY PIPE MODEL PIPE CAR PIPE ...etc
Any help?



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
GinnyJakes
Expert
posted Hide Post
Don't forget that wonderful APP HOLD dirname command which will hold it in a directory without having to do a filedef and makes your program platform independent.

Alternates to this are

APP HOLDMETA dirname which only saves the master.
APP HOLDDATA dirname which only saves the data file.


There is also an APP FI ddname DISK dirname/filename as an alternate to the original FILEDEF where you had to put the full disk path of the file.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
linus
Platinum Member
posted Hide Post
Try adding an lrecl specification to your filedef. I had a similar task as yours and this is how I resolved the issue:
APP HOLD car_dept
FILEDEF TIAASAV DISK /comm/ibi/webfocus/apps/car_dept/carinfo.FTM (LRECL 68 RECFM V

Then I defined fields equal to the length of the blank spaces needed and concatenated these to my record instead of concatenating ' '

DEFINE FILE CARHLD4
BLSPC1/A1 = ' ';
BLSPC2/A2 = ' ';
BLSPC4/A4 = ' ';
BLSPC15/A15 = ' ';
NEWCAR/A28 = MAKE | MODEL | BLSPC4 ;

I had problems with the concatenation of ' ' even with a weak concat. so I tried the define fields and it seemed to work.

Hope this helps.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Report This Post
linus
Platinum Member
posted Hide Post
The filedef tiaasav was supposed to be changed to filedef carinfo in my previous post.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Report This Post
Dave K.
Member
posted Hide Post
Try

select distinct rpad(a.spriden_id, 18, ' ') | 'Y' |
rpad(nvl(a.spriden_last_name, ' '), 30,' ') |

The || operator removes trailing and preceding spaces. | does not.
 
Posts: 1 | Registered: October 25, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
I love how 25 different solutions are suggested without any input from the person that originally posted the question.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Anish
Member
posted Hide Post
Thanqs very much to all

i am sorry, today i was late to work, just logged in and i am trying out your suggestions


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Anish, while you're trying out the ideas presented here, could you please describe in more detail what you're trying to achieve. Is it a comma delimited file, an alpha file with columns padded by spaces, an Excel file or an Excel 2000 file?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Anish
Member
posted Hide Post
Hi Francis

i did with the Wrap

TYPE=REPORT, COLUMN=SALES, WRAP=18, COLOR=BLUE,

Here 18 means wot is the measurement cm or inch? and how to change bcoz, column width is very big


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Anish
Member
posted Hide Post
Francis

The output should be in Excel2k, with same columnwidth, which is specified

Thanqs


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
18 seems to be an arbitrary measurement that is close to inches. Use a smaller number.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Anish
Member
posted Hide Post
Because i required 18 characters or 18 digits column width.

6 inches=15cm

depends on this calculation i have give?


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
It has nothing to do with the number of characters. - it can't, because the size of the characters would would depend on the font and the font size.

The resulting Excel 2000 spreadsheet column does not contain trailing blanks to make the width, the width of the column is adjustable by the WebFOCUS style sheet WRAP attribute. You have to decide how wide you want the column to be.

The user can adjust the width of the column when the file is opened in Excel. I don't understand your concern about the width of a column.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Anish
Member
posted Hide Post
Francis

thanqs
The report consists of 18 columns, in that first column should start at 1 and end at 18 and second column should start at 19 and end at 30......etc. because output is sent as source further

Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Tony A
Expert
posted Hide Post
The width (and wrap) values in the style sheet relates to the UNITS attribute.

In the help file(s) this information is given -

quote:
Procedure: How to Measure for Column Width and Decimal Alignment
Measuring Width. Determining the width of a heading or footing item is a three-step process:

Identify the maximum number of characters in a text string or field.

For a text string, simply count the characters. For a field, refer to the format specification in the Master File or in a command such as a DEFINE.

Measure the physical space in units (for example, in inches) that is required to display the number of characters identified in step 1, based on the size of the font you are using. For example, the following value of the COUNTRY field would measure as follows:

Font
Font size
Comparison
Inches

Helvetica
10
England
.5

Times New Roman
10
England
.44

Courier
10
England
.56

Tip: Consider using a consistent set of fonts in your reports to make your measurements reusable.

Measuring for Decimal Alignment. After you have determined the width of an item, you can do a related measurement to determine the physical space required to display decimal data with a varying number of digits to the right of the decimal point.

Determine the maximum number of decimal places you need to accommodate to the right of the decimal place, plus the decimal point itself.

Measure the physical space in units (for example, in inches) that is required to display the number of characters identified in step 1, based on the size of the font you are using.


You can also find this info using the search facility (top right). Use the left hand search option on the resulting screen (Search Technical Documentation), select your release and then search on the text "width attribute". Look at the first item returned and you should have loads of info. to help you.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
quote:
The report consists of 18 columns, in that first column should start at 1 and end at 18 and second column should start at 19 and end at 30......etc. because output is sent as source further

Then you don't want an Excel 2000 spreadsheet, you want an ALPHA HOLD or SAVE file.

SET HOLDLIST=PRINTONLY
-RUN

TABLE FILE CAR
PRINT
CAR/A18
COUNTRY/A20
MODEL/A25
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

?FF H001
-RUN


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Anish
Member
posted Hide Post
Thanqs very much to all


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Problem creating a delimited file

Copyright © 1996-2020 Information Builders