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     [SOLVED] Question about COMT

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Question about COMT
 Login/Join
 
ChannyS
Guru
posted
The documentation states:

"Saves the column headings in the first row of the output file. It produces a variable-length text file with fields separated by commas, and with character values enclosed in double quotation marks. Leading blanks are removed from numeric fields, and trailing blanks are removed from character fields."

However, I'm finding that only values that actually contain a comma in it get the double quotes around it. Is there some setting I need to set in order to get the double quotes around all character values?

This message has been edited. Last edited by: ChannyS,


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
mrguru
Gold member
posted Hide Post
I just ran a focexec and did an on table hold format comt and the results were as stated. If the master shows the field as an alpha then it is enclosed in double quotes. I am running 7.6.4.


WF 8.2.01M
8.2.01M Reporting Server
Windows 2012 Srvr R2
PDF,Excel, HTML
Graphs - a lot of graphs
 
Posts: 60 | Location: Atlanta, GA | Registered: October 30, 2003Report This Post
ChannyS
Guru
posted Hide Post
I am using 7.6.8. When I open in textpad it doesn't show the double quotes unless there is a comma in the data.


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
ChannyS
Guru
posted Hide Post
When I run the following FOCEXEC:

 
TABLE FILE CAR
PRINT CAR COUNTRY
COMPUTE FIELD_WITH_COMMA/A9 = 'HELLO,BYE';
ON TABLE PCHOLD FORMAT COMT
END


Save the results to a file, open the file in TextPad, this is what I get:

  

CAR,COUNTRY,FIELD_WITH_COMMA
JAGUAR,ENGLAND,"HELLO,BYE"
JENSEN,ENGLAND,"HELLO,BYE"
TRIUMPH,ENGLAND,"HELLO,BYE"
DATSUN,JAPAN,"HELLO,BYE"
TOYOTA,JAPAN,"HELLO,BYE"
ALFA ROMEO,ITALY,"HELLO,BYE"
MASERATI,ITALY,"HELLO,BYE"
AUDI,W GERMANY,"HELLO,BYE"
BMW,W GERMANY,"HELLO,BYE"
PEUGEOT,FRANCE,"HELLO,BYE"


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
j.gross
Virtuoso
posted Hide Post
COM and COMT produce CSV files.

There seems to be no published standard for CSV format, and no accepted convention for representing null values. Given that state of affairs, the product folks apparently decided to ignore the MISSING attribute, to ensure that other programs opening the csv file would not choke. Thus, the output file represents NULL by " " (for alpha fields) or 0 (for numeric or date fields).
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
ChannyS
Guru
posted Hide Post
Jack - I think you meant this as a response to my other post. In any case, following Susannah's idea, I can use the MISSING to get myself the NA I want. But, that brings me back to this post! If they actually would put quotes around alpha fields then my numeric fields which have turned into alphas so that I can get NA's into them will have quotes around them. :-(


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
j.gross
Virtuoso
posted Hide Post
Oops.

But, joining the two posts, in 768 if you construct an alpha field (with missing=on), containing either the numeric representation of your numeric field or a suitable representation of NA, then the former will be held w/o quotes, and Excel should render the field as either a number or NA, as required. Non?


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
ChannyS
Guru
posted Hide Post
Well I guess it's a little cyclic here because I have a real alpha field that I'm expecting to always have quotes around it and that's not happening. So if that's continues working that way, then the missing field is fine (numeric or NA). However, if it works the way I expect it, then my numeric fields which need to have NA's so they are put out as Alpha's would have quotes...


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
jimster06
Guru
posted Hide Post
Consider using
ON TABLE HOLD FORMAT COMT
hth


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report This Post
susannah
Expert
posted Hide Post
'Hello,bye' is 1 field.
you've defined 1 field, with special characters in it, so what you got is exactly what you asked for.
you can't have a comma in the middle of a single field in a COMT unless its in quotes so that the interpreter knows its 1 field.



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
ChannyS
Guru
posted Hide Post
Susannah,

My question wasn't why 'Hello,Bye' got the quotes - it was why the others didn't when the documentation says all character fields get quotes around them. So when my real field has some data with commas and some without commas, only those values with commas get the quotes. This seems to be inconsistent with the documentation unless I am misunderstanding something?


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
susannah
Expert
posted Hide Post
yes you are misunderstanding something.



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
ChannyS
Guru
posted Hide Post
Considering that the documentation states:

It produces a variable-length text file with fields separated by commas, and with character values enclosed in double quotation marks.

Which part am I misunderstanding?


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
In v7.6.5 and v5.3.4, I get a file as described:

FILEDEF HTEST1 DISK baseapp/htest1.csv
TABLE FILE CAR
PRINT CAR COUNTRY
COMPUTE FIELD_WITH_COMMA/A9 = 'HELLO,BYE';
ON TABLE HOLD AS HTEST1 FORMAT COMT
END


Gives me:

"CAR","COUNTRY","FIELD_WITH_COMMA"
"JAGUAR","ENGLAND","HELLO,BYE"
"JENSEN","ENGLAND","HELLO,BYE"
"TRIUMPH","ENGLAND","HELLO,BYE"
"DATSUN","JAPAN","HELLO,BYE"
"TOYOTA","JAPAN","HELLO,BYE"
"ALFA ROMEO","ITALY","HELLO,BYE"
"MASERATI","ITALY","HELLO,BYE"
"AUDI","W GERMANY","HELLO,BYE"
"BMW","W GERMANY","HELLO,BYE"
"PEUGEOT","FRANCE","HELLO,BYE"


Perhaps a default setting has changed...

This message has been edited. Last edited by: Francis Mariani,


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
ChannyS
Guru
posted Hide Post
Francis,

You get what I would have expected.

I guess it's time for me to open a case....


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
Yes, I'd open a case. It works as expected in v7.6.5, v5.3.4 and v5.3.2.


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
ChannyS
Guru
posted Hide Post
Actually, when I do it as a FILEDEF it works the same way for me. But when I PCHOLD it, then save the file in Excel, that's when I get the problem. Any suggestions?


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
<JG>
posted
quote:
then save the file in Excel, that's when I get the problem. Any suggestions?

Yes, do not save the file in Excel.

It is Excel that is removing the outer double quotes when it does an automatic import of a csv file.
The reason the inner ones remain is because excel does not know how to handle them.

If you want to open the file in Excel them you must use the data/import external data functionality
 
Report This Post
ChannyS
Guru
posted Hide Post
OK - thanks. I'm just trying to figure out now how to make sure this thing doesn't automatically pop up in Excel. (Even though I changed the Windows File Extension association, it is still coming up in Excel).


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
ChannyS
Guru
posted Hide Post
OK - I had to change the rules for .xls - not COMT (thought I can't explain why), and now I get the File Open, Save, Cancel Dialog


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report 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     [SOLVED] Question about COMT

Copyright © 1996-2020 Information Builders