 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Need Spaces to be in excel concatenated file
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Need Spaces to be in excel concatenated file Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Need Spaces to be in excel concatenated file | Login/Join |
| Silver Member |
Hi, I have a dilemma that I need some assistance with. I have a fex file that I need to be able to show the spaces in the output. The scenario is a bit complicated. Here are the specifics. WebFOCUS 8.2.0.2M is the version we are on. The fex file below creates an excel spreadsheet with values from the CAR file, but to the right of each row is a concatenated line of all the fields in that row. The user will make changes to the fields on the left and they will automatically populate into the concatenated line to its right. The problem I am having is that when there is a field that is blank or spaces, I need those spaces to be in the concatenated file as spaces and not squeezed together when it is blank (I made the SEATS field spaces when it was equal to 2 for this demonstration as this is not my real data). I want the number of characters (say 3) to show so we will see spaces where the field would be (column C is the one I am attempting to get spaces into when there is no value). Our users use this concatenated field to upload into their system. I have already tested this and it all worked (we have yet to put it into prod), but when it was tested again the spaces that I put into it ( ) no longer work; the system the file is uploaded into says the characters are invalid. As a test one of our BA's copied the line from the excel spreadsheet to Word and with the checkbox on to see spaces between words, a real space shows up like a period but these are showing up as little o's, so something isn't correct. I have tried using hexbyt with a number of different values, 10, 13, 32, 160, and 255. I searched this forum and those were the choices people had made to get spaces in excel output. I have tried so many combinations of fields and spaces with the hexbyte parameter, plain spaces, just about everything I can think of. I also tried using the SET SHOWBLANKS = on as well but I am finding I get closer with it off. So without further boring you, here is the code:
-*SET SHOWBLANKS=ON
SET PAGE-NUM=OFF
DEFINE FILE CAR
A_SEATS/A3=EDIT(SEATS);
SOME_BLANK_SEATS/A3=IF A_SEATS EQ '002' THEN HEXBYT(160,'A1') ELSE A_SEATS;
END
-RUN
TABLE FILE CAR
PRINT
CAR AS 'Car'
MODEL AS 'Model'
SOME_BLANK_SEATS AS 'Seats'
DEALER_COST AS 'Dealer Cost'
RETAIL_COST AS 'Retail Cost'
SALES AS 'Sales'
LENGTH AS 'Length'
WIDTH AS 'Width'
HEIGHT AS 'Height'
COMPUTE SPACES/A10=' ';
COMPUTE SPACES_2/A10=' '; NOPRINT
COMPUTE SPACES_3/A10=' '; NOPRINT
COMPUTE REC_CNT/D20.2 = LAST REC_CNT + 1; NOPRINT
COMPUTE REC_CNTX/D20.2 = REC_CNT + 2; NOPRINT
COMPUTE ROW_NUMBERY/A20=EDIT(REC_CNTX); NOPRINT
COMPUTE RN_TRIM/A5=TRIM('L',ROW_NUMBERY,20,'0',1,RN_TRIM); NOPRINT
COMPUTE ONE/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE TWO/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE THREE/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE NBSP3/A9=ONE|TWO|THREE; NOPRINT
COMPUTE KONKAT/A1700='=CONCATENATE(A' || RN_TRIM || ',B' || RN_TRIM || ',LEFT("'|NBSP3|'",3-LEN(C' || RN_TRIM || '))' || ',D' || RN_TRIM || ')'; AS 'Concatenated Line'
BY CAR NOPRINT
BY MODEL NOPRINT
HEADING
"Car Test"
ON TABLE SET HTMLCSS ON
ON TABLE HOLD AS REP001 FORMAT HTMTABLE
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, GRID=ON,$
TYPE=HEADING,HEADALIGN=BODY,JUSTIFY=CENTER,COLSPAN=11,$
TYPE=DATA,COLUMN=KONKAT,JUSTIFY=LEFT,WRAP=OFF,$
TYPE=TITLE,CLASS=rotate,$
TYPE=DATA,CLASS=border,$
ENDSTYLE
END
-RUN
SET HTMLFORMTYPE=XLS
-RUN
-HTMLFORM BEGIN
<STYLE>
.border
{
font-size:9pt;
font-family: Arial;
text-align:center;
mso-number-format:"\@";
}
.rotate
{
mso-rotate:90;
vertical-align:center;
height:auto;
font-weight: bold;
text-align: center;
}
</STYLE>
<BODY>
!IBI.FIL.REP001;
</BODY>
-HTMLFORM END
-EXIT
Right now the code above shows the output as a period, a small o, and another period - I am not sure why I get two spaces that are valid and one that is not - just very weird. I tried to copy/paste it from Word to here but it removed all the periods and small o's - I can check when I am done here to see if we are allowed to attach files to our posts. I just wanted to attach a png file. Anyway, thanks for listening, and any help would be appreciated. PaulThis message has been edited. Last edited by: Paul M., WebFOCUS 8 Windows, All Outputs | ||
|
| Master |
Since you are outputting it to html first, by default html reduces all multiple space characters down to one space. so if you'd like multiple spaces you'll need to use the for a non-breaking space character in your code, with a pipe excape on the & character so IBI won't read it as an amper variable:
-*SET SHOWBLANKS=ON
SET PAGE-NUM=OFF
DEFINE FILE CAR
A_SEATS/A3=EDIT(SEATS);
SOME_BLANK_SEATS/A63=IF A_SEATS EQ '002' THEN '&|nbsp;&|nbsp;&|nbsp;' ELSE A_SEATS;
END
-RUN
TABLE FILE CAR
PRINT
CAR AS 'Car'
MODEL AS 'Model'
SOME_BLANK_SEATS AS 'Seats'
DEALER_COST AS 'Dealer Cost'
RETAIL_COST AS 'Retail Cost'
SALES AS 'Sales'
LENGTH AS 'Length'
WIDTH AS 'Width'
HEIGHT AS 'Height'
COMPUTE SPACES/A63='&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;';
COMPUTE SPACES_2/A63='&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;'; NOPRINT
COMPUTE SPACES_3/A63='&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;'; NOPRINT
COMPUTE REC_CNT/D20.2 = LAST REC_CNT + 1; NOPRINT
COMPUTE REC_CNTX/D20.2 = REC_CNT + 2; NOPRINT
COMPUTE ROW_NUMBERY/A20=EDIT(REC_CNTX); NOPRINT
COMPUTE RN_TRIM/A5=TRIM('L',ROW_NUMBERY,20,'0',1,RN_TRIM); NOPRINT
COMPUTE ONE/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE TWO/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE THREE/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE NBSP3/A21=TRIM_(BOTH, ' ',ONE||TWO||THREE); NOPRINT
COMPUTE KONKAT/A1700='=CONCATENATE(A' || RN_TRIM || ',B' || RN_TRIM || ',C' || RN_TRIM || ',D' || RN_TRIM || ')'; AS 'Concatenated Line'
BY CAR NOPRINT
BY MODEL NOPRINT
HEADING
"Car Test"
ON TABLE SET HTMLCSS ON
ON TABLE HOLD AS REP001 FORMAT HTMTABLE
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, GRID=ON,$
TYPE=HEADING,HEADALIGN=BODY,JUSTIFY=CENTER,COLSPAN=11,$
TYPE=DATA,COLUMN=KONKAT,JUSTIFY=LEFT,WRAP=OFF,$
TYPE=TITLE,CLASS=rotate,$
TYPE=DATA,CLASS=border,$
ENDSTYLE
END
-RUN
SET HTMLFORMTYPE=XLS
-RUN
-HTMLFORM BEGIN
<STYLE>
.border
{
font-size:9pt;
font-family: Arial;
text-align:center;
mso-number-format:"\@";
}
.rotate
{
mso-rotate:90;
vertical-align:center;
height:auto;
font-weight: bold;
text-align: center;
}
</STYLE>
<BODY>
!IBI.FIL.REP001;
</BODY>
-HTMLFORM END
-EXIT
HallwayProd: 8202M1 Test: 8202M4 Repository:  OS:  Outputs:     | |||
|
| Silver Member |
Hallway, Thanks so much for your reply. I completely understand why using "&|nbsp" would be appropriate, however when you get the final excel output and you copy line K4 from the spreadsheet to Word, you get a weird character to isn't really a space. I would have uploaded a screenshot but I don't think you can upload files to this platform. In Word when you set the Display to show the spaces, an actual space is a period in the display. When you look at what comes out when adding the "&|nbsp", instead of a period it is a small zero or o. We then take that data and upload it to the customers system but since the value isn't "really" a space it says it is an invalid character. I am not sure what to do at this point to get actual spaces between the values when the value is blank. I will continue trying. Thanks. PaulThis message has been edited. Last edited by: Paul M., WebFOCUS 8 Windows, All Outputs | |||
|
| Master |
I'm thinking that you will run into this problem every time when outputting it to an HTMTABLE. The HTML parser converts all spaces ( HEXBYT(32,'A1') ) to a before exporting to XLS. Try this code using EXL07 FORMULA and see if that gets you ehat you need: DEFINE FILE CAR A_SEATS/A3=EDIT(SEATS); A_DEALER_COST/A20V = PTOA(DEALER_COST, '(P12)', 'A20V') ; SPC/A1 = HEXBYT(32,'A1'); END TABLE FILE CAR PRINT CAR AS 'Car' MODEL AS 'Model' COMPUTE SOME_BLANK_SEATS/A10V=IF A_SEATS EQ '002' THEN SPC | SPC | SPC ELSE A_SEATS; AS 'Seats' A_DEALER_COST AS 'Dealer Cost' RETAIL_COST AS 'Retail Cost' SALES AS 'Sales' LENGTH AS 'Length' WIDTH AS 'Width' HEIGHT AS 'Height' COMPUTE KONKAT/A1700= CAR||MODEL||SOME_BLANK_SEATS||A_DEALER_COST; AS 'Concatenated Line' BY CAR NOPRINT BY MODEL NOPRINT HEADING "Car Test" ON TABLE SET PAGE-NUM OFF ON TABLE SET BYDISPLAY ON ON TABLE SET DROPBLNKLINE HEADING ON TABLE PCHOLD FORMAT EXL07 FORMULA ON TABLE SET STYLE * UNITS=IN, SQUEEZE=ON, GRID=ON,$ TYPE=HEADING, HEADALIGN=BODY, JUSTIFY=CENTER, COLSPAN=10, $ TYPE=TITLE, JUSTIFY=CENTER, FONT='ARIAL', SIZE=11, BORDER-TOP=LIGHT, BORDER-RIGHT=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-LEFT=LIGHT, STYLE=BOLD, $ TYPE=DATA, JUSTIFY=CENTER, FONT='ARIAL', SIZE=9, BORDER-TOP=LIGHT, BORDER-RIGHT=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-LEFT=LIGHT, $ TYPE=DATA, COLUMN=KONKAT, JUSTIFY=LEFT, WRAP=OFF, $ ENDSTYLE END -RUN HallwayProd: 8202M1 Test: 8202M4 Repository: OS: Outputs: | |||
|
| Silver Member |
Hallway, Once again, thanks so much for your help. Unfortunately, now there are 8 spaces where there should only be 3 - I tried to make that field A3 but it still put in 8 spaces. Part of the reason I use the HTMTABLE and the SET HTMLFORMTYPE=XLS is for the ability to manipulate the titles and borders and the like in my original post because we are not able to get our macro to work in WF 8.2.0.2m - so the manipulation we do is what gets us the correct titles and such(the real one is the rotating of the titles). So apart from your awesome help with trying EXL07 FORMULA, I should have said why we were doing these things. We currently have our report in prod with something a little more clunky than what we see here. I was trying to eliminate some of the steps for our customer. It looks like we may have to leave it as it is until we can either resolve this code or get our macro to work that stopped working in the new version we upgraded to 8.2.0.2m Thanks. Paul WebFOCUS 8 Windows, All Outputs | |||
|
| Silver Member |
I wanted to mention that I resolved this by making a change to the CONCATENATE field. I removed the LEFT parameter and replaced it with the REPT (repeat) function and removed the field NBSP and the ticks (') surrounding it, and when you cut/pate the line into Word it shows the correct number of spaces - much simpler than I was trying to make it. COMPUTE KONKAT/A1700='=CONCATENATE(A' || RN_TRIM || ',B' || RN_TRIM || ',REPT(" ",3-LEN(C' || RN_TRIM || '))' || ',D' || RN_TRIM || ')'; AS 'Concatenated Line'
I don't recall how to close this however. Thanks, Paul WebFOCUS 8 Windows, All Outputs | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Need Spaces to be in excel concatenated file
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Need Spaces to be in excel concatenated fileCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |