Hi
Is there any way to READ data from external files excel or csv and use them inside webfocus without using master files.i.e. avoiding creationof master files

No...

How does your system know what you are reading?
If you use an other language like .Net or VB you have to do that defines too.
So what do you expect? Magic?

If they were flat or sequential, i.e. csv, you could use Dialog Manager to read them but you couldn't do a report. If that is your goal, you are out of luck.

Of course, with Dialog Manager you could also write a temporary master to read the file.

Whenever I need read comma or delimited files from an outside source I use the following master. You can adjust to your record length.

FILENAME=hb1143data,SUFFIX=FIX, $
SEGNAME=SEG1 ,$
  FIELD=LDATA,,A160,A160, $

Then I use something like the following to "parse" the data.

 FILEDEF HB1143DATA DISK &TMPDIR..hb1143_0708.txt ( LRECL 160 RECFM V
-*
 DEFINE FILE HB1143DATA
   TAB/A1=HEXBYT(9,'A1');
-*
   WID/A4=GETTOK(LDATA,160,1,TAB,4,WID);
   ROWID/I4S=EDIT(WID);
-*
   F3LST/A3=GETTOK(LDATA,160,2,TAB,3,F3LST);
-*
   SSN/A9=GETTOK(LDATA,160,3,TAB,9,SSN);
-*
   ACASE1/A8=GETTOK(LDATA,160,4,TAB,8,ACASE1);
   CASE1/I8=EDIT(ACASE1);
-*
   ACASE2/A8=GETTOK(LDATA,160,5,TAB,8,ACASE2);
   CASE2/I8=EDIT(ACASE2);
-*
   ETHNIC/A3=GETTOK(LDATA,160,6,TAB,3,ETHNIC);
-*
   GNDR/A1=GETTOK(LDATA,160,7,TAB,1,GNDR);
-*
   WDOB/A10=GETTOK(LDATA,160,8,TAB,10,WDOB);
   ADOB/A8YYMD=EDIT(WDOB,'9999$99$99');
   CLDOB/YYMD=DATECVT(ADOB,'A8YYMD','YYMD');
-*
   MATCH_KEY/A13=
     IF F3LST NE ' ' AND GNDR NE ' ' AND ADOB NE ' '
       THEN F3LST | GNDR | ADOB ELSE ' ';
 END
-*
 TABLEF FILE HB1143DATA
  PRINT ROWID CASE1 CASE2 SSN MATCH_KEY
     IF ROWID GT 0
     ON TABLE HOLD
 END

In this case, the data was tab delimited, but you can do the same thing with comma delimited data.

the key point being that you still MUST have a master file, but this is an excellent and easy way to pull in data where layouts and formats may vary. You just have to write defines for every field. And if you have to do it over and over, you may as well create an MFD for the file you are trying to read. MFD is simpler than all those defines and functions, but user has more control with defines.

I have written MFD's but facing some problem here
I will explain at my level best.I have 2 csv files.so i created 2 master files.I am able to read them in webfocus and display them.I have 5 fields in my csv file out of which all 5 fields are key fields which are to be checked for mis-matches between 2 csv files.I have used MATCH FILE concept here to create oen single HOLD file using OLD-NOR-NEW since i need only the mismatches as output in my final output.
I am posting my 2 master files and fex code here for your reference.
Please let me know what changes i need to make and where i have gone wrong.
::Master file1::
FILE=qmtrade1, SUFFIX=COM
SEGNAME=QMTRADE1, SEGTYPE=S1, $
FIELD=MMRefNumber, ALIAS=M01, USAGE=A40, ACTUAL=A40, $
FIELD=TradeDate, ALIAS=M02, USAGE=MDY, ACTUAL=DATE, $
FIELD=Portfolio, ALIAS=M03, USAGE=A40, ACTUAL=A40, $
FIELD=NotionalAmt, ALIAS=M04, USAGE=P16.2C, ACTUAL=P16, $
FIELD=TradeCounterParty, ALIAS=M05, USAGE=A40, ACTUAL=A40, $
FIELD=AssetSubType, ALIAS=M06, USAGE=A40, ACTUAL=A40, $
FIELD=AssetType, ALIAS=M07, USAGE=A40, ACTUAL=A40, $

::Master file2::
FILENAME=qmtrade3,SUFFIX=COM
SEGNAME=QMTRADE3, SEGTYPE=S1, $
FIELD=Number, ALIAS=M001, USAGE=A40, ACTUAL=A40, $
FIELD=Date, ALIAS=M002, USAGE=MDY, ACTUAL=DATE, $
FIELD=Portf, ALIAS=M003, USAGE=A40, ACTUAL=A40, $
FIELD=Notional, ALIAS=M004, USAGE=P16.2C, ACTUAL=P16, $
FIELD=CounterParty, ALIAS=M005, USAGE=A40, ACTUAL=A40, $
FIELD=ASubType, ALIAS=M006, USAGE=A40, ACTUAL=A40, $
FIELD=AType, ALIAS=M007, USAGE=A40, ACTUAL=A40, $


::FEX code::
-* File qmtrade3csv.fex
FILEDEF QMTRADE1 DISK \\IMNT14\SHARED\TRANSFER\NEW_TRADE1.CSV
FILEDEF QMTRADE3 DISK \\IMNT14\SHARED\TRANSFER\NEW_TRADE2.CSV
SET ASNAMES=ON
SET NULL=ON
TABLE FILE QMTRADE1
PRINT
AssetSubType
AssetType
BY MMRefNumber
BY TradeDate
BY Portfolio
BY NotionalAmt
BY TradeCounterParty
ON TABLE HOLD AS QMTRADEONE
END
-RUN
-*
TABLE FILE QMTRADE3
PRINT
ASubType
AType
BY Number
BY Date
BY Portf
BY Notional
BY CounterParty
ON TABLE HOLD AS QMTRADEThree
END
-RUN
-*
MATCH FILE QMTRADEONE
PRINT
AssetSubType
AssetType
BY MMRefNumber
BY TradeDate
BY Portfolio
BY NotionalAmt
BY TradeCounterParty
RUN
FILE QMTRADEThree
SUM
ASubType
AType
BY Number AS NUM
BY Date AS DAT
BY Portf AS PORT
BY Notional AS NOTI
BY CounterParty AS CPTY
AFTER MATCH HOLD OLD-NOR-NEW
END
-RUN
-*
DEFINE FILE HOLD
DELIM/A1 = '|';
END
-RUN
SET NODATA=' '
-*
TABLE FILE HOLD
HEADING
"COMPARE OF TWO CSV FILES"
"---- FILE1 ---------------------------- <65 -----------------------FILE2 --------"
"PRINT
MMRefNumber AS 'MM,Ref,Number'
TradeDate AS 'Trade,Date'
Portfolio AS 'Port,folio'
NotionalAmt AS 'Notional,Amt'
TradeCounterParty AS 'Trade,Counter,Party'
AssetSubType AS 'Asset,Sub,Type'
AssetType AS 'Asset,Type'
DELIM AS ' ' IN +4
NUM
DAT
PORT
NOTI
CPTY
ASubType
AType
BY MMRefNumber NOPRINT
BY TradeDate NOPRINT
BY Portfolio NOPRINT
BY NotionalAmt NOPRINT
BY TradeCounterParty NOPRINT
BY NUM NOPRINT
BY DAT NOPRINT
BY PORT NOPRINT
BY NOTI NOPRINT
BY CPTY NOPRINT
ON TABLE NOTOTAL
etc etc....

Umar

You are confusing us with this question, you ahve posted the same question in several discussions here.
It would be better to post it once and if that problem is solved NOT posting a new question in that same discussion, but start a new one.

Now, you start with two comma delimited files, do a query on them and hold them in two hold files.

That is the first error.

You now in fact created two new flat files.

Change the two master description so the fieldnames are the same for the keyfields, and the keep the names short as possible and not longer than 12 characters.
The two datafields should be different.

I would hold them in a Focus format file with indexes and the by statements are in fact not necessary.

TABLE FILE QMTRADE1
PRINT
ASubType
AType
Number
Date
Portf
Notional
CounterParty
ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX NUMBER DATE PORTF NOTIONAL COUNTERPARTY
END
TABLE FILE QMTRADE3
PRINT
BSubType
BType
Number
Date
Portf
Notional
CounterParty
ON TABLE HOLD AS HOLD2 FORMAT FOCUS INDEX NUMBER DATE PORTF NOTIONAL COUNTERPARTY
END

The run statements are not needed.

Now you can start with the match statements.

In the MATCH part you start with a PRINT fieldnames BY indexed fields.
the second part should be the same, so not a SUM.

Ad the end you should say

AFTER MATCH HOLD OLD-AND-NEW

Then you will have a combined dataset with the 5 keyfields and the 4 datafields

key1 key2 key3 key4 key5 data1 data2 data3 data4 
one  xxx  aaa  bbb  123  aab   006   aab   006
for  xxy  aad  bbp  125  aacb  007   aacb  006
two  xpp  aaa  bb3  127              abx   781
five xxp  bsa  axd  120  xxx   781

So the first record is the same
in the second data2 and data4 is different
the 3th record has no data from the first file
and the 4th record has no data in the second file.

You can build a query on that set of data to give you all kind of errors.

I hope this will help.

Hi Frank

I like to know why i cannot use OLD-NOR-NEW
which will give me the mismatches when i need only mismatches between the two files.
So that i can HOLD it and PRINT the HOLD file for report output.

Please advice .

Umar

just start with following my advices and you can then create the reports you need.
the hold files need to be focus format.

TABLE FILE HOLD
PRINT *
WHERE data1 NE data3 OR data2 NE data4;
END

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

Thanks Very Much for your help Frank.
It worked fine.
I like to have one more clarification.
1.If the input files have 10 fields and i need only 5 in my report do i need to include them while i create MASTER.

Yes and No

You have to describe the whole string, so if the first 50 characters are not needed, you have to define them as a filler of A50 and you omit that field in your query.

If your master describes a SQL datasource you can describe only the 5 fields you need and leave the rest out of it.

Hi Frank,
Sorry to come back again on the code.
I first tried with 5 to 10 rows of data and it worked fine.
But when i give the whole CSV file as input it creates problem again.
Even if the row matches in both files it showed as mis-matched row.I do not know why a zero value is getting displayed for a field.I hope if u have a look at the code and output you can say whats the problem.But i donot know how to attach files here in forum.

you can send me the whole FEX code you have created and the two master files and the CSV files, but it will take some time till I come back on this.
I will be out for a two week holiday till the 1st of October.

send it to frank.terlien@gmail.com