Guru's

I have a freeformat file with closing prices for stocks.
The source (Financial Times) is not willing or not able to close the records with ",$"

How can I read that file?

I created a master like:

FILENAME=fndKoers, SUFFIX=COM    ,
 DATASET=baseapp/koers.txt, $
  SEGMENT=FNDKOERS, SEGTYPE=S1, $
    FIELDNAME=SEDOL, ALIAS=SEDOL, USAGE=A7, ACTUAL=A7, $
    FIELDNAME=BRONID, ALIAS=BRONID, USAGE=A8, ACTUAL=A8, $
    FIELDNAME=ISINCODE, ALIAS=ISINCODE, USAGE=A12, ACTUAL=A12, $
    FIELDNAME=NUL9NUL, ALIAS=NUL9NUL, USAGE=A9, ACTUAL=A9, $
    FIELDNAME=CURRENC, ALIAS=CURRENC, USAGE=A3, ACTUAL=A3, $
    FIELDNAME=CLOSING, ALIAS=CLOSING, USAGE=D9.4, ACTUAL=A9, $
    FIELDNAME=EXTRACODE, ALIAS=EXTRACODE, USAGE=I1, ACTUAL=A1, $
    FIELDNAME=PRICEDATE, ALIAS=PRICEDATE, USAGE=I8YYMD, ACTUAL=A8, $
    FIELDNAME=NAME1, ALIAS=NAME1, USAGE=A18, ACTUAL=A18, $
    FIELDNAME=NAME2, ALIAS=NAME2, USAGE=A27, ACTUAL=A27, $
    FIELDNAME=CODE2, ALIAS=CODE2, USAGE=A2, ACTUAL=A2, $
    FIELDNAME=CODE1, ALIAS=CODE1, USAGE=A1, ACTUAL=A1, $
  

I know it might be a solution to first read the file in an editor and add the ",$" But I was hoping on a more sofisticated solution.

Frank

Frank

Had exactly the same a while back. What I found was that MAINTAIN can read the records without the $ on the end, and with varying length. So a very simple maintain, can read in all these records, as a large alpha, plant a ,$ on the end a write the whole lot back.

The code is on a machine which has currently gone AWOL, so I would ahve to search through back-ups, but could get to it if you are interested.

Could you post a couple of the data lines?

Yes Alan, I'm interested but the problem is we do not have MAINTAIN

Francis

Here are some datalines
as you see, they put commas in it, but made it to a fixed format.
the first two lines could give me some extra problems, as you see here the 'H' in the field and on the second line there is a stockprice that has a picture of D9.2 in stead of D9.4

I will be pleased to see how I (or you) can solf this.

6226576,LIVE    ,CN0009365379,000000000,HKD,   8.6100,9,20070320,PETROCHINA  O     ,'H'CNY1                    ,FH,H
6229597,LIVE    ,JP3967200001,000000000,JPY, 57100.00,9,20070320,RAKUTEN NC        ,NPV                        ,FJ,J
6240156,LIVE    ,AU000000TZL7,000000000,AUD,   0.5450,9,20070320,TZ LIMITED        ,NPV                        ,AA,L
0003128,LIVE    ,GB0000031285,000000000,GBP,   1.9100,9,20070320,ABERDEEN ASSET MGT,ORD GBP0.10                ,EX,L
0028262,LIVE    ,GB0000282623,000000000,GBP,   5.1225,9,20070320,AMEC              ,ORD GBP0.50                ,EX,L
0044473,LIVE    ,GB0000444736,000000000,GBP,   2.3500,9,20070320,EMI GROUP         ,ORD GBP0.14                ,EX,L
0056650,LIVE    ,GB0000566504,000000000,GBP,  10.8000,9,20070320,BHP BILLITON PLC  ,USD0.50                    ,EX,L
0060800,LIVE    ,GB0000608009,000000000,GBP,   9.2950,9,20070320,ATKINS WS         ,ORD GBP0.005               ,EX,L
0081180,LIVE    ,GB0000811801,000000000,GBP,  11.3200,9,20070320,BARRATT DEVEL     ,ORD GBP0.10                ,EX,L
0082923,LIVE    ,IE0000829238,000000000,USD, 673.1400,R,20070316,BARING INTL (IRE) ,HONG KONG CHINA USD        ,EZ,I
0118271,LIVE    ,GB0055007982,000000000,GBP,   6.4250,9,20070320,AUTONOMY CORP     ,ORD GBP0.003333            ,EX,L
0136701,LIVE    ,GB0001367019,000000000,GBP,  15.3700,9,20070320,BRITISH LAND CO   ,ORD GBP0.25                ,EX,L
0138893,LIVE    ,GB0001388932,000000000,GBP,   0.3875,1,20070312,INTECHNOLOGY      ,ORD GBP0.01                ,EX,L
0141192,LIVE    ,GB0001411924,000000000,GBP,   5.5400,9,20070320,BRITISH SKY BROADC,ORD GBP0.50                ,EX,L
0143002,LIVE    ,GB0001430023,000000000,GBP,   5.1100,9,20070320,BRIXTON           ,ORD GBP0.25                ,EX,L
0159247,LIVE    ,GB0001592475,000000000,GBP,   7.3200,9,20070320,CLS HLDGS         ,ORD GBP0.25                ,EX,L

Thanks in advance.

Frank

Oh, that's a pity....

The master is along these lines:

  
FILE=IN_REC, SUFFIX=FIX
SEGNAME=ONE,
FIELD=LINE_RECORD, ,A1000,A1000,$

and the code like this:

FILEDEF IN_REC DISK C:\IN_REC.DAT (LRECL 1000
FILEDEF OUT_REC DISK C:\OUT_REC.DAT (LRECL 1000
MAINTAIN FILE IN_REC
DECLARE NEWLINE/A0;
FOR ALL NEXT IN_REC.ONE.LINE_RECORD INTO LINESTACK;
REPEAT LINESTACK.FOCCOUNT; CNTR/I4=1;
NEWLINE = LINESTACK(CNTR).LINE_RECORD ||',$';
TYPE ON OUT_REC "<NEWLINE";
ENDREPEAT CNTR=CNTR+1;
END

Just note the use of the 1000 field length in master and LRECL, this can be reduced but keep all in line.

Since the file looks like it has fixed width columns, I would create a Master with one column and use DEFINE to separate the text into individual columns. You could have the DEFINEs in the Master.

SUBSTR into an alpha field then convert from alpah to numeric where necessary.

Frank

This isn't complex at all. You should be able to TABLE this lot out straight off with your master.

Don't forget to SET PCOMMA=ON

Since it's just a flat file with fixed format, why not just create the Master that can read it directly. For example, this seems to read the sample data just fine:

FILE=data, SUFFIX=FIX, DATASET='C:\ibi\apps\baseapp\data.ftm'
SEGNAME=data, SEGTYPE=S0, $
FIELD=FIELD1, ALIAS=FIELD1, USAGE=A7, ACTUAL=A7, $
FIELD=COMMA1, COMMA1,A1,A1,$
FIELD=FIELD2, ALIAS=FIELD2, USAGE=A8, ACTUAL=A8, $
FIELD=COMMA2, COMMA2,A1,A1,$
FIELD=FIELD3, ALIAS=FIELD3, USAGE=A12, ACTUAL=A12, $
FIELD=COMMA3, COMMA3,A1,A1,$
FIELD=FIELD4, ALIAS=FIELD4, USAGE=A9, ACTUAL=A9, $
FIELD=COMMA4, COMMA4,A1,A1,$
FIELD=FIELD5, ALIAS=FIELD5, USAGE=A3, ACTUAL=A3, $
FIELD=COMMA5, COMMA5,A1,A1,$
FIELD=FIELD6, ALIAS=FIELD6, USAGE=D12.4, ACTUAL=A09, $
FIELD=COMMA6, COMMA6,A1,A1,$
FIELD=FIELD7, ALIAS=FIELD7, USAGE=A1, ACTUAL=A1, $
FIELD=COMMA7, COMMA7,A1,A1,$
FIELD=FIELD8, ALIAS=FIELD8, USAGE=A08YYMD, ACTUAL=A08, $
FIELD=COMMA8, COMMA8,A1,A1,$
FIELD=FIELD9, ALIAS=FIELD9, USAGE=A18, ACTUAL=A18, $
FIELD=COMMA9, COMMA9,A1,A1,$
FIELD=FIELD10, ALIAS=FIELD10, USAGE=A27, ACTUAL=A27, $
FIELD=COMMA10, COMMA10,A1,A1,$
FIELD=FIELD11, ALIAS=FIELD11, USAGE=A2, ACTUAL=A2, $
FIELD=COMMA11, COMMA11,A1,A1,$
FIELD=FIELD12, ALIAS=FIELD12, USAGE=A1, ACTUAL=A1, $

Alan

We don't have the maintain functionality and to buy that just for one file is a bit too far.

Darin

What you suggest could work, but my problem is the lack of the ending ",$" in the file.
My master almost works perfect when and if I add the dollar sign in the file.

Leave the data in the file as it is, would be the best, since it than can be replaced during the day at any time without let me do anything.

I think I will try to convince FT to add the "$" ...

Frank

Frank

You do not need the ,$. Honestly, just try it, run a TABLE request.

This is format FIX so it does not require the ,$ like format COM does.

Alan, Darin, Francis

I will give it a try tomorrow since I'm a t home now and work should be finished now, time for the family.....

Frank