Passing excel data to Natural

, , ,
1

I’m prototyping calling Natural from Microsoft Excel using EntireX and had a question. I have defined my PDA as follows and plan on passing (and returning) csv rows with a maximum length of 229…

DEFINE DATA
PARAMETER
1 #IN (A229/1:V) /* INOUT
*

My extracted IDL looks like…
…
define data parameter
1 #IN (A229/1:V) In Out /* Restricted usage: (A229/1:V) : Natural V-array is not a full IDL unbounded array /* INOUT
*…

Can someone explain to me the “Restricted usage” warning message before I get too far down this path? Thanks

Ken

2

Or maybe there’s a better format to pass table data to a sub program?

3

Have you considered the alternatives?

Mainframe:

  • To Excel:
    Create a TAB-delimited (pseudo-CSV format) record.
COMPRESS NUMERIC field-list INTO #large-variable WITH ALL DELIMITER H'09' 
WRITE WORK nn #large-variable
  • From Excel:
    Use Save as in Excel to create a CSV-format file. Read the WORK file and SEPARATE the columns.

Natural for Windows:

  • To Excel:
    Create a CSV-format file.
DEFINE WORK FILE nn TYPE "CSV"
WRITE WORK nn field-list
  • From Excel:
    Use Save as in Excel to create a CSV-format file that Natural can read natively.
DEFINE WORK FILE nn TYPE "CSV"
READ WORK nn field-list
  • Read and write Excel files:
    Create a Natural Dialog to create, access, and update Excel files. Dialogs have full access to ActiveX controls and Excel methods.
4

I believe your solution requires a Natural license for Windows which I do not have. Also, I wanted to write an Excel add in and call Natural from within Excel so the user would never have to exit Excel to do the processing.

Ken

5

If Natural is being used solely to retrieve Adabas data, then Adabas SOA Gateway can retrieve that data from within Excel.

6

There’s quite a bit of processing being done to the data in the Natural program, so the Adabas Gateway is an interesting idea but not an option for this task.

7

Hi Ken,
you should use Natural X-Arrays, see https://techcommunity.softwareag.com/ecosystem/documentation/natural/nat827mf/rpc/limits.htm#limits_x_arrays

8

Rolf,

Thank you for your suggestion, IDL looks cleaner now…

define data parameter
1 #PARM (A6) In /* IN
1 #IN (A229/1:V) In /* Full usage: (A229/1:) : Originates from Natural X-array / IN
1 #OUT (A229/1:V) Out /* Full usage: (A229/1:) : Originates from Natural X-array / OUT
*

Ken

9

a suggestion: if your Natural subprograms are operating on specific data (which the length of A229 suggests), you can generate the actual columns of data to send than having to create csv from Excel columns and parse the csv in Natural. You can still use the x-array to handle variable number of rows.