Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Ektoras Giorgos
New Member.
3825 views

Import from Excel file

does anyone know how can i import from a excel file data to variables in my program? is there a sample code?

0 Likes
5 Replies
Micro Focus Expert
Micro Focus Expert

RE: Import from Excel file

I believe you have two alternatives .. use Excel (NetDefGen or AxDefGen depending on the version of Excel) or use a runtime that has Acu4GL ODBC. I do not have an example of using Excedl within COBOL doing an import. With Acu4GL ODBC you set up a COBOL FD that matches your Excel table ... there is a toll called Acu4GLFD that may help .. then using the appropriate configuration entries .. the COBOL IO for this file will get and place data in the Excel table.

0 Likes
Micro Focus Expert
Micro Focus Expert

RE: Import from Excel file

with 9.0 and above you also have the choice to use XML extensions with extend ... this url has an example .. community.microfocus.com/.../19681.import-data-from-excel-using-xml-extensions.aspx

0 Likes
Axcel Absent Member.
Absent Member.

RE: Import from Excel file

I use from OCX,

example in the importation is for a GRID.

      identification division.

      program-id.    ver-laul.

      author.        rps

      environment division.

      configuration section.

      special-names.

          copy "lib\excelx.def".

          .          

      input-output section.

      file-control.                  

      data division.

      file section.          

      working-storage section.

      77  widx             pic 9(06).

      77  wds              pic x(150).

      77  wrow             pic 9(06).

      77  wcol             pic 9(04).

      77  wgrid-handle handle of grid external.

      01  excel-objects.

          03  hexcelapp  handle of application of excel.

          03  hexcelwkb  handle of workbook    of excel.

          03  hexcelwks  handle of worksheet   of excel.

          03  hrange     handle of range       of excel.

      linkage section.

      01 warq-xls             pic x(50).

      01 wqtd-load            pic 9(06).

      procedure division using warq-xls.

      main-logic.          

          create  application     of excel

                  handle          in hexcelapp.

          modify  hexcelapp   @SheetsInNewWorkBook = 1.

          modify  hexcelapp       workbooks::open(warq-xls)

                  giving          hexcelwkb.

          inquire hexcelwkb       worksheets::item(1) in hexcelwks.

     *    modify  hexcelapp       @visible = 1.

          perform varying wrow from 2 by 1 until wrow > 2000

             inquire hexcelwks @cells::@item(wrow,02) = hrange

             INQUIRE hrange @text IN WDS

             destroy hrange

             IF WDS = SPACES EXIT PERFORM

             END-IF

             MODIFY WGRID-HANDLE(wrow, 01), CELL-DATA = WROW

             perform varying widx from 1 by 1 until widx > 26

                inquire hexcelwks @cells::@item(wrow,WIDX) = hrange

                INQUIRE hrange @text IN WDS

                MOVE WIDX TO WCOL ADD 1 TO WCOL

                MODIFY WGRID-HANDLE(wrow, wcol), CELL-DATA = WDS

                destroy hrange

             end-perform

             add 1 to wqtd-load

          END-PERFORM.

          modify  hexcelwkb       @close(0).

          destroy hexcelwks.

          destroy hexcelwkb.

          modify  hexcelapp       @quit().

          destroy hexcelapp.

          EXIT PROGRAM.

0 Likes
mickyhall Absent Member.
Absent Member.

RE: Import from Excel file

I have seen several posts on importing data from Excel, and I wonder has anyone got a verfied answer for this.

https://community.microfocus.com/microfocus/cobol/extend_and_acucobol/f/20/t/10395.aspx

http://community.microfocus.com/microfocus/cobol/rm_cobol/w/knowledge_base/19681.import-data-from-excel-using-xml-extensions.aspx

__________________________________

TAGS: Excel, Excel loading

0 Likes
Lucille1578 Absent Member.
Absent Member.

RE: Import from Excel file

Solution is!

well the only thing that helped is this.

pcunleashed.com/.../how-to-convert-text-to-columns-in-excel

before i import data, especially numbers, first i use the "text to columns" command to convert the cells. Then the rest is made in the code we already mention above. mobdro

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.