XLGETFILE command

Imports a text file into a sheet of an Excel workbook.

Syntax

CHGVAR	VAR(&CMD) VALUE('XLGETFILE')
CHGVAR	VAR(&PARM1) VALUE('text file')
CHGVAR	VAR(&PARM2) VALUE(' [Start=Number] [;RecordCnt= Number] [;Destination=Cell reference] [;MapColName=True/False] [;AutoFit=True/False] [;FormatValue=True/False] ')
CALL	PGM(LNCCMD) PARM(&HANDLE &CMD &OPT &PARM1 + &PARM2 &RESULT)

Parameters

Parm1

Path and name of the text file to import into the Excel sheet.

Parm2

Parm2 allows you to set read properties of the text file.

Properties are separated by semicolons.
Property names can be in lowercase or uppercase letters.

Properties:

Start: to set the rank of the first line of the source file to insert.
If the source file is from a transfer made by the DBXFER command, then it includes the names of the columns in the first line.
This line will be ignored when inserting with "Start = 2".

RecordCnt: to set the maximum number of lines to insert.

Destination: Lets you give the cell reference at the top left of the destination range that will receive the data from the file.
By default, the active cell will be the first receiving cell.
This parameter is not taken into account if MapColName = true.

If MapColName is true, the cells with the column names of the text file receive the values.
The first line of the text file must contain the names of the columns.

If Autofit is true, this allows you to adjust the width of the columns to the content.

FormatValue:
True (default): The cell format is set according to the AS400 data type.
False: The format of the cells is fixed by Excel, according to the values.

Example

LNCCMD CMD(XLOPENFILE) +

PARM1('C:\A\Templates\SPCUST_template.xlsx')

LNCCMD CMD(XLGOTOSH) PARM1('Invoice')

LNCCMD CMD(XLGETFILE) +

PARM1('File="C:\A\LNC002.TXT"') +

PARM2('Start=2;FormatValue=True;Aut+

oFit=False;RecordCnt=4;MapColName=true')

LNCCMD CMD(XLGOTOSH) PARM1('Data')

LNCCMD CMD(XLGETFILE) +

PARM1('File="C:\A\LNC002.TXT"') +

PARM2('Start=2;FormatValue=True;Aut+

oFit=true;RecordCnt=4;Destination=$B$3')

In this example, the text file data (4 records), from the second record, are copied to the "Invoice" sheet, using data mapping based on their names.

In addition, the data from the text file (4 records), from the second record, are also copied to the "Data" sheet. The cell at the top left of the destination range is cell B3.

See also

• LNCTOXLS - CL Command