Excel spreadsheet to COBOL program

2
votes

I have a task to migrate an Excel Macro(Which basically does screen scraping) to a COBOL program.

Problem is that the user wants to give the input in excel format , I am stumped as to how to convert an excel file as a readable input to a TN3270 Mainframe system.

Some have suggested to do something called as flat file conversion which I am not sure how to do.

Is there a easier method to extract inputs from an Excel spreadsheet to run a COBOL program on a TN3270 Mainframe.

The below is the screenshot of the tool which is to be converted.

enter image description here

According to my knowledge, we can convert it into a flat file and read the strings using delimiter(|) but I want to know if there is a better way.

3
excelcobolmainframejclvba
Good luck. The excel macro api is very weak. In particular using something like cobal to get through it. The only tip I can offer is that xlsx files are zipped xml files. - t3dodson
It is unclear what you want. You need a lot more, accurate, informaiton. TN3270 is just a hardware-emulation term. Need to know where, and on what OS and with which compiler, the COBOL program is going to be. Also need to know exactly any continuing role of the Excel spreadsheet. - Bill Woodger
The downvote is probably because you have not provided sufficient information. It's a tough one. If you can work it into a good question, I'll offset the downvote for you with my upvote. But my first thought is that reading between the lines, someone in the design process has expectations beyond the reasonable. Describe exactly what is happening now, and exactly what you hope to replace it withj. - Bill Woodger
@ Bill .Thanks , I am not much familiar on the Mainframe part since we use the emulator(TN3270) but I know its running the Z/Os i have no idea about the compiler, the excel file will be treated only as pure input nothing more. - Anarach
Need a lot more, like how often will the spreadsheet be the source (once, once a day, some other period)? You will be using Enterprise COBOL, does your site have CICS, IMS/DC? What DBMS, if any? Is there any return of data to the spreadsheet? Will the user end up using a Mainframe system which they'd like to look a bit more like Excel than would be normal? It can be done, but can't be anywhere near the power oif Excel. - Bill Woodger

3 Answers

2
votes

You might want to split your task in several steps

  1. Extract data from Excel sheet
  2. Make data available to COBOL program
  3. Work with data in COBOL

Not knowing the details of your problem I can only suggest solutions at this point:

  1. Use Visual Basic for applications to create a file (you might want to look for a CSV solution and take into account that it might be necessary to change the file encoding)
  2. Use a transfer utility - e.g. ftp or some other mainframe connection that let's you transfer files from the Excel-running computer to the mainframe. Some 3270 terminal software also offers APIs for this.
  3. Convert Excel logic to COBOL (if any)
2
votes

You can't run Excel on the Mainframe and to code something there would increase in complexity each time the user required more Excel-like functionality. It would be a variation of reinventing-the-wheel, which is something to avoid.

You have a user using Excel, the information is required in an IMS database on the Mainframe. By far the most sensible thing to do would be to provide something to link the two together, allowing the user to continue with Excel but with a customisation (for that specific Excel application) to connect to the IMS database and use it, even for update.

How you go about this depends on what you have at your site already with which you can do this. Here's an article from the Mainframe Edition of IBMSystems Magazine: http://www.ibmsystemsmag.com/mainframe/administrator/ims/imstm_integration/

Here is an extract from the article, showing you some groups of options to do this. Within the groups, there would be specific ways to apply each option. Under RYO, of course, there are a plethora of highly-customised possibilities:

  1. Java EE Connector Architecture (JCA)

A Java-based technology solution for connecting application servers and enterprise information systems (EIS), with a standard set of system-level contracts between the Java EE application server and a resource adapter that manage the connection, transactions, security, work, life cycle, transaction inflow and message inflow.

You have an existing Java EE server or one of the IBM servers that needs access to IMS.

Primary use is direct synchronous IMS access.

  1. Simple Object Access Protocol (SOAP)

A protocol that allows heterogeneous applications to discover and communicate with each other in a platform- and language-independent way.

You need to interact with partners or applications that require the SOAP protocol.

Primary use is synchronous IMS access.

  1. Representational State Transfer (REST)

A protocol for simple Web services and efficient exchanges of data through stateless applications, used in conjunction with JavaScript Object Notation (JSON), a lightweight data-interchange format. JSON and REST are often used in mobile applications, mashup tools or automated business processes.

You need mobile solutions.

  1. Java Message Services (JMS)

An asynchronous-based messaging interface for exchanging of data between computers using messaging services in support of Java programs.

You have existing Java applications.

You are using a Java EE/JMS-based application server.

Primary use is asynchronous IMS access.

  1. Roll-your-own (RYO)

An approach often used when the organization already has its own implementations of solutions that handle the transactions, security and message flow.

You have an existing in-house server that needs a simple API to connect to IMS.

You need to go back to the designer, and they need to go to your Technical Support to find out what is already available for this type of connection. If there is nothing, then someone will have to do a technical review of what is available from various providers. A favourite search-engine will give hits of who those providers are.

0
votes

It's actually very simple. Simply save your Excel file as a CSV file (comma delimited). Each field in the columns can be different in size. Then read it into a COBOL program, using the same saved name. And then simply use UNSTRING in COBOL to delimit the fields by ','.

In one program I read in 50,000 records from an Excel file into COBOL, sometimes 10,000 more, sometimes less. Using dynamic statements in COBOL, it automatically delimits each column from the Excel file into COBOL. I happen to use tables to store the records.