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.
Edwintor Absent Member.
Absent Member.
1628 views

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|



Tags (3)
0 Likes
5 Replies
Edwintor Absent Member.
Absent Member.

RE: Export text to excel delimited using native cobol

I found this option, but I do not know how or if it can be interpreted with native Cobol
msdn.microsoft.com/.../ff193593.aspx
0 Likes
Micro Focus Expert
Micro Focus Expert

RE: Export text to excel delimited using native cobol

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.
neidingd Honored Contributor.
Honored Contributor.

RE: Export text to excel delimited using native cobol

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...
0 Likes
Edwintor Absent Member.
Absent Member.

RE: Export text to excel delimited using native cobol

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
0 Likes
neidingd Honored Contributor.
Honored Contributor.

RE: Export text to excel delimited using native cobol

Yes, insert cell to cell is very time consuming.
This is why we insert the whole text file over a query in one time.
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.