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?

  • 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.

  • 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

  • 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.

  • 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.

  • 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.

  • 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

  • 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

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

    community.microfocus.com/.../10395.aspx

    community.microfocus.com/.../19681.import-data-from-excel-using-xml-extensions.aspx

    __________________________________

    TAGS: Excel, Excel loading

  • 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

  • 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