Export text to excel delimited using native cobol

Buen dia amables desarrolladores. Acudo a sus conocimientos pidiendo por favor me ayuden en el uso de excel desde cobol nativo, jamas abia utilizado estas funciones y necesito tomar un archivo generado con el delimitador "|" y mediante codigo cobol nativo exportarlo a excel. He utiliazdo un codigo de ejemplo que ha posteado anteriormente Chris:

*---------------------------------------------------------- ---------------
* Save Excel File
*
* This example program demonstrates how to use the Excel COM
* server support to create a new spreadsheet, populate it with
* graph data and then prompt the user for a filename using the
* GetSaveAsFileName method and then save it using the SaveAs
* method.
*
* This is a modified version of the demo that is supplied with
* Net Express under the C:\Program Files (x86)\Micro Focus\Net
* ress 5.1\Examples\Net Express IDE\COMDEMOS\EXCEL folder.
*
* Also demonstrated is the support for using Named Parameters
* in the COM methods.
*-------------------------------------------------------------------------
* following directive is required for COM clients
$set ooctrl( P)
class-control.
MSExcel is class "$OLE$Excel.Application"
olesup is class "olesup".

working-storage section.
01 ExcelObject object reference.
01 WorkBooksCollection object reference.
01 WorkBook object reference.
01 Cell object reference.
01 CellRange object reference.
01 CellRange2 object reference.
01 ChartsCollection object reference.
01 Chart object reference.

01 LoopCount pic xx comp-5.
01 SaveName pic x(256) value spaces.
01 lstype pic x(4) comp-5 value 0.


77 RUTAFILA PIC X(32) VALUE
"C:\plano prueba\materiaprima.txt".


procedure division.
*> Create a new instance of Microsoft Excel
invoke MSExcel "new" returning ExcelObject

*> Make Excel visible
invoke ExcelObject "setVisible" using by value 1

*>INVOCA APERTURA DE UNA FILA
invoke ExcelObject "getWorkBooks"
returning WorkBooksCollection
*>open archivo example

*>Aca opens the file txt, but it sticks everything in column "A",
*>here is where I would like to know in some way how to delimit it by the character "|"


invoke WorkbooksCollection "Open" using RUTAFILA
returning Workbook.

*>cambia el formato de la celda
invoke ExcelObject "getRange"
using z"A1:A1048576"
returning CellRange
end-invoke
* invoke CellRange "Select" using z"Delimited"

invoke olesup "setNamedParameters" using by value 1 size 4
*> Next call is required because getSaveAsFilename is method
*> that starts with get - don't treat as property.
invoke olesup "setDispatchType" using by value 0 size 4
*> Finalize all objects
* invoke Chart "finalize" returning Chart
* invoke ChartsCollection "finalize" returning ChartsCollection
invoke CellRange "finalize" returning CellRange
invoke WorkBook "finalize" returning WorkBook
invoke WorkBooksCollection "finalize"
returning WorkBooksCollection
end-invoke

*> Exit Excel
* invoke ExcelObject "Quit"
invoke ExcelObject "Finalize" returning ExcelObject

stop run.

This is the structure of the file that I should export:

0023924 |02 |SBR-1205 |CAUCHO SOLPRENE S-1205 |KLS| .43496471| | |KG|42|4|
0023924 |03 |SGR-L |CAUCHO NATURAL LIGHT (CLARO) |KLS| .24134536| | |KG|42|4|
0023924 |03 |MEPZ |MEDIAPLAST 62 |KLS| .00030168| | |KG|42|4|
0023924 |02 |KOED |RESINA DE CAUCHO E-260 |KLS| .27386667| | |KG|42|4|
0023924 |02 |OVIL |PLASTIFICANTE 1520 |KLS| .05638432| | |KG|42|4|
0023924 |02 |DESU |ANTIOXIDANTE NO M. VULCANOX SP |KLS| .01933176| | |KG|42|4|
0023924 |02 |CEBU |CERA ANTILUX 550 |KLS| .00966589| | |KG|42|4|
0023924 |02 |KLUZ |FL STRUKTOL,DISPERGATOR, FL |KLS| .01610979| | |KG|42|4|



  • I found this option, but I do not know how or if it can be interpreted with native Cobol
    msdn.microsoft.com/.../ff193593.aspx
  • Ha publicado su pregunta en la sección del foro de la comunidad Acu. Su pregunta debe colocarse en la parte de Net Express o Visual COBOL del sitio de la comunidad.
  • We never use another delimiter, but we insert text files to excel with the query opject...

    There is also a option to define another delimited with
    @TextFileOtherDelimiter = "|"
    but we never tested/needed this option, so it need to figure out how it is written.

    Create the Query:
    inquire xls-sheet @CELLS::@ITEM(1, "A") xls-range.

    modify xls-sheet @QueryTables::@Add(
    by name Connection "TEXT;Y:\muffrohr\SEQ-ARTBEW.txt"
    by name Destination xls-range
    )
    giving xls-query.

    Excel want's a array...
    call "C$SETVARIANT" using 2, myItems(1).
    call "C$SETVARIANT" using 4, myItems(2).

    Place the query and close it...
    modify xls-query @Name("SEQ-ARTBEW"),
    @FieldNames(1),
    @RowNumbers(0),
    @FillAdjacentFormulas(0),
    @PreserveFormatting(1),
    @RefreshOnFileOpen(0),
    @RefreshStyle(xlInsertDeleteCells),
    @SavePassword(0),
    @SaveData(1),
    @AdjustColumnWidth(0),
    @RefreshPeriod(0),
    @TextFilePromptOnRefresh(0),
    @TextFilePlatform(850),
    @TextFileStartRow(1),
    @TextFileParseType(xlDelimited),
    @TextFileTextQualifier(xlTextQualifierNone),
    @TextFileConsecutiveDelimiter(0),
    @TextFileTabDelimiter(0),
    @TextFileSemicolonDelimiter(1),
    @TextFileCommaDelimiter(0),
    @TextFileSpaceDelimiter(0),
    @TextFileColumnDataTypes(myArray),
    @TextFileTrailingMinusNumbers(1),
    @Refresh(by name BackgroundQuery 0).

    modify xls-query @Delete().
    destroy xls-query.

    here should be the semicolon delimiter set to (0)
    and the other delimiter set...
  • I can change the delimiter | by ; When I generate the txt file. The txt file I generated it from the reading of a table, so in the report I can change it. Try to write cell to cell from the flat file, but it is very time consuming. Thank you very much for your answer
  • Yes, insert cell to cell is very time consuming.
    This is why we insert the whole text file over a query in one time.