Is there a way to programmatically generate the .acx and .ma...

[Russian Wyatt 2]

Is there a way to programmatically generate the .acx and .mas files

What I want to do is have and end-user dump a group of excel files into a directory. Then modify one spreadsheet (Main.xlsx) that shows the list of companies they want to include in the next phase of this process.

The Main.xlsx spreadsheet has two columns in it: Company & File

Column A (Company) would be the input for:

 

SEGNAME in the .acx file

FILENAME in the .mas file

SEGEMENT in the .mas file

 

Column B (File) would be the input for DATASET in the .mas file.

Everything else would be the same for all files, so the program would basically loop through all the rows creating the access information for each excel spreadsheet.

[john cullen]

Hello Russian,

You can create a master file from a focexec.

sample code for a SQLJDBC master.

CREATE SYNONYM /baseapp/a_dates FOR A_DATES SCHEMA WebFOCUS DBMS SQLJDBC AT CON01 DROP PRESERVECASE

If you want sample code for your EXCEL, then position 1 click away from completing making the master,

then turn on server side traces, make the master in one click, turn off server side traces. Then find the ts0000nn.fex (where nn is the tscom id that the procedure ran under) It will have the create synonym syntax like I typed in above.

You could then build your looping logic around the CREATE SYNONYM focexec, substituting parameters.

[Warren Hinchliffe]

The old forum, Focal Point has some stuff too

http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/956107535

[David Briars]

Is the endgame to combine the different Company spreadsheets into one report

If so, maybe consider creating a synonym (.acx/.mas) in the usual way, and then using FILEDEF to point to the various spreadsheets

For example:

 

 

-* Environmental Setups

-DEFAULTH &COMPANY = ' '

-DEFAULTH &FILENAME = ' '

-* Define the output file with APPEND.

FILEDEF OUTFILE DISK OUTFILE.FTM (APPEND

-RUN

-* Create Extract (HOLD file) of Company/Filenames Spreadsheet.

TABLE FILE COMANYFILENAMESSPREADSHEET

PRINT *

ON TABLE HOLD AS HLDFILNM

END

-RUN

-SET &TIMES = &LINES;

-*

-REPEAT END_LOOP &TIMES TIMES

-READFILE HLDFILNM

-TYPE Company: &COMPANY Filename: &FILENAME

-* Pull in the Company/Car spreadsheet.

FILEDEF CARSPREADSHEET DISK WORK/CarSpreadsheet-&COMPANY|.xlsx

DEFINE FILE CARSPREADSHEET

COMPANY/A24 = '&COMPANY';

END

TABLE FILE CARSPREADSHEET

PRINT COMPANY CAR COST SALES_PRICE

ON TABLE HOLD AS OUTFILE

END

-RUN

-END_LOOP

-* Create Final Report for User.

TABLE FILE OUTFILE

"Combined Car Report"

PRINT *

BY COMPANY NOPRINT

BY CAR NOPRINT

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = endeflt,$

ENDSTYLE

END

[Russian Wyatt 2]

I have actually managed to do the part where I combine all of the spreadsheets into one FOCUS database.

Currently I generate the synonyms by hand after seeing which files come in, since it requires so few line changes from a template. But Im working on automating as much of the process as possible, given I have end users of various skill levels as well as updates at various times.

This is really one of the last manual parts of this portion of the project.

[David Briars]

Russian:

 

Currently I generate the synonyms by hand after seeing which files come in

 

 

Do the Excel files coming in all have the same layout

That is what my example is assuming.

Then, in my example, I have only one master/access file: CARSPREADSHEET.

CARSPREADSHEET was created in the GUI in the usual way, one time. The DATASET attribute was removed after the one time creation.

At run time the incoming Excel spreadsheets are assigned to the CARSPREADSHEET master via the FILEDEF command.

[David Briars]

Interesting. It looks like the EDAPUT command is now documented in the TIBCO FOCUS Developing Applications manual Release 8207.27.0.

If you are in a release, where EDAPUT is documented, it might be worth looking at.

With EDAPUT you can write a Master File to disk or memory at runtime.

[Russian Wyatt 2]

I never did figure out where to find the .fex of the traces.

The Excel files all have the same layout. But the first 7 rows are filler (some sort of header for the form) and actual header row is #8

I was researching the FILEDEF command and the CREATE SYNONYM commands in parallel, and went with the one that worked first.

I didnt get a chance to look at EDAPUT, but Im on 8206

But after looking through a lot of the old forum stuff still out there I managed to cobble together:

-READFILE Main

CREATE SYNONYM &CompanyName

FOR &FNAME

DBMS DIREXCEL

PARMS 'WORKSHEET Sheet1 HROWS 8'

AT &CompanyName

DROP PRESERVECASE

END

 

Basically Id loop through the Main spreadsheet, it would create .acx and .mas files for all the companies in the list and giving me this error.

(FOC31049) Synonym COMPANY_A created with default field names pattern FIELD_nn

Oddly enough no matter how many companies it looped through it only gave me that one error.

[Russian Wyatt 2]

I ran into another issue with using Create Synonym. As I said before, my input file has 7 rows of filler and the header is the 8th row.

 

Using the command I used in the previous post created a .acx file that is nearly identical to the one I hand crafted.

SEGNAME=SAMPLE_COMPANY_INC,

WORKSHEET=Sheet1,

HROWS=8,

NUMDATA=RAW, $

However the master (.mas) file takes everything in the first column for 8 rows and mashes it into one field name and thus the actual first field (Position), never gets created as a FIELDNAME.

The same thing happens in the third column (First Name).

 

Does anyone know how to get the master file to ignore the first 7 rows

[Toby Mills]

Hey Wyatt

Long time since I did heavy work with CREATE SYNONYM so I just used the GUI to build it. Then I turned on traces to try to catch how it was done. I made up a sample sheet like yours and was able to read it okay (for the data parts). Looks like the keyword youre looking for is SKIP_ROWS 7.

CREATE SYNONYM ~pth_tmpuser/wyatt_sample_sheet1 FOR baseapp/wyatt_sample.xlsx PARMS WORKSHEET Sheet1 HROWS AUTO SKIP_ROWS 7 RECORDS 5000 CHAR_EXT 25 DBMS DIREXCEL DROP CHECKNAMES UNIQUENAMES

Youd use whatever you want for your app path folder instead of my ~homeapps name.

I looked around a little and couldnt find SKIP_ROWS related to directly reading excel files, but it is documented in 8206 for flat files.

Give it a shot.

Good luck!

Toby

[Russian Wyatt 2]

Hey Toby,

I tried that synonym command and experimented with the parameters but in the best case it still compressed every bit of text in the first eight rows into one column fieldname.

In the end I ended up just using FILEDEF as David suggested and just write them both the .acx and .mas files manually.

TABLE FILE work/Company_List

PRINT CompanyName FNAME

ON TABLE HOLD AS CompanyList

END

-RUN

 

-* Capture number of companies in the table in preparation for the upcoming loop

-SET &C_Limit = &LINES

-SET &I=0;

 

-REPEAT Listing WHILE &I LT &C_Limit;

-READFILE CompanyList

-SET &CACX = &CompanyName || '.acx';

-SET &CMAS = &CompanyName || '.mas';

 

-* Create the .acx file

FILEDEF OFFLINE DISK c:ibiappsProj_24work&CACX

-RUN

-WRITE OFFLINE SEGNAME=&CompanyName, WORKSHEET=Sheet1, HROWS=8, NUMDATA=RAW, $

 

-* Create the .mas file

FILEDEF OFFLINE DISK c:ibiappsProj_24work&CMAS

-RUN

-WRITE OFFLINE FILENAME=&CompanyName, SUFFIX=DIREXCEL,

-WRITE OFFLINE DATASET=work/&FNAME, $

-WRITE OFFLINE SEGMENT=&CompanyName, SEGTYPE=S0, $

-WRITE OFFLINE FIELDNAME=Position, ALIAS=Position, USAGE=A50, ACTUAL=A50V, MISSING=ON, TITLE='Position', $

-WRITE OFFLINE FIELDNAME=LastName, ALIAS='LName', USAGE=A60V, ACTUAL=A60V, MISSING=ON, TITLE='Last Name', $

-WRITE OFFLINE FIELDNAME=FirstName, ALIAS='FName', USAGE=A60V, ACTUAL=A60V, MISSING=ON, TITLE='First Name', $

.

.

.

-RUN

-SET &I = &I +1;

-Listing

 

It wasnt as clean/compact as I wanted but it seems to work.