Problem creating a delimited file

Focal Point

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7781012652

November 07, 2007, 04:02 PM

Anish

Problem creating a delimited file

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

November 07, 2007, 04:25 PM

Leah

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

November 07, 2007, 04:28 PM

smiths

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

November 07, 2007, 04:40 PM

Francis Mariani

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

November 07, 2007, 06:33 PM

susannah

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

November 07, 2007, 06:44 PM

GinnyJakes

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

November 08, 2007, 08:36 AM

linus

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

November 08, 2007, 08:37 AM

linus

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

November 08, 2007, 09:58 AM

Dave K.

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.

November 08, 2007, 10:07 AM

Francis Mariani

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

November 08, 2007, 10:22 AM

Anish

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

November 08, 2007, 10:27 AM

Francis Mariani

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

November 08, 2007, 10:30 AM

Anish

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

November 08, 2007, 10:33 AM

Anish

Francis

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

Thanqs

WebFOCUS 762 and 761
Windows
Output: Excel

November 08, 2007, 10:33 AM

Francis Mariani

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

November 08, 2007, 10:37 AM

Anish

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

November 08, 2007, 10:52 AM

Francis Mariani

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

November 08, 2007, 10:56 AM

Anish

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

November 08, 2007, 11:02 AM

Tony A

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

November 08, 2007, 11:09 AM

Francis Mariani

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

November 09, 2007, 08:51 AM

Anish

Thanqs very much to all

WebFOCUS 762 and 761
Windows
Output: Excel