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     Date format on mas of flat file [Closed]

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Date format on mas of flat file [Closed]
 Login/Join
 
Geri
Platinum Member
posted
I have read on the forum and tried many variations of dates for the MFD of my flat file but with no success. A portion of the flat file and the mas is below. Any help would be appreciated.

mas file:
FILE=DATETBL, SUFFIX=FIX, $


SEGNAME=DATETBL, SEGTYPE=S0, $

FIELDNAME=ORDNMBR, ALIAS=E01, USAGE=P17, ACTUAL=A10, $

FIELDNAME=DTEA01, ALIAS=E02, USAGE=MDY, ACTUAL=A8, $
FIELDNAME=DTEA02, ALIAS=E03, USAGE=MDY, ACTUAL=A8, $

flat file:
9219 12/31/2010 0:00:00 12/31/2010 0:00:00
19065 7/14/2010 0:00:00 7/14/2010 0:00:00
201206 12/11/2008 0:00:00 12/11/2008 0:00:00
201222 7/29/2009 0:00:00 7/29/2009 0:00:00
18504 9/30/2010 0:00:00 9/30/2010 0:00:00
18811 7/23/2010 0:00:00 7/23/2010 0:00:00
20182 9/21/2010 0:00:00 9/21/2010 0:00:00
14925 12/30/2009 0:00:00 12/30/2009 0:00:00
201866 11/1/2010 0:00:00 11/1/2010 0:00:00
19771 3/1/2009 0:00:00 3/1/2009 0:00:00
208712 5/20/2010 0:00:00 5/20/2010 0:00:00
208711 8/31/2010 0:00:00 8/31/2010 0:00:00
209340 7/5/2010 0:00:00 7/5/2010 0:00:00
209341 2/18/2011 0:00:00 2/18/2011 0:00:00
209342 9/30/2010 0:00:00 9/30/2010 0:00:00
209359 11/29/2011 0:00:00 11/29/2011 0:00:00
209394 12/11/2010 0:00:00 12/11/2010 0:00:00
209397 6/11/2010 0:00:00 6/11/2010 0:00:00
209401 10/1/2010 0:00:00 10/1/2010 0:00:00


With 2 date columns, the following fail:
USAGE=MDYY, ACTUAL=A10
USAGE=YYMD, ACTUAL=A10
USAGE=MDYY, ACTUAL=DATE
USAGE=YYMD, ACTUAL=DATE
Adding s or H to the Usage fails also.

If I go with just the first column and a single date column, this will display correctly. With 2 date columns, this fails. As a test, I exported the exact same date column twice and it fails where it does not fail with a single date column only. I actually have 8 date columns so this is a problem. The goal is a date field only although 1 other date field in the full file must display a date also.

error: (for each line in the report)
(FOC209) THE DATA VALUE EXCEEDS ITS LENGTH SPECIFICATION:
(FOC1346) : FORMAT ERROR: Record 1 , Column 20
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : DTEA02
(FOC1346) : FORMAT ERROR: Record 1 , Column 0
(FOC1346) : FORMAT ERROR: Record 1 , Column 12
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : PRJNMBR
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : DTEA01
(FOC1346) : FORMAT ERROR: Record 2 , Column 20
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : DTEA02


Thank you,
Geri

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



Prod: WebFOCUS 7.7.05 OS:Linux; Upgrading to: WebFOCUS 8.1.05 OS:Windows; Outputs: HTML, PDF, Excel; Adapters: SAP, MySQL, Oracle incl Report Caster
 
Posts: 102 | Location: Cincinnati, Oh USA area | Registered: November 02, 2006Report This Post
<JG>
posted
quote:
12/31/2010 0:00:00

you don't have date fields, you have alpha fields, 'A18'.

Create them in the MFD as ALPAH and if you want to use them as dates and not just for display
add a define to the master using the correct alpha to date functions.
 
Report This Post
Dan Satchell
Virtuoso
posted Hide Post
If you want to use SUFFIX=FIX in your master file description, the data must line-up in straight columns. You also need to account for every character in the flat file, including the time columns and spaces between the columns of data. Notice below that I describe TIME01 with a USAGE of A08 but an ACTUAL of A09. The A09 accounts for the spaces on either side of the TIME01 column.

FILEDEF DATATBL DISK DATATBL.TXT
-RUN

-WRITE DATATBL 9219   12/31/2010 0:00:00 12/31/2010 0:00:00 
-WRITE DATATBL 19065   7/14/2010 0:00:00  7/14/2010 0:00:00 
-WRITE DATATBL 201206 12/11/2008 0:00:00 12/11/2008 0:00:00 
-WRITE DATATBL 201222  7/29/2009 0:00:00  7/29/2009 0:00:00 
-WRITE DATATBL 18504   9/30/2010 0:00:00  9/30/2010 0:00:00 
-WRITE DATATBL 18811   7/23/2010 0:00:00  7/23/2010 0:00:00 
-WRITE DATATBL 20182   9/21/2010 0:00:00  9/21/2010 0:00:00 
-WRITE DATATBL 14925  12/30/2009 0:00:00 12/30/2009 0:00:00 
-WRITE DATATBL 201866 11/01/2010 0:00:00 11/01/2010 0:00:00 
-WRITE DATATBL 19771   3/01/2009 0:00:00  3/01/2009 0:00:00 
-WRITE DATATBL 208712  5/20/2010 0:00:00  5/20/2010 0:00:00 
-WRITE DATATBL 208711  8/31/2010 0:00:00  8/31/2010 0:00:00 
-WRITE DATATBL 209340  7/05/2010 0:00:00  7/05/2010 0:00:00 
-WRITE DATATBL 209341  2/18/2011 0:00:00  2/18/2011 0:00:00 
-WRITE DATATBL 209342  9/30/2010 0:00:00  9/30/2010 0:00:00 
-WRITE DATATBL 209359 11/29/2011 0:00:00 11/29/2011 0:00:00 
-WRITE DATATBL 209394 12/11/2010 0:00:00 12/11/2010 0:00:00 
-WRITE DATATBL 209397  6/11/2010 0:00:00  6/11/2010 0:00:00 
-WRITE DATATBL 209401 10/01/2010 0:00:00 10/01/2010 0:00:00 
-RUN

FILEDEF MASTER DISK DATATBL.MAS
-RUN

-WRITE MASTER FILE=DATETBL, SUFFIX=FIX, $
-WRITE MASTER DATASET=DATATBL.TXT, $
-WRITE MASTER SEGNAME=DATETBL, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=ORDNMBR, ALIAS=E01, USAGE=P17 , ACTUAL=A7, $
-WRITE MASTER FIELDNAME=DATE01 , ALIAS=E02, USAGE=MDYY, ACTUAL=A10, $
-WRITE MASTER FIELDNAME=TIME01 , ALIAS=E03, USAGE=A08 , ACTUAL=A09, $
-WRITE MASTER FIELDNAME=DATE02 , ALIAS=E04, USAGE=MDYY, ACTUAL=A10, $
-WRITE MASTER FIELDNAME=TIME02 , ALIAS=E05, USAGE=A08 , ACTUAL=A08, $
-RUN

TABLE FILE DATATBL
PRINT *
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Geri
Platinum Member
posted Hide Post
The mas file did not paste well into this page but the columns are straight up and down and all begin at the left side of the beginning of each column. They end differently. The next column again begins straight up and down. They are dates in the flat file. However, they are not full 8/10 charater dates. Feb 2 shows as 2/2/2010 rather than 02/02/2010.

Because these are dates without a consistent length, when creating the file as a text file, it causes the data to go to columns inconsistently. I prefer to read the files as dates for that reason if possible.

I am pasting below as it displays on the flat file and each column begins the same. Let's see how it pastes here.
19065 7/14/2010 0:00:00 11/7/2008 0:00:00
9219 12/31/2010 0:00:00 12/31/2010 0:00:00
201222 7/29/2009 0:00:00 7/29/2009 0:00:00
201206 12/11/2008 0:00:00 12/11/2008 0:00:00
18504 9/30/2010 0:00:00 9/30/2010 0:00:00
18811 7/23/2010 0:00:00 4/28/2009 0:00:00

This like shows the error below
USAGE=MDYY, ACTUAL=A10, $

(FOC209) THE DATA VALUE EXCEEDS ITS LENGTH SPECIFICATION:
(FOC1346) : FORMAT ERROR: Record 1 , Column 22
(FOC1346) : FORMAT ERROR: Record 1 , Column 42
(FOC1346) : FORMAT ERROR: Record 1 , Column 62


Normally, this file will have 3500 lines and 50 columns. For that reason, I cannot do the write to the create the txt file. We will send this file to the server and read the results of the file on a regular basis.

Thank you,
Geri
 
Posts: 102 | Location: Cincinnati, Oh USA area | Registered: November 02, 2006Report This Post
GinnyJakes
Expert
posted Hide Post
Geri, I think you are not going to be able to. You will have to define both the usage and actual formats as alpha. You can add DEFINE fields to the master to parse the input date fields and make them into Smart Dates.

Sorry.


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
GamP
Virtuoso
posted Hide Post
Example of this, building on Ginnys code:
FILEDEF DATATBL DISK DATATBL.TXT
-RUN
-WRITE DATATBL 9219   12/31/2010 0:00:00 12/31/2010 0:00:00 
-WRITE DATATBL 19065  7/14/2010 0:00:00  7/14/2010 0:00:00 
-WRITE DATATBL 201206 12/11/2008 0:00:00 12/11/2008 0:00:00 
-WRITE DATATBL 201222 7/29/2009 0:00:00  7/29/2009 0:00:00 
-WRITE DATATBL 18504  9/30/2010 0:00:00  9/30/2010 0:00:00 
-WRITE DATATBL 18811  7/23/2010 0:00:00  7/23/2010 0:00:00 
-WRITE DATATBL 20182  9/21/2010 0:00:00  9/21/2010 0:00:00 
-WRITE DATATBL 14925  12/30/2009 0:00:00 12/30/2009 0:00:00 
-WRITE DATATBL 201866 11/1/2010 0:00:00  11/1/2010 0:00:00 
-WRITE DATATBL 19771  3/1/2009 0:00:00   3/1/2009 0:00:00 
-WRITE DATATBL 208712 5/20/2010 0:00:00  5/20/2010 0:00:00 
-WRITE DATATBL 208711 8/31/2010 0:00:00  8/31/2010 0:00:00 
-WRITE DATATBL 209340 7/5/2010 0:00:00   7/5/2010 0:00:00 
-WRITE DATATBL 209341 2/18/2011 0:00:00  2/18/2011 0:00:00 
-WRITE DATATBL 209342 9/30/2010 0:00:00  9/30/2010 0:00:00 
-WRITE DATATBL 209359 11/29/2011 0:00:00 11/29/2011 0:00:00 
-WRITE DATATBL 209394 12/11/2010 0:00:00 12/11/2010 0:00:00 
-WRITE DATATBL 209397 6/11/2010 0:00:00  6/11/2010 0:00:00 
-WRITE DATATBL 209401 10/1/2010 0:00:00  10/1/2010 0:00:00 
-RUN

FILEDEF MASTER DISK DATATBL.MAS
-RUN

-WRITE MASTER FILE=DATETBL, SUFFIX=FIX, $
-WRITE MASTER DATASET=DATATBL.TXT, $
-WRITE MASTER SEGNAME=DATETBL, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=ORDNMBR, ALIAS=E01, USAGE=P17, ACTUAL=A7 , $
-WRITE MASTER FIELDNAME=DATE01 , ALIAS=E02, USAGE=A18, ACTUAL=A18, $
-WRITE MASTER FIELDNAME=DUMMY  , ALIAS=E03, USAGE=A1 , ACTUAL=A1 , $
-WRITE MASTER FIELDNAME=DATE02 , ALIAS=E04, USAGE=A18, ACTUAL=A18, $
-WRITE MASTER DEFINE DATEPART1/YYMD = HDATE(HINPUT(18,DATE01,8,'HMDYYS'),DATEPART1);, $
-WRITE MASTER DEFINE DATEPART2/YYMD = HDATE(HINPUT(18,DATE02,8,'HMDYYS'),DATEPART2);, $
-RUN

TABLE FILE DATATBL
PRINT DATE01 DATEPART1 DATE02 DATEPART2
BY ORDNMBR
END

Hope this helps ...


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
quote:
I cannot do the write to the create the txt file
Geri, just wanted to let you know that these -WRITE statements are only for demonstration purposes - it's the quickest way for someone to create an example fex that can be run on almost anybody's WF environment. You wouldn't have to -WRITE the file, nor the Master.


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
Dan Satchell
Virtuoso
posted Hide Post
If your data looks like this...

9219   12/31/2010 0:00:00 12/31/2010 0:00:00 
19065  7/14/2010  0:00:00 7/14/2010  0:00:00 
201206 12/11/2008 0:00:00 12/11/2008 0:00:00 
201222 7/29/2009  0:00:00 7/29/2009  0:00:00 
18504  9/30/2010  0:00:00 9/30/2010  0:00:00 
18811  7/23/2010  0:00:00 7/23/2010  0:00:00 
20182  9/21/2010  0:00:00 9/21/2010  0:00:00 
14925  12/30/2009 0:00:00 12/30/2009 0:00:00 
201866 11/1/2010  0:00:00 11/1/2010  0:00:00 
19771  3/1/2009   0:00:00 3/1/2009   0:00:00 
208712 5/20/2010  0:00:00 5/20/2010  0:00:00 
208711 8/31/2010  0:00:00 8/31/2010  0:00:00 
209340 7/5/2010   0:00:00 7/5/2010   0:00:00 
209341 2/18/2011  0:00:00 2/18/2011  0:00:00 
209342 9/30/2010  0:00:00 9/30/2010  0:00:00 
209359 11/29/2011 0:00:00 11/29/2011 0:00:00 
209394 12/11/2010 0:00:00 12/11/2010 0:00:00 
209397 6/11/2010  0:00:00 6/11/2010  0:00:00 
209401 10/1/2010  0:00:00 10/1/2010  0:00:00

...then the master description I suggested in my earlier post should work...

FILE=DATETBL, SUFFIX=FIX, $
SEGNAME=DATETBL, SEGTYPE=S0, $
FIELDNAME=ORDNMBR, ALIAS=E01, USAGE=P17 , ACTUAL=A7, $
FIELDNAME=DATE01, ALIAS=E02 , USAGE=MDYY, ACTUAL=A10, $
FIELDNAME=TIME01, ALIAS=E03 , USAGE=A08 , ACTUAL=A09, $
FIELDNAME=DATE02, ALIAS=E04 , USAGE=MDYY, ACTUAL=A10, $
FIELDNAME=TIME02, ALIAS=E05 , USAGE=A08 , ACTUAL=A08, $

But if your data looks like this...

19065 7/14/2010 0:00:00 11/7/2008 0:00:00
9219 12/31/2010 0:00:00 12/31/2010 0:00:00
201222 7/29/2009 0:00:00 7/29/2009 0:00:00
201206 12/11/2008 0:00:00 12/11/2008 0:00:00
18504 9/30/2010 0:00:00 9/30/2010 0:00:00
18811 7/23/2010 0:00:00 4/28/2009 0:00:00

...then SUFFIX=FIX will not work.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Geri
Platinum Member
posted Hide Post
I will try Dan's first suggestion and let you know. Dan for your last note, my data looks like your first example.

Geri



Prod: WebFOCUS 7.7.05 OS:Linux; Upgrading to: WebFOCUS 8.1.05 OS:Windows; Outputs: HTML, PDF, Excel; Adapters: SAP, MySQL, Oracle incl Report Caster
 
Posts: 102 | Location: Cincinnati, Oh USA area | Registered: November 02, 2006Report This Post
Geri
Platinum Member
posted Hide Post
Unfortunately, none of the suggestions worked for my situation. I found a workaround that will be used.



Prod: WebFOCUS 7.7.05 OS:Linux; Upgrading to: WebFOCUS 8.1.05 OS:Windows; Outputs: HTML, PDF, Excel; Adapters: SAP, MySQL, Oracle incl Report Caster
 
Posts: 102 | Location: Cincinnati, Oh USA area | Registered: November 02, 2006Report This Post
GamP
Virtuoso
posted Hide Post
Would you be so kind to inform us what the workaround is? I bet we're all very curious to see the solution you found!


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Tony A
Expert
posted Hide Post
Here is one solution that would give you what you need. I had to edit the HTML source of this page to get your underlying data format so hopefully I have the correct format.

The first part of the code builds a master file in memory but you could easily build a permanent master file. It has a few defined fields within it to get the timestamps from the data, but it does work.

The second part just loads your exact data in to give me some sample data to test upon.

EX -LINES 7 EDAPUT MASTER,DATAFILE,CF,MEM,FILENAME=DATAFILE, SUFFIX=TAB,$
SEGNAME=ONE, SEGTYPE=S0 ,$
  FIELD=DATALN   , ,A100  ,A100V , $
  DEFINE ORDER_NO/P17   = EDIT(EDIT(DATALN,'9999999999999999'));
  DEFINE DATES/A80      = SUBSTR(100, DATALN, 17, 100, 84, 'A80');
  DEFINE HDATE01/HYYMDS = HINPUT(19, GETTOK(DATES, 80, 1, ' ', 10, 'A10') || (' ' | GETTOK(DATES, 80, 2, ' ', 10, 'A8')), 8, HDATE01);
  DEFINE HDATE02/HYYMDS = HINPUT(19, GETTOK(DATES, 80, 3, ' ', 10, 'A10') || (' ' | GETTOK(DATES, 80, 4, ' ', 10, 'A8')), 8, HDATE01);
-RUN
FILEDEF DATAFILE DISK DATAFILE.FTM
-RUN
-WRITE DATAFILE 9219            12/31/2010 0:00:00 12/31/2010 0:00:00 
-WRITE DATAFILE 19065           7/14/2010 0:00:00 7/14/2010 0:00:00 
-WRITE DATAFILE 201206          12/11/2008 0:00:00 12/11/2008 0:00:00 
-WRITE DATAFILE 201222          7/29/2009 0:00:00 7/29/2009 0:00:00 
-WRITE DATAFILE 18504           9/30/2010 0:00:00 9/30/2010 0:00:00 
-WRITE DATAFILE 18811           7/23/2010 0:00:00 7/23/2010 0:00:00 
-WRITE DATAFILE 20182           9/21/2010 0:00:00 9/21/2010 0:00:00 
-WRITE DATAFILE 14925           12/30/2009 0:00:00 12/30/2009 0:00:00 
-WRITE DATAFILE 201866          11/1/2010 0:00:00 11/1/2010 0:00:00 
-WRITE DATAFILE 19771           3/1/2009 0:00:00 3/1/2009 0:00:00 
-WRITE DATAFILE 208712          5/20/2010 0:00:00 5/20/2010 0:00:00 
-WRITE DATAFILE 208711          8/31/2010 0:00:00 8/31/2010 0:00:00 
-WRITE DATAFILE 209340          7/5/2010 0:00:00 7/5/2010 0:00:00 
-WRITE DATAFILE 209341          2/18/2011 0:00:00 2/18/2011 0:00:00 
-WRITE DATAFILE 209342          9/30/2010 0:00:00 9/30/2010 0:00:00 
-WRITE DATAFILE 209359          11/29/2011 0:00:00 11/29/2011 0:00:00 
-WRITE DATAFILE 209394          12/11/2010 0:00:00 12/11/2010 0:00:00 
-WRITE DATAFILE 209397          6/11/2010 0:00:00 6/11/2010 0:00:00 
-WRITE DATAFILE 209401          10/1/2010 0:00:00 10/1/2010 0:00:00 
-RUN
TABLE FILE DATAFILE
PRINT HDATE01
      HDATE02
   BY ORDER_NO
END

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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Date format on mas of flat file [Closed]

Copyright © 1996-2020 Information Builders