9.222 TRANSFORM_LIST

Note: Built-In Function Rules     Usage Options

Transforms the current contents of one or more working lists into a disk file.

It is designed to facilitate the transfer of information between Visual LANSA applications and other products (e.g: spreadsheets).

Arguments

No

Type

Req/ Opt

Description

Min Len

Max Len

Min Dec

Max Dec

1

L

Req

Name of the primary working list that is to be transformed into a disk file.

Note: If this list contains fields of type Binary or VarBinary, the Built-In Function will end in error.

 

 

 

 

2

A

Req

Name of file to be replaced or created by this Built-In Function.

1

256

 

 

3

A

Opt

Output File Format.

A - Normal Delimited File.

B - DBF File.(Not available on IBM i.)

C - Columnized File (Numeric Fields Have Leading Signs) with signs.

D - Columnized File (Numeric Fields do NOT have Signs) without signs.

O - Comma Delimited File.

S – Comma Delimited File: A common CSV file format. It has exactly the same format as type O files except that a completely blank field is not represented as a single blank and trailing blanks are ONLY included if they represent invalid character substitutions.

T - Horizontal Tab Delimited Files.

Output File Formats A, C, D, O, S and T support UTF-8 format. This is indicated by appending a 'U' to this argument (i.e. Format). For example, UTF-8 output for format A would have an Output File Format of 'AU'.

Note: CU and DU column widths are double the field width in order to accommodate the extra bytes that UTF-8 may require.

The default value is 'A'.

1

3

 

 

4

A

Opt

Method of handling invalid characters encountered within alphanumeric fields.

'B' - Replace by blank character.

'I' - Ignore. Include character.

'R' - Remove from output.

The default value is 'B'.

1

1

 

 

5

A

Opt

Include Carriage Return at the end of each record.

'N'- Do not include carriage  return.

'T' - Include carriage return and also truncate all blank data from the end of the record.

'Y'- Include carriage return.

The default is 'Y'.

1

1

 

 

6

A

Opt

Decimal Point to be used . The allowable values are:

'R'- Remove the decimal point from all numeric representations. This will shorten the length of numeric fields that have decimal positions by 1 character. Only valid with file formats C and D.

other - The value to be used as a decimal point character.

The default is the currently defined system decimal point (i.e. '.' or ',').

Note: The use of European style ',' decimal points may create problems in files formats that also use commas to delimit fields.

1

1

 

 

7

A

Opt

Close Output File Option.

'Y'- Close the file at completion.

'N'- Do not close the file at  completion.

The default is 'Y'.

1

1

 

 

8 - 17

L

Opt

Allows up to 10 Appendage Working Lists to be specified. Refer to the following notes for more details.

Valid only if the primary working list is an RDML list.

 

 

 

 

 

Return Values

No

Type

Req/ Opt

Description

Min Len

Max Len

Min Dec

Max Dec

1

A

Req

Return Code.

OK - File Created.
ER - Error when opening file. Refer to Return Code - Error Handling and Error Activity .

2

2

 

 

 

Technical Notes - TRANSFORM_LIST

9.222.1 Output File Formats

9.222.2 Other Parameters

SQLNULL Handling

When a field is SQLNULL, the *NULL equivalent is output.

Special Handling for BLOB and CLOB value

The full BLOB/CLOB file name will be saved in the output file. The BLOB/CLOB file itself will be duplicated in a subdirectory under the output file directory. The name of this subdirectory is <output file name>_LOB. For example if the output file is:

C:\Root\Data\Transformed1.dat,
and the original CLOB file is C:\XYZ\ CLOBNumber1.txt
then the duplicated CLOB file is

C:\Root\Data\Transformed1_LOB\CLOBNumber1.txt
and the CLOB value in the Transformed1.dat will be:
C:\XYZ\ CLOBNumber1.txt

If Transformed1.dat is moved (or copied ) into another system, move or copy the sub directory Transformed1_LOB and all its contents as well.

Example

The following outline function can save the contents of an existing SQL table to a disk file, or insert the contents of disk file into an SQL table (i.e. An SQL table Save/Restore function) .....

def_list #list fields(....) listcount(#count) type(*working) entrys(100)
request fields (#option and name of disk file involved)
if (#option = SAVE)
  select fields(...) from_file(...)
         add_entry #list
         if (#count = 100)
            use TRANSFORM_LIST #list (with "do not close" option)
            clr_list #list
         endif
       endselect
       use TRANSFORM_LIST #list (with "close" option)
else (#option was RESTORE)
    dowhile (#retcode *ne EF)
        use TRANSFORM_FILE into #list (with "do not close" option)
        execute insertlist
   endwhile
   execute insertlist
endif
subroutine insertlist
    selectlist #list
        insert fields(...) to_file(....)
    endselect
    clr_list #list
endroutine

By adding the CONNECT_SERVER and CONNECT_FILE Built-In Functions, this function could be very simply expanded to support the following table of "data transfers":

Data Target

<- - - - - - - - - -

Data Source

- - - - - - - - - ->

 

PC SQL Table PC Disk File IBM i File

PC SQL Table

N/A

Yes

Yes

PC Disk File

Yes

N/A

Yes

IBM i File

Yes

Yes

N/A