I have a web interface that accepts multi-row adhoc data, mostly pasted from Excel by the users. A webfocus program then processes and loads the data into the database.

Currently I am using -WRITE within a loop to manually create the webfocus dataset, and it's working fine. Is there a better way, or a standard webfocus command that would create dataset off adhoc data?

Thanks!

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

Do you mean like this?

Note: the create file, will wipe the data each time, but comment that will only append new data too it.

-SET &FN = 'foccache/TestFile';

-*Create new db from sample db source.  Not using any columns from the sample source, we will
-*generate new columns based on computes.  The SET HOLDLIST=PRINTONLY above will ignore all NOPRINT columns.
-*NOTE: BY CAR NOPRINT is required, because at least 1 column must be within the query or it will error.  :(
-TYPE &FNSource was found and will be used to generate new DB - &FN.EVAL.foc
TABLE FILE CAR
BY	COMPUTE SOFTWAREVERSION/D9.3 = 1.000;
BY	COMPUTE RELEASEDATE/M-D-YY = '01-01-2015';
BY	COMPUTE ENVIRONMENT/A50V = 'Development';
BY	CAR NOPRINT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS &FN FORMAT XFOCUS INDEX SOFTWAREVERSION
END

-FOUND
-*CREATE FILE will wipe all data from exists db, while MODIFY FILE populates the db with information stored in DATA.
-TYPE Generating data for - &FN.EVAL.foc
CREATE FILE &FN
MODIFY FILE &FN
FREEFORM SOFTWAREVERSION RELEASEDATE ENVIRONMENT
DATA
1.002,'01-01-2015','Development',$
1.003,'02-01-2015','Development',$
1.004,'03-01-2015','Development',$
1.005,'04-01-2015','Development',$
1.006,'05-01-2015','Development',$
END

-*Return all data from table.
TABLE FILE &FN
PRINT
     *
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END
-RUN

This is it! I remember seeing "CREATE" and "FREEFORM" somewhere, and this is a good example.

One more question: is it possible to specify the data type and/or create a MASTER FILE on the fly (in the fex program)? Here it seems the master file was created during the TABLE FILE CAR query.

Thanks Gavin!

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

Only if you run the TABLE FILE CAR query again. It will override the existing table if exists.

Another option is to build a master description, either permanently or in-line in the focexec, to read your adhoc data. Then simply use a TABLE FILE with the HOLD command below to create the FOCUS file in the desired app folder. No MODIFY is necessary and this will always overwrite any existing FOCUS file and its master description.

ON TABLE HOLD AS <appname>/<filename> FORMAT FOCUS/XFOCUS INDEX <fieldname>

Hi Dan, can you elaborate how to build a master description on the fly? Thanks.

quote:

Originally posted by Dan Satchell:
Another option is to build a master description, either permanently or in-line in the focexec, to read your adhoc data. Then simply use a TABLE FILE with the HOLD command below to create the FOCUS file in the desired app folder. No MODIFY is necessary and this will always overwrite any existing FOCUS file and its master description.

ON TABLE HOLD AS <appname>/<filename> FORMAT FOCUS/XFOCUS INDEX <fieldname>

Try this sample:

TABLE FILE CAR
SUM DEALER_COST/P8.2C AS 'TOTAL_DEALER_COST'
    RETAIL_COST/P8.2C AS 'TOTAL_RETAIL_COST'
BY COUNTRY AS 'BY_COUNTRY'
BY CAR     AS 'BY_CAR_NAME'
BY MODEL   AS 'BY_CAR_MODEL'
ON TABLE HOLD AS BASEAPP/NEWMASTER FORMAT FOCUS INDEX COUNTRY CAR MODEL
END

It generates a new master file called NEWMASTER having the same name and definition as describe in the TABLE FILE code.

NEWMASTER.mas definition:

FILENAME=NEWMASTER, SUFFIX=FOC     ,
 DATASET=baseapp/newmaster.foc, $
  SEGMENT=SEG01, SEGTYPE=S3, $
    FIELDNAME=BY_COUNTRY, ALIAS=E01, USAGE=A10, FIELDTYPE=I, $
    FIELDNAME=BY_CAR_NAME, ALIAS=E02, USAGE=A16, FIELDTYPE=I, $
    FIELDNAME=BY_CAR_MODEL, ALIAS=E03, USAGE=A24, FIELDTYPE=I, $
    FIELDNAME=TOTAL_DEALER_COST, ALIAS=E04, USAGE=P8.2C, $
    FIELDNAME=TOTAL_RETAIL_COST, ALIAS=E05, USAGE=P8.2C, $

MartinY, your approach requires an existing data source. In my case, user can post different number of columns, and the data type is not known until data is received. What I need is to create a master file on the fly when a user uploads the data.

In fact I found this posted by Waz a while ago:

http://forums.informationbuild...1036282?r=9131036282

The beauty of this method is I can create the master file in the memory and don't bother to clean it up from the file system after the data is loaded.

Case is closed. Thanks for all the input!

I was just illustrating what Dan was giving you as a method.

Glad to see that it's solved

EDAPUT is one method. I use -WRITE statements to create my in-line master descriptions:

FILEDEF ADHOCDATA DISK <app name>/<data filename>
FILEDEF TEMPMAST DISK ADHOCDATA.MAS
-RUN
-WRITE TEMPMAST FILENAME=ADHOCDATA, SUFFIX=FIX/COM/TAB/etc.
-WRITE TEMPMAST  SEGNAME=ADHOCDATA, SEGTYPE=SO, $
-WRITE TEMPMAST   FIELDNAME=field1,, ACTUAL=<xx>, USAGE=<XX>, $
-WRITE TEMPMAST   FIELDNAME=field2,, ACTUAL=<yy>, USAGE=<YY>, $
-RUN

This message has been edited. Last edited by: Dan Satchell,