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
September 10, 2007, 04:05 PM
FrankDutch
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?
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
September 10, 2007, 06:10 PM
GinnyJakes
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.
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.
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
September 14, 2007, 01:26 PM
Darin Lee
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.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
September 14, 2007, 01:33 PM
<Umar Farook S>
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, $
::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....
September 14, 2007, 03:35 PM
FrankDutch
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
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.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
September 14, 2007, 03:59 PM
<Umar Farook S>
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 .
September 14, 2007, 04:07 PM
FrankDutch
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,
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
September 17, 2007, 02:18 PM
<Umar Farook S>
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.
September 17, 2007, 03:35 PM
FrankDutch
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.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
September 17, 2007, 04:03 PM
<Umar Farook S>
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.
September 17, 2007, 05:16 PM
FrankDutch
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
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7